CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
stephen776
stephen776

Reputation: 9234

How to count active subscribers between two given dates

I have a table of users. Each user has a SubscriptionStartDate and a SubscriptionEndDate

The End Date could be null if their subscription has not been scheduled to end

I need to count the number of active subscriptions by month over a period of time

I can do this one month at a time but I would like a single query that can give me all the results at once for say every month in a year or more.

declare @startDate datetime;
declare @endDate datetime;

set @startDate = '2-01-2016';
set @endDate = '2-29-2016'

select COUNT(*)
from Users
where SubscriptionStartDate <= @startDate
and (SubscriptionEndDate is null or SubscriptionEndDate>= @endDate)

How can I write a query or CTE to do this?

Upvotes: 0

Views: 2042

Answers (2)

mxix
mxix

Reputation: 3659

With a time table or a CTE and with APPLY you can try it this way.

with months AS (
    select CONVERT(DATE,'2015-01-01') MonthStart, CONVERT(DATE,'2015-01-31') MonthEnd
    union all
    select dateadd(MONTH,1,MonthStart), dateadd(DAY,-1,dateadd(MONTH,2,MonthStart))
    from months
    where
        dateadd(MONTH,1,MonthStart) < GETDATE()
)
select 
    * 
from months m 
outer apply (
    select
        COUNT(*) UserCount
    from [Users] [U]
    where
        SubscriptionStartDate <= m.MonthEnd and
        (ISNULL(SubscriptionEndDate,'3000-01-01')>= m.MonthStart)
) Users
OPTION (MAXRECURSION 0)

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31785

Start with a table or CTE of all the months in the range you want to check, and join to that table using the Subscription dates in the user table.

Upvotes: 0

Related Questions