What SQL queries will help analyse a membership base?

Question

At our sports centre, I would like to analyse the number and types of subscriptions our members have, but I'm having trouble with the exact SQL queries.

A "subscription" is an entry pass that lets you into specific activities in our sports centre. They have a start and end date, representing the period they are valid. They have an associated activity (eg. "gym", "swim", "group fitness", "rock climbing", etc.)

A member can have more than one subscription. Typically, one will expire before they purchase their next one, but they can have two (or more) valid (or current) subscriptions.

Simplifying a little, we have the following two tables:

  Members - stores a member's data, has these columns
    ID (int) - unique ID for a member
    Name (varchar) - the member's name
    Gender (varchar) - the member's gender
    NumGym (int) - number of valid gym passes a member has (derived)

and

  Subscriptions - holds all the subscriptions we've sold, has these columns
    TypeID (varchar) - unique ID for a subscription type (specifies activity)
    MemberID (int) - the ID of the member that purchased this subscription
    StartDate (datetime) - when the subscription is valid from
    EndDate (datetime) - when the subscription is valid to

Some of the questions I would like to be able to ask are:

1. how many members currently have a valid "gym" pass?
2. how many active members do I have on a given day

For (1), I've tried the following:

declare @aDay datetime
set @aDay = convert(datetime, '2009-12-08', 102)

update Members m
set NumGym = (select count(memberid)
    from Subscriptions s
    where s.MemberId = m.Id
    and s.TypeID = "Gym"
    and @aDay between s.StartDate and s.EndDate)

However it doesn't seem to be producing the correct results (based on our understanding of our membership and some manual counting).

Not having been able to get (1) to work, I haven't tried (2) yet.

Any suggestions on queries to produce what I'm after?

Answer 1

1. How many members currently have a valid "gym" pass?

Use:

SELECT COUNT(*)
  FROM SUBSCRIPTIONS s
 WHERE GETDATE() BETWEEN s.startdate AND s.enddate
   AND s.typeid = 'Gym'

GETDATE() is a SQL Server function you can call to get the current date (and time), rather than construct a date.

2. How many active members to I have on a given day (ie. how many valid passes)

Use:

SELECT COUNT(*)
  FROM SUBSCRIPTIONS s
 WHERE @arbitrary_date BETWEEN s.startdate AND s.enddate

Answer 2

How many members currently have a valid "gym" pass

SELECT COUNT(*)
    FROM Subscriptions
    WHERE CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) BETWEEN StartDate AND EndDate
          AND TypeID = 'Gym'

You should truncate GETDATE() here because you want to include EndDate as I understood.

"How many members?" is not the same as "How many passes?".

So "How many members?":

SELECT COUNT(DISTINCT MemberID)
    FROM Subscriptions
    WHERE @Date BETWEEN StartDate AND EndDate

And "How many passes?":

SELECT COUNT(*)
    FROM Subscriptions
    WHERE @Date BETWEEN StartDate AND EndDate