Let’s say you have a table with some dates and numeric values e.g.:

2011-08-19	16
2011-08-22	45
2011-08-24	62
2011-08-25	88
2011-08-27	17
2011-08-28	35
2011-09-01	10
2011-09-02	79
2011-09-03	70
2011-09-07	83

As you can see not all dates are in the sequential order, 24th comes after 22nd etc. But in many cases you need this data to be sequential, for example if this data feeds a chart you need to fill it with missing dates and 0 for a value. There is an easy way to achieve this.

Some solution suggest creation of a Calendar table – table with prefilled dates and values, but for this task it would be an overkill. A small Common Table Expression will suffice instead.

Let’s say your table with data is called #TEMP_DATA, column with dates is called MyDate, column with integer values is called MyValue. Consider following CTE:

-- Getting date limits
DECLARE @MaxDate DateTime
DECLARE @MinDate DateTime

SELECT @MaxDate = MAX(MyDate), @MinDate = MIN(MyDate) FROM #TEMP_DATA;

-- Calendar CTE with ALL the dates
WITH mycte AS
    (
        SELECT @MinDate AS DateValue
        UNION ALL
        SELECT DateValue + 1
        FROM mycte   
        WHERE DateValue + 1 <= @MaxDate
    )
SELECT DateValue, 0 AS IntValue
FROM mycte 
OPTION (MAXRECURSION 0)

It will produce full date range within limits specified by dates in #TEMP_DATA table:

2011-08-19 	0
2011-08-20 	0
2011-08-21 	0
2011-08-22 	0
2011-08-23 	0
2011-08-24 	0
2011-08-25 	0
2011-08-26 	0
2011-08-27 	0
2011-08-28 	0
2011-08-29 	0
2011-08-30 	0
2011-08-31 	0
2011-09-01 	0
2011-09-02 	0
2011-09-03 	0
2011-09-04 	0
2011-09-05 	0
2011-09-06 	0
2011-09-07 	0

Only thing remains is to marry it in happy UNION to original table with data:

DECLARE @MaxDate DateTime
DECLARE @MinDate DateTime

SELECT @MaxDate = MAX(MyDate), @MinDate = MIN(MyDate) FROM #TEMP_DATA;

-- Calendar CTE with ALL the dates
WITH mycte as
    (
        SELECT @MinDate AS DateValue
        UNION ALL
        SELECT DateValue + 1
        FROM mycte   
        WHERE DateValue + 1 <= @MaxDate
    )
-- Original data        
SELECT MyDate, MyValue FROM #TEMP_DATA 
UNION
-- Filled in data
SELECT DateValue, 0
FROM mycte LEFT JOIN #TEMP_DATA ON DateValue = MyDate WHERE MyDate IS NULL
ORDER BY 1
OPTION (MAXRECURSION 0)

Query selects original data that has dates missing and then UNIONs it with full date range from the CTE which is limited only to missing dates by LEFT JOIN. The result is perfect combination:

2011-08-19	16
2011-08-20	0
2011-08-21	0
2011-08-22	45
2011-08-23	0
2011-08-24	62
2011-08-25	88
2011-08-26	0
2011-08-27	17
2011-08-28	35
2011-08-29	0
2011-08-30	0
2011-08-31	0
2011-09-01	10
2011-09-02	79
2011-09-03	70
2011-09-04	0
2011-09-05	0
2011-09-06	0
2011-09-07	83

All the dates are in sequential order, original date range is preserved and missing values are filled with zeroes.