SQL Server – How to Fill Gaps with Calendar Table, Count, and Group By

chart;gaps-and-islandssql servert-sql

I'm building a bar chart. I would like a query which counts the entries from a particular day and groups them together as one row with a count. I have the following query working and I have a working date dimension table which I would like to try to join with to fill in the gaps. It would also be nice to have min and max variables to easily alter the span (1 week, 1 month, 1 year, or from start of this year etc).

I got a variant working with a CTE query but it was painfully slow (1min+). I can't seem to figure this out, help!

The Date Dimension table was created following this guide: 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
);

Current query:

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

I want something like this

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

Best Answer

First, get your list of dates from the dimension. Just specify the start and end you need - let's say all of January, since you should have at least a gap at the end:

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

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

You should see 31 rows here. Now, use an outer join to pull in the rows you have aggregates for:

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];
Related Question