TSQL查询12个月的数据 - 包括没有记录的月份

Question

我正在尝试创建一个12个月的网格视图，以查看该12个月期间每个月提交的所有问题。

SELECT 
    YEAR(h.metaInsert) [Year], 
    MONTH(h.metaInsert) [Month], 
    DATENAME(MONTH,h.metaInsert) [Month Name], 
    COUNT(1) [Total Documents]
FROM 
    Document_Count_History AS h
WHERE 
    YEAR(h.metaInsert) = 2017
GROUP BY 
    YEAR(h.metaInsert), MONTH(h.metaInsert), DATENAME(MONTH, h.metaInsert)
ORDER BY 
    1, 2

这可以完美地返回拥有该数据的几个月的数据，但是我没有返回该特定月份有0个记录的人的数据。

我的目标是查看所有12个月以及文件计数。如果没有文档，则该月只有0个文档，但它将包含在结果集中。

我该如何采取自己的身份并应用缺失的月份？

Answer 1

您可以使用类似的内容来生成查询的月顺序：

declare @StartDate     date = '20170101'
       ,@NumberOfYears int  = 1;
;with Months as (
select top (12*@NumberOfYears) 
    [Month] = dateadd(Month, row_number() over (order by number) -1, @StartDate)
   , NextMonth = dateadd(Month, row_number() over (order by number), @StartDate)
  from master.dbo.spt_values
)
select 
    year(m.Month) [Year], 
    Month(m.Month) [Month], 
    datename(Month,m.Month) [Month Name], 
    count(h.*) [Total Documents]
from Months as m
  left join Document_Count_History AS h
    on h.metaInsert >= m.Month
   and h.metaInsert < m.NextMonth
--where h.metaInsert >= '20170101'
group by m.Month
order by m.Month

尽管您可能要考虑添加日历表或日期维度。

日历和数字表参考：

• 生成无环的集合或序列-1 -Aaron Bertrand

• "数字"或" tally"表：它是什么以及如何替换循环-Jeff Moden

• 在SQL Server 2008中创建日期表/维度-David Stein
• 日历表 - 为什么需要一个-David Stein
• 在SQL Server中创建日期维度或日历表-Aaron Bertrand

示例月份表：

create table dbo.Months(
    MonthStart date not null primary key
  , NextMonthStart date not null
  , [Year] smallint not null
  , [Month] tinyint not null
  , [MonthName] varchar(16) not null
);
declare @StartDate     date = '20100101'
       ,@NumberOfYears int  = 30;
insert dbo.Months(MonthStart,NextMonthStart,[Year],[Month])
  select top (12*@NumberOfYears) 
    [MonthStart] = dateadd(month, row_number() over (order by number) -1, @StartDate)
    , NextMonthStart = dateadd(month, row_number() over (order by number), @StartDate)
    , [year] = year(dateadd(month, row_number() over (order by number) -1, @StartDate))
    , [Month] = Month(dateadd(month, row_number() over (order by number) -1, @StartDate))
    , MonthName =  datename(Month,dateadd(month, row_number() over (order by number) -1, @StartDate))
  from master.dbo.spt_values;

，您的查询将简化为：

select 
    m.[Year], 
    m.[Month], 
    m.[MonthName], 
    count(h.*) [Total Documents]
from Months as m
  left join Document_Count_History AS h
    on h.metaInsert >= m.MonthStart
   and h.metaInsert < m.NextMonthStart
where m.Year = 2017
group by m.Month, m.Year, m.MonthName
order by m.MonthStart

Answer 2

您需要一个日期维度。具体来说，您需要一个具有几个月值的表格。然后，您可以在桌子上进行左键，以获取总计并拔出一个和值。