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 (@Counter&lt;1000)
BEGIN
    DECLARE @SemiRandomCount1 INT=@Counter*589043%23
    DECLARE @SemiRandomCount2 INT=@Counter*85907%7
    IF @SemiRandomCount1&gt;0 AND @Counter%7&lt;&gt;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&gt;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&lt;=@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?
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 &gt;= 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