Filling in date holes in grouped-by-date SQL data

The actual problem involves far more data and joins, but I've created a small sample to demonstrate the issue:

-- create example table
DROP TABLE dbo.EventRecords
GO
CREATE TABLE dbo.EventRecords
    (
    EventDate datetime NOT NULL,
    EventCount int NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.EventRecords ADD CONSTRAINT
    PK_EventRecords PRIMARY KEY CLUSTERED 
    (
    EventDate,
    EventCount
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
-- put in some random data for example
DECLARE @Counter INT=0
WHILE (@Counter0 AND @Counter%7<>0    -- leave some dates empty
    BEGIN
        INSERT INTO dbo.EventRecords(EventDate,EventCount)
        VALUES (DATEADD(day,@Counter,'2013-01-01'),@SemiRandomCount1)
        PRINT CAST(@SemiRandomCount2 AS VARCHAR(MAX))
        IF @SemiRandomCount2>0 AND @Counter%2=0 -- some dates have multiple entries
            INSERT INTO dbo.EventRecords(EventDate,EventCount)
            VALUES (DATEADD(day,@Counter,'2013-01-01'),@SemiRandomCount2)
    END
    SET @Counter=@Counter+1
END
--SELECT * FROM dbo.EventRecords

So, some dates have multiple entries, some have none. I need to get results for reporting that contain every date in a specified range, with the total counts for that date (zero if there were no counts for that date). After much googling and experimentation, I found a very clever way to generate sequences on the fly and built this function from it. These sequences can be used to build a date sequence table on the fly which can then be used to join the EventRecords table and group by date with no holes:

```
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE name='GetSequence')
EXECUTE sp_executesql N'CREATE FUNCTION GetSequence() RETURNS @Table TABLE (Value SMALLINT NOT NULL) AS BEGIN RETURN END'
GO

ALTER FUNCTION [dbo].GetSequence
RETURNS @Sequence TABLE
(
Value BIGINT NOT N

I recommend using a Calendar table or Date Dimension (whichever name you prefer). Here is an answer with using a quick CTE.

/ Date Range CTE /
-- Updated based on @AaronBertrand's articles linked in the comments
-- Basically ends up being the same query as the last half of @AaronBertrand's post
declare @FromDate date;
declare @ThruDate date;
set @FromDate='2013-01-01';
set @ThruDate='2015-01-01';

with cal as (
select top (1+datediff(day, @FromDate, @ThruDate))
DateValue = dateadd(day, v.number, @FromDate)
from master.dbo.spt_values v
where v.type = 'P'
and v.number >= 0
order by v.number
)
select EventDate = cal.DateValue
, TotalEventCount = isnull(sum(EventCount), 0)
from cal
left join dbo.EventRecords e on cal.DateValue=e.EventDate
group by cal.DateValue
order by cal.DateValue


Some links about calendar tables:

• Sql 2005 Calendar Tables - Why You Need One - David Stein

• Sql 2008+ Creating a Date Table/Dimension on SQL 2008 - David Stein

• Calendar Tables in T-SQL

StackExchange Database Administrators Q#86435, answer score: 3