force sql results to return each date even if it is not in database

Question

Possible Duplicate:
generate days from date range

Here is my query:

SELECT SUM(number) AS number, c_date 
    FROM my_table 
    WHERE c_date between '11/6/2012' AND '11/12/2012' 
    GROUP BY c_date 
    ORDER BY c_date DESC

It will return something like this

11    '11/12/2012'
9     '11/9/2012'
10    '11/8/2012'
10    '11/7/2012'
10    '11/6/2012'

Now how can I force the return results to include 11/11 and 11/10 with 0 for the number rather than skipping over them entirely. Also I cannot create a date table to store the dates.

"Also I cannot create a date table to store the dates." Why not? — Michael Fredrickson, Nov 12 '12 at 20:54
you can always make a numbers table on the fly with a CTE or temp table. — dotjoe, Nov 12 '12 at 20:56
@kricket Please see my updated answer... same idea, but the link points to an example using a CTE. — Michael Fredrickson, Nov 12 '12 at 20:57

Michael Fredrickson · Accepted Answer · 2012-11-12T20:54:18.087

0

Since you can't create a new table, an alternate approach is to build up a "calendar" CTE.

Next, perform a LEFT JOIN from your newly created "calendar" table to my_table:

SELECT ISNULL(SUM(mt.number), 0) AS number, mc.c_date 
FROM 
    my_calendar mc LEFT JOIN
    my_table mt ON mt.c_date = mc.c_date
WHERE mc.c_date between '11/6/2012' AND '11/12/2012' 
GROUP BY mc.c_date 
ORDER BY mc.c_date DESC

edited Nov 12 '12 at 20:54

answered Nov 12 '12 at 20:48

Michael Fredrickson

36,839
5
92
109

score 0 · Answer 2 · answered Nov 12 '12 at 21:06

You could create a user defined function that returns a date range. It would look like this:

CREATE FUNCTION uspDateRange(@MinDate datetime, @Maxdate datetime)
RETURNS @DateRange TABLE ([Date] datetime)
AS
BEGIN
    DECLARE @Date datetime

    SET @Date = @MinDate

    WHILE (@Date < @MaxDate)
    BEGIN
        INSERT INTO @DateRange VALUES(@Date)
        SET @Date = DATEADD(day, 1, @Date)
    END

    RETURN
END
GO

Now you can select a range to get the range of all days of October 2012:

SELECT * FROM uspDateRange('2012-10-01', '2013-11-01')

Your query would look like this:

SELECT SUM(number) AS number, c_date 
    FROM my_table t
    INNER JOIN uspDateRange('11/6/2012', '11/12/2012') r ON t.c_date = r.[Date]
    GROUP BY c_date     
    ORDER BY c_date DESC

force sql results to return each date even if it is not in database

2 Answers2