SQL 组不同计数

Question

我有下表，

User ID    Start Date   End Date
-------------------------------------
John Doe   Mar 11 2011  May 28 2011
Robret S   Mar 21 2011  Jun 29 2011
Tina T     Feb 01 2011  August 20 2011

我想显示过去 6 个月有多少人可以使用，即使该月没有人。这怎么可能。我知道我必须进行分组并使用不同的计数。

预期输出：

February  = 1 Resource
March     = 3 Resources
April     = 3 Resources
May       = 3 Resources
June      = 2 Resources
July      = 1 Resource
August    = 1 Resource

Answer 1

With Calendar As
    (
    Select Cast('20110501' As DateTime) As [Date]
    Union All
    Select DateAdd(m,-1,[Date])
    From Calendar
    Where [Date] > DateAdd(m,-5,'20110501')
    )
Select DateName(m, C.Date) + ' ' + Cast(Year(C.Date) As char(4))
    , Case Count(*)
        When 1 Then Cast(Count(*) As varchar(10)) + ' Resource'
        Else Cast(Count(*) As varchar(10)) + ' Resources'
        End
From Calendar As C
    Left Join MyTable As T
        On C.Date Between T.StartDate And T.EndDate
Group By C.Date

结果：

December 2010   | 1 Resource
January 2011    | 1 Resource
February 2011   | 1 Resource
March 2011      | 1 Resource
April 2011      | 3 Resources
May 2011        | 3 Resources

Answer 2

您将需要过去 6 个月的现有数据记录，以便可以合并两组数据。您可以在 CTE 中生成过去 6 个月的数据，并对数据进行左连接。即使您没有数据，这也可以让您显示过去 6 个月的情况。

Answer 3

我认为你不能使用“简单”的 select 语句（甚至使用 GROUPing 等）来做你想做的事情。以下是我的想法，所以你必须稍微尝试一下，最好阅读Joe Celko 的优秀SQL for Smarties 书籍。

您需要创建第二个表，其中包含所有月份（开始/结束日期）。对于所有类型的类似查询，您只需要一张表，并且它必须包含您感兴趣查询的日期范围内的所有月份：

CREATE TABLE months (id, start DATE, end DATE);

INSERT INTO months (id, start, end) 
          values ( (1, 2011-01-01, 2011-01-31), 
                   (2, 2011-02-01, 2011-02-28), ...);

然后，您可以从用户表到本月表进行 LEFT OUTER JOIN。这将为您提供每个用户每个可用月份的一行，您可以根据需要对其进行分组：

SELECT months.id, COUNT(user.id) 
  FROM months LEFT OUTER JOIN users 
         ON user.start_date < months.end 
        AND user.end_date > months.start 
GROUP BY months.id;

希望有帮助。

Answer 4

WITH resources AS (
  SELECT
    Date = DATEADD(month, v.number, [Start Date])
  FROM atable t
    INNER JOIN master.dbo.spt_values v ON v.type = 'P'
      AND v.number BETWEEN 0 AND DATEDIFF(month, t.[Start Date], t.[End Date])
)
SELECT
  Month = DATENAME(month, Date),
  ResourceCount = CAST(COUNT(*) AS varchar(30)) +
                  CASE COUNT(*) WHEN 1 THEN ' Resource' ELSE ' Resources' END
FROM resources
WHERE Date > DATEADD(month, -6, DATEADD(day, -DAY(GETDATE()), GETDATE()))
GROUP BY YEAR(Date), MONTH(Date), DATENAME(month, Date)
ORDER BY YEAR(Date), MONTH(Date)