sql-计算按月分组的活动记录 [英] sql - count active records grouping by month
本文介绍了sql-计算按月分组的活动记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
使用TSQL,我如何获得活动记录的计数,这些记录按月划分,其中我有一个OpenOn日期和一个ClosedOn日期? (Null的ClosedOn表示该记录仍处于活动状态.)
E.G.
如果我有:
Using TSQL, how would I get a count of active records, grouped by month where I have a OpenOn date and a ClosedOn date? (ClosedOn of Null means the record is still active).
E.G.
If I have:
OpenOn ClosedOn
********* **********
2011-01-02 2011-01-30
2011-01-04 2011-03-15
2011-01-05
2011-02-01 2011-03-10
2011-03-02 2011-03-20
那我应该得到
Then I should get
Month Total
****** *****
2011-01-01 3
2011-02-01 3
2011-03-01 4
******************
I.E.
Records 1,2 and 3 were active during Jan,
Records 2,3 and 4 were active during Feb, and
Records 2,3,4 and 5 were active during March.
推荐答案
看看 ^ ].
问题已解决.
我使用了一个临时表来确定我感兴趣的月份组,然后使用一个带有between指令的内部联接来标识正确的数据.
I.E.
Problem solved.
I used a temporary table to identify the month groups I was interested in then an inner join with a between directive to identify the correct data.
I.E.
DECLARE @Calendar TABLE(theMonth DATETIME);
INSERT INTO @Calendar ( theMonth )
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
INSERT INTO @Calendar ( theMonth )
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -1, GETDATE())), 0)
INSERT INTO @Calendar ( theMonth )
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -2, GETDATE())), 0)
INSERT INTO @Calendar ( theMonth )
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -3, GETDATE())), 0)
SELECT calendar.theMonth,
COUNT(1) AS total
FROM SourceTable AS t
INNER JOIN @Calendar AS calendar
ON (calendar.theMonth
BETWEEN DATEADD(MONTH,DATEDIFF(MONTH,0, t.OpenOn),0)
AND CASE
WHEN t.ClosedOn IS NULL THEN GETDATE()
ELSE t.ClosedOn
END)
GROUP BY calendar.theMonth,
ORDER BY calendar.theMonth DESC
这篇关于sql-计算按月分组的活动记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文