如何计算两个给定日期之间的活跃订阅者(How to count active subscribers between two given dates)

如何计算两个给定日期之间的活跃订阅者(How to count active subscribers between two given dates)

我有一个用户表。 每个用户都有一个SubscriptionStartDate和一个SubscriptionEndDate

如果尚未安排其订阅结束，则结束日期可以为空

我需要在一段时间内按月计算有效订阅的数量

我可以一次这样做一个月，但我希望一个查询可以一次性给我所有结果，比如一年或更长时间。

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)

如何编写查询或CTE来执行此操作？

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?

最满意答案

使用时间表或CTE并使用APPLY，您可以这样尝试。

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)

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)