sql-计算按月分组的活动记录 [英] sql - count active records grouping by month

问题描述

使用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屋！