包括每月计数，包括数据不存在的月份 [英] Include monthly counts including months where data doesn't exist

问题描述

这似乎应该很简单，但我似乎无法弄清楚。我有一个表格，其中包含一个 Artifact 名称和 Modification_Date 。我有一个查询，获取月份的文档修改计数。有几个月没有修改。因为我在 Modification_Date 上进行查询和分组，所以没有任何修改返回这些月份的结果。理想情况下，在结果集中，我想要的是那个月份，而数量中的0。

  SELECT CONVERT（NVARCHAR（7），Modification_Date，120）[Month]，
 COUNT（Artifact）as Quantity 
 FROM table 
 WHERE Modification_Date> DATEADD（月，-6，getdate（））
 GROUP BY CONVERT（NVARCHAR（7），Modification_Date，120）
 ORDER BY [Month] DESC 
  / pre> 
 
 
这样可以得到类似的结果：
 月数量
 ------- -------- 
 2013-02 10 
 2012-11 12 
 2012-10 5 
 2012- 09 29 
  
如您所见，2012年12月和2013年1月的月份不在结果集中。我想让那些月份在那里代表一个0 数量，以便我可以在SQL报表条形图中使用该数据，并将这些月份表示为0值。目前在条形图上，它完全跳过了这几个月。有没有办法生成过去6个月的 yyyy-mm   Month 列，而不是仅使用 Modification_Date ？
解决方案
不要将日期转换为字符串以剥离时间或日期，使用日期算术。转换为字符串效率较低，并且还需要扫描整个表格。
 
 
 
如果要转换为固定长度的字符串，请勿使用 NVARCHAR ，使用 CHAR 。您需要在数字日期支持哪些Unicode字符？ Umlauts？英镑？象形文字？
 
 
 
这是一个使用目录视图生成6行的示例，然后从当前日期减去几个月，以前6个月（和索引应该使用 Modification_Date ，与目前的方法不同）。这不是第一次看到它是完全直观的，但是可以看到我的系列中没有循环生成集合（第1部分 | 第2部分 |  // 
 $ b 
 ; x（m）AS 
（
 SELECT TOP 6 DATEADD（MONTH，DATEDIFF（MONTH，0，GETDATE（））
  - （ROW_NUMBER（）OVER（ORDER BY [object_id] ）），0）
 FROM sys.all_objects 
 ORDER BY [object_id] 
）
 SELECT [Month] = xm，Quantity = COALESCE（COUNT（t.Artifact），0 ）
 FROM x 
 LEFT OUTER JOIN dbo.tablename AS t 
 ON t.Modification_Date> = xm 
 AND t.Modification_Date< DATEADD（MONTH，1，x.m）
 GROUP BY x.m 
 ORDER BY x.m DESC; 
  
请注意，这不包括当前月份。如果你想转移到包括十月 - >三月，而不是九月 - >二月，只需更改这一行：
  + 1  - （ROW_NUMBER（）OVER（ORDER BY [object_id]）），0）
  
如果格式化为 YYYY-MM 绝对必要，您可以这样做：
 ; WITH y（m）AS 
（
 SELECT TOP 6 DATEADD（MONTH，DATEDIFF（MONTH，0，GETDATE（））
  - （ROW_NUMBER（）OVER（ORDER BY [object_id]）） 0）
 FROM sys.all_objects 
 ORDER BY [object_id] 
），
x（[Month]，Quantity）
 AS 
（
 SELECT [Month] = ym，Quantity = COALESCE（COUNT（t.Artifact），0）
 FROM y 
 LEFT OUTER JOIN dbo.tablename AS t 
 ON t.Modification_Date> = ym 
 AND t.Modification_Date< DATEADD（MONTH，1，ym）
 GROUP BY ym 
）
 SELECT [Month] = CONVERT（CHAR（7），[Month] 120），数量
 FROM x 
 ORDER BY [月] DESC; 
  
 
This seems like it should be easy, but I cannot seem to figure it out. I have a table that has an Artifact name and Modification_Date among other things.  I have a query that is getting counts of document modifications by month.  There are a couple of months where there were no modifications.  Since I am querying and grouping on the Modification_Date there is no result returned for those months with no modifications.  Ideally, in the result set I'd like the month there and 0 for the Quantity.
SELECT CONVERT(NVARCHAR(7), Modification_Date, 120) [Month], 
    COUNT(Artifact) as Quantity
    FROM table
WHERE Modification_Date > DATEADD(month, -6, getdate())
GROUP BY CONVERT(NVARCHAR(7), Modification_Date, 120)
ORDER BY [Month] DESC
This gets me a result similar to:
Month       Quantity
-------     --------
2013-02     10
2012-11     12
2012-10     5
2012-09     29
As you can see, the months of December 2012 and January 2013 are not in the result set.  I would like to have those months represented there with a 0 Quantity so that I can use that data in a SQL Report bar graph and have those months represented with a 0 value.  Currently on the bar graph it skips those months completely.  Is there a way to generate the yyyy-mm Month column for the last 6 months rather than just using the Modification_Date?
解决方案
Don't convert dates to strings to strip time or day, use date arithmetic. Converting to strings is less efficient and also requires a scan of the entire table.


If you're going to convert to a fixed length string, don't use NVARCHAR, use CHAR. What Unicode characters are you going to need to support in a numeric date? Umlauts? Pound signs? Hieroglyphics?

Here is an example that uses a catalog view to generate 6 rows, then subtracts months away from the current date to group by the previous 6 months (and an index on Modification_Date should be used, unlike your current approach). This is not entirely intuitive the first time you see it, but you can see my series on generating sets without loops (part 1 | part 2 | part 3).
;WITH x(m) AS 
(
  SELECT TOP 6 DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) 
    - (ROW_NUMBER() OVER (ORDER BY [object_id])), 0) 
  FROM sys.all_objects
  ORDER BY [object_id]
)
SELECT [Month] = x.m, Quantity = COALESCE(COUNT(t.Artifact), 0)
FROM x
LEFT OUTER JOIN dbo.tablename AS t
ON t.Modification_Date >= x.m
AND t.Modification_Date < DATEADD(MONTH, 1, x.m)
GROUP BY x.m
ORDER BY x.m DESC;
Note that this will not include the current month. If you want to shift to include October -> March instead of September -> February, just change this line:
+ 1 - (ROW_NUMBER() OVER (ORDER BY [object_id])), 0) 
And if formatting as YYYY-MM is absolutely essential, you can do this:
;WITH y(m) AS 
(
  SELECT TOP 6 DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) 
    - (ROW_NUMBER() OVER (ORDER BY [object_id])), 0) 
  FROM sys.all_objects
  ORDER BY [object_id]
),
x([Month], Quantity)
AS
(
  SELECT [Month] = y.m, Quantity = COALESCE(COUNT(t.Artifact), 0)
  FROM y
  LEFT OUTER JOIN dbo.tablename AS t
  ON t.Modification_Date >= y.m
  AND t.Modification_Date < DATEADD(MONTH, 1, y.m)
  GROUP BY y.m
)
SELECT [Month] = CONVERT(CHAR(7), [Month], 120), Quantity
FROM x
ORDER BY [Month] DESC;


                        
这篇关于包括每月计数，包括数据不存在的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！