SQL在过去12个月中选择多个计数，包括缺少的月份 [英] SQL select multiple counts in the last 12 months including missing months

问题描述

我一直在尝试创建一个SQL查询，返回的结果看起来像这样：

  2013年5月P1 0 
 2013年5月P2 5 
 2013年5月P3 2 
 2013年5月P4 1 
 2013年6月P1 3 
 2013年6月P2 2 
 2013年6月P3 4 
 2013年6月P4 9 
 2013年7月P1 0 
 2013年7月P2 0 
 2013年7月P3 5 
 2013年7月P4 2 
  
 
 
 
这个想法是我有两个表Priority和Work：
 code>优先级：
名称|值
 P1 9 
 P2 7 
 P3 5 
 P4 2 
 ... 
  
工作：
  ID | PriorityValue | CompleteTime 
 1 9 03/04/2013 
 2 5 02/09/2013 
 3 7 12/01/2014 
 4 9 11/02/2014 
  
如何在过去12个月或更长时间内为每项优先工作产生计数？
 
 
 
到目前为止，我已经看过这个包括每月计数，包括数据不存在的月份 
 
 
 
我到目前为止的查询是：
 ; WITH d（d）AS 
（
 SELECT DATEADD（MONTH，n，DATEADD（MONTH，DATEDIFF（MONTH，0， '20130501'），0））
 FROM（SELECT TOP（DATEDIFF（MONTH，'20130501'，'20140401'）+ 1）
n = ROW_NUMBER（）OVER（ORDER BY [object_id]） -  1 
 FROM sys.all_objects ORDER BY [object_id]）AS n 
）
 SELECT 
 dd，
 DATENAME（MONTH，dd）as [Month]，
 YEAR（dd）as [Year]，
 Priority.Name，COUNT（Work.ID）AS Count 
 FROM d LEFT OUTER JOIN工作
 ON CompleteTime> = dd 
 AND CompleteTime< DATEADD（MONTH，1，d.d）RIGHT OUTER JOIN Work.PriorityValue的优先级= Priority.Value 
 GROUP BY d.d，优先级名称
 ORDER BY d.d; 
  
但它仅生成优先级计数大于0的月份列表。例如：
  2013年5月P3 5 
 2013年5月P4 55 
 2013年7月P3 8 
 2013年7月P4 48 
 2013年8月P3 2 
 2013年8月P4 8 
  
 
 
解决方案
如果任何人寻找解决方案，我使用CROSS JOIN而不是RIGHT OUTER JOIN解决它，如下所示：
  SELECT 
 dd，
 DATENAME（MONTH，dd）as [Month]，
 YEAR（dd）as [Year]，
 ISNULL（Priority.Name，'P1'），
 COUNT（Work.ID）AS Count 
 FROM d 
 LEFT OUTER JOIN工作ON CompleteTime> = dd AND CompleteTime< DATEDD（MONTH，1，d.d）
 CROSS JOIN Work.PriorityValue的优先级= Priority.Value 
 GROUP BY d.d，ISNULL（Priority.Name，'P1'）
 ORDER BY d.d; 
  
 
I've been trying to create a SQL Query that returns a result that looks something like this:
May     2013    P1  0
May     2013    P2  5
May     2013    P3  2
May     2013    P4  1
June    2013    P1  3
June    2013    P2  2
June    2013    P3  4
June    2013    P4  9
July    2013    P1  0
July    2013    P2  0
July    2013    P3  5
July    2013    P4  2
The idea is I've got two tables Priority and Work:
Priority:
Name|Value
P1    9
P2    7
P3    5
P4    2
...
Work:
ID | PriorityValue | CompleteTime
1    9               03/04/2013
2    5               02/09/2013
3    7               12/01/2014
4    9               11/02/2014
How do I generate count for each priority work over the last 12 months or more?

So far I've looked at the solution from this Include monthly counts including months where data doesn't exist

The query I've got so far is:
;WITH d(d) AS 
(
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, '20130501'), 0))
  FROM ( SELECT TOP (DATEDIFF(MONTH, '20130501', '20140401') + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
    FROM sys.all_objects ORDER BY [object_id] ) AS n
)
SELECT 
  d.d,
  DATENAME(MONTH, d.d) as [Month], 
  YEAR(d.d) as [Year], 
  Priority.Name, COUNT(Work.ID) AS Count
FROM d LEFT OUTER JOIN Work
  ON CompleteTime >= d.d
  AND CompleteTime < DATEADD(MONTH, 1, d.d) RIGHT OUTER JOIN Priority on Work.PriorityValue = Priority.Value
GROUP BY d.d, Priority.Name
ORDER BY d.d;
But it only generates a list of months with priority count > 0. For example:
May     2013    P3  5
May     2013    P4  55
July    2013    P3  8
July    2013    P4  48
August  2013    P3  2
August  2013    P4  8

解决方案
In case anyone looking for a solution, I solved it using CROSS JOIN rather than RIGHT OUTER JOIN as below:
SELECT 
  d.d,
  DATENAME(MONTH, d.d) as [Month], 
  YEAR(d.d) as [Year], 
  ISNULL(Priority.Name,'P1'), 
  COUNT(Work.ID) AS Count
FROM d 
LEFT OUTER JOIN Work ON CompleteTime >= d.d AND CompleteTime < DATEADD(MONTH, 1, d.d)
CROSS JOIN Priority on Work.PriorityValue = Priority.Value
GROUP BY d.d, ISNULL(Priority.Name,'P1')
ORDER BY d.d;


                        
这篇关于SQL在过去12个月中选择多个计数，包括缺少的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！