2

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?

3

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:

| improve this answer | |
  • 1
    +1 on the calendar table. But the recursive CTE approach performs absolutely terribly on larger ranges (example here generating numbers). – Aaron Bertrand Dec 17 '14 at 17:33
  • @AaronBertrand Performs absolutely terribly on larger ranges compared to master.dbo.spt_values based cte or to a Calendar/Number table? – SqlZim Dec 17 '14 at 17:38
  • 1
    Sorry, the link was bad, try it again. Recursive CTE was worse by orders of magnitude against all comers - spt_values, numbers table, etc. Here's an illustrative graphic from my tests: cdn.sqlperformance.com/wp-content/uploads/2013/01/… Recursive is just bad for numbers or dates in my experience, and so there is little justification for using it even in small sets. – Aaron Bertrand Dec 17 '14 at 17:40
  • Wow, very thorough testing and benchmarks in your articles! I have only ever used this recursive cte as the basis for creating my calendar tables, guess it is time to switch it over to values. – SqlZim Dec 17 '14 at 17:46
  • For one time stuff, probably not a big deal at all. But most bang for buck in replacing a slow and awkward function would not be the recursive CTE IMHO. – Aaron Bertrand Dec 17 '14 at 17:48
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.