如何用日曆表、計數和分組來填補空白

我正在建構一個條形圖。我想要一個查詢，它計算特定日期的條目並將它們組合在一起作為一行並進行計數。我有以下查詢工作，我有一個工作日期維度表，我想嘗試加入以填補空白。擁有 min 和 max 變數來輕鬆更改跨度（1 週、1 個月、1 年或從今年年初開始等）也會很好。

我有一個使用 CTE 查詢的變體，但速度非常慢（1 分鐘以上）。這個我好像搞不明白，求救！

日期維度表是按照本指南創建的：https ://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

CREATE TABLE dbo.DateDimension
(
DateKey             INT         NOT NULL PRIMARY KEY,
[Date]              DATE        NOT NULL,
[Day]               TINYINT     NOT NULL,
DaySuffix           CHAR(2)     NOT NULL,
[Weekday]           TINYINT     NOT NULL,
WeekDayName         VARCHAR(10) NOT NULL,
IsWeekend           BIT         NOT NULL,
IsHoliday           BIT         NOT NULL,
HolidayText         VARCHAR(64) SPARSE,
DOWInMonth          TINYINT     NOT NULL,
[DayOfYear]         SMALLINT    NOT NULL,
WeekOfMonth         TINYINT     NOT NULL,
WeekOfYear          TINYINT     NOT NULL,
ISOWeekOfYear       TINYINT     NOT NULL,
[Month]             TINYINT     NOT NULL,
[MonthName]         VARCHAR(10) NOT NULL,
[Quarter]           TINYINT     NOT NULL,
QuarterName         VARCHAR(6)  NOT NULL,
[Year]              INT         NOT NULL,
MMYYYY              CHAR(6)     NOT NULL,
MonthYear           CHAR(7)     NOT NULL,
FirstDayOfMonth     DATE        NOT NULL,
LastDayOfMonth      DATE        NOT NULL,
FirstDayOfQuarter   DATE        NOT NULL,
LastDayOfQuarter    DATE        NOT NULL,
FirstDayOfYear      DATE        NOT NULL,
LastDayOfYear       DATE        NOT NULL,
FirstDayOfNextMonth DATE        NOT NULL,
FirstDayOfNextYear  DATE        NOT NULL
);

目前查詢：

select count(*) as total, dateadd(DAY,0, datediff(day,0, CreatedAt)) as created
from Table1
group by dateadd(DAY,0, datediff(day,0, CreatedAt))
order by dateadd(DAY,0, datediff(day,0, CreatedAt)) desc


total   created 
1       01/11/2017 00:00:00 
16      01/03/2017 00:00:00 
1       12/27/2016 00:00:00 
1       12/20/2016 00:00:00 
1       11/30/2016 00:00:00 
1       11/29/2016 00:00:00 
11      11/28/2016 00:00:00 
13      11/25/2016 00:00:00 
4       11/24/2016 00:00:00 
2       11/22/2016 00:00:00 

我想要這樣的東西

total   created 
1       01/11/2017 00:00:00 
0       01/10/2017 00:00:00
0       01/09/2017 00:00:00
0       01/08/2017 00:00:00
0       01/07/2017 00:00:00
0       01/06/2017 00:00:00
0       01/05/2017 00:00:00
0       01/04/2017 00:00:00
16      01/03/2017 00:00:00 
etc

首先，從維度獲取日期列表。只需指定您需要的開始和結束時間 - 假設整個 1 月份，因為您應該在結尾處至少有一個間隙：

DECLARE @Start date = '20170101',
 @End date = DATEADD(MONTH,1,'20170101');

SELECT [Date] FROM dbo.DateDimension
 WHERE [Date] >= @Start AND [Date] < @End;

您應該在這裡看到 31 行。現在，使用外連接來拉入您擁有聚合的行：

DECLARE @Start date = '20170101',
 @End date = DATEADD(MONTH,1,'20170101');

SELECT created = d.[Date], total = COALESCE(s.total, 0)
 FROM dbo.DateDimension AS d
 LEFT OUTER JOIN
 (
    SELECT [Date] = CONVERT(date, CreatedAt), 
           total  = COUNT(*)
    FROM dbo.whatever
    WHERE CreatedAt >= @Start
      AND CreatedAt < @End
    GROUP BY CONVERT(date, CreatedAt)
 ) AS s ON d.[Date] = s.[Date]
 WHERE d.[Date] >= @Start 
   AND d.[Date] < @End
 ORDER BY d.[Date];

引用自：https://dba.stackexchange.com/questions/161204