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

Question

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 (@Counter<1000)
BEGIN
    DECLARE @SemiRandomCount1 INT=@Counter*589043%23
    DECLARE @SemiRandomCount2 INT=@Counter*85907%7
    IF @SemiRandomCount1>0 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](@StartInclusive INT, @EndExclusive INT)
RETURNS @Sequence TABLE
(
    Value BIGINT NOT NULL
)
AS
BEGIN
    INSERT @Sequence
    SELECT Value=@StartInclusive+n-1
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY o1.n) 
        FROM (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o1
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o2
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o3
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o4
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o5
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o6
        ) D (n)
    WHERE n<=@EndExclusive-@StartInclusive
    RETURN
END
GO

Here are sample queries:

DECLARE @StartDate DATE='2013-01-01'
DECLARE @EndDate DATE='2015-01-01'
-- query with holes: not what I need
SELECT [EventDate], [TotalEventCount]=ISNULL(SUM(EventCount),0) 
FROM dbo.EventRecords 
GROUP BY [EventDate]
-- query with date holes filled in: this is what I need
SELECT 
    [EventDate]=DATEADD(day,s.Value,@StartDate), 
    [TotalEventCount]=ISNULL(SUM(EventCount),0)
FROM [dbo].[GetSequence](0,DATEDIFF(day,@StartDate,@EndDate)) s
LEFT JOIN dbo.EventRecords c
    ON DATEDIFF(day,@StartDate, EventDate)=s.Value
GROUP BY s.Value

So, my question is: is there a better (simpler or faster) way to get sequences, or a better way to solve this problem in SQL?

SqlZim · Accepted Answer · 2016-12-05T17:30:41.920

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

score 1 · Answer 2 · edited Nov 13 '18 at 23:15

Well, you should have a Numbers table or a Calendar table. I'll start with a Numbers table:

CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY CLUSTERED);

INSERT dbo.Numbers WITH (TABLOCKX) (Number) 
SELECT TOP (1000000) Number = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1
  CROSS JOIN sys.all_objects AS s2;

Then:

DECLARE @StartDate DATE='2013-01-01'
DECLARE @EndDate DATE='2015-01-01'

;WITH x(d) AS
(
  SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate)+1)
    DATEADD(DAY, Number-1, @StartDate) 
    FROM dbo.Numbers ORDER BY Number
)
SELECT 
  EventDate = x.d,
  TotalEventCount = COALESCE(SUM(er.EventCount),0)
FROM x
LEFT OUTER JOIN dbo.EventRecords AS er
ON x.d = er.EventDate
GROUP BY x.d
ORDER BY x.d;

A Calendar table, as described in the other answer, is even better, but either should perform vastly better than your function.

If you don't have a Numbers or Calendar table, and don't want to create either (please read the links at the bottom of this answer and this article first), then you can use built-in things, like spt_values if your maximum date range is less than about 2,000 days:

DECLARE @StartDate DATE='2013-01-01'
DECLARE @EndDate DATE='2015-01-01'

;WITH x(d) AS
(
  SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate)+1)
    DATEADD(DAY, Number, @StartDate) 
    FROM master.dbo.spt_values
    WHERE type = 'P' AND Number >= 0 ORDER BY Number
)
SELECT 
  EventDate = x.d,
  TotalEventCount = COALESCE(SUM(er.EventCount),0)
FROM x
LEFT OUTER JOIN dbo.EventRecords AS er
ON x.d = er.EventDate
GROUP BY x.d
ORDER BY x.d;

If you need more than 2,000 days (who is going to consume a report that spans two years of individual days, anyway?), you can use a single ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_columns or a cross-join of different catalog views to meet your maximum range, rather than a sloppy cross join of many individual queries. But really, the Calendar table is your best bet.

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

2 Answers2