Sql-server – Join Calender Table

join;sql server

I have a Datatable and a Calendartable.
I do a join on this tables to get sequential dates if there is no data for this day.

My problem now is that there a several id's and i need a count for each one per day.
The problem is that I get a 'null' value for the id-field if there is no data for this date.
Also my query below does only work if there is one id in the table, if i add a second id, the empty rows are not shown because they exist for the other id.

My sample tables are:

    --Datatable
    CREATE TABLE [dbo].[datatable](
        [the_id] int,
        [the_date] date,
        [the_count] int NULL
    ) ON [PRIMARY]

    INSERT INTO datatable (the_id, the_date, the_count) VALUES
    (1, dateadd(d, -5, getdate()), 37),
    (1, dateadd(d, -5, getdate()), 30),
    (1, dateadd(d, -5, getdate()), 70),
    (1, dateadd(d, -4, getdate()), 8),
    (1, dateadd(d, -4, getdate()), 9),
    (1, dateadd(d, -2, getdate()), 19),
    (1, dateadd(d, -2, getdate()), 3),
    (1, dateadd(d, -1, getdate()), 20)

    INSERT INTO datatable (the_id, the_date, the_count) VALUES
    (2, dateadd(d, -5, getdate()), 27),
    (2, dateadd(d, -5, getdate()), 17),
    (2, dateadd(d, -5, getdate()), 37),
    (2, dateadd(d, -3, getdate()), 8),
    (2, dateadd(d, -3, getdate()), 89),
    (2, dateadd(d, -2, getdate()), 19),
    (2, dateadd(d, -2, getdate()), 9),
    (2, dateadd(d, -1, getdate()), 20),
    (2, dateadd(d, -1, getdate()), 2)

    --Calendartable
    CREATE TABLE [dbo].[calendartable](
        [the_day] date
    )

    DECLARE @StartDate date
    DECLARE @EndDate date
    SET @StartDate = DATEADD(d, -10, GETDATE())
    SET @EndDate = DATEADD(d, 10, GETDATE())

    WHILE @StartDate <= @EndDate
    BEGIN
    INSERT INTO [calendartable] (the_day)
    SELECT @StartDate
    SET @StartDate = DATEADD(dd, 1, @StartDate)
    END

My query:

    --Query
    DECLARE @mindate date
    DECLARE @maxdate date
    SELECT @mindate = MIN(CONVERT(date, the_date)),
    @maxdate = MAX(CONVERT(date, the_date))
    FROM datatable

    SELECT  dt.the_id, isnull(dt.the_date, ct.the_day),
    (SELECT SUM(the_count) WHERE the_id = dt.the_id and the_date = dt.the_date)
    as the_sum_count
    FROM calendartable AS ct
    LEFT JOIN datatable AS dt
    ON dt.the_date = ct.the_day
    AND ct.the_day BETWEEN @mindate AND @maxdate
    WHERE ct.the_day BETWEEN @mindate AND @maxdate
    GROUP BY dt.the_id, dt.the_date, ct.the_day
    ORDER BY dt.the_id, dt.the_date ASC

This query shows the row with no data at this day, but I also need the id to which this date belongs. The query shows no empty rows if there is a second id in the datatable.

Please help!

EDIT:

To be more explicit which output I want:

    ID  the_date    the_count
    1   2013-03-08  137
    1   2013-03-09  17
    1   2013-03-10  null  <--- this is missing in the above query
    1   2013-03-11  22
    1   2013-03-12  20
    2   2013-03-08  81
    2   2013-03-09  null <--- this is also missing
    2   2013-03-10  97
    2   2013-03-11  28
    2   2013-03-12  22

I hope my questions is clearer now!

Best Answer

I think you want (tested at SQL-Fiddle-1):

SELECT  the_id    = dd.the_id, 
        the_day   = ct.the_day,
        the_count = COALESCE(SUM(dt.the_count), 0)
FROM 
        calendartable AS ct
    CROSS JOIN
        ( SELECT DISTINCT the_id 
          FROM datatable
          WHERE the_date BETWEEN @mindate 
                             AND @maxdate
        ) AS dd
    LEFT JOIN 
        datatable AS dt
            ON  dt.the_date = ct.the_day
            AND dt.the_id = dd.the_id 
WHERE 
        ct.the_day BETWEEN @mindate 
                        AND @maxdate
GROUP BY 
        dd.the_id, ct.the_day
ORDER BY 
        the_id, the_day ;

It can also be written like this (SQL-Fiddle-2):

SELECT  the_id    = dd.the_id, 
        the_day   = ct.the_day,
        the_count = COALESCE(
            ( SELECT SUM(dt.the_count)
              FROM   datatable AS dt
              WHERE  dt.the_date = ct.the_day
                AND  dt.the_id = dd.the_id     
            )               
                            , 0)
FROM 
        calendartable AS ct
    CROSS JOIN
        ( SELECT DISTINCT the_id 
          FROM datatable
          WHERE the_date BETWEEN @mindate 
                             AND @maxdate
        ) AS dd
WHERE 
        ct.the_day BETWEEN @mindate 
                        AND @maxdate
ORDER BY 
        the_id, the_day ;
Related Question