如何计算两个给定日期之间的活跃订阅者(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)更多推荐
发布评论