Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
My brain hurts after trying to determine what you want. Can you be more explicit - perhaps show the data you want in a sample table? –  Max Vernon Mar 13 '13 at 20:30
    
You need a reference table containing all IDs possible, then LEFT JOIN to that table. Then you will get the null missing items. –  Max Vernon Mar 13 '13 at 21:06
1  
Are you sure you are running this query? I don't see how this part is parsed as valid: (SELECT SUM(the_count) WHERE the_id = dt.the_id and the_date = dt.the_date) –  ypercube Mar 13 '13 at 21:07
    
It is running without errors, SQL Server 2012 - but doesn't deliver the expected result –  damica Mar 13 '13 at 21:17
add comment

1 Answer

up vote 3 down vote accepted

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 ;
share|improve this answer
    
Yes! I had to make small modifications on the column names, but it is working now!! Thank you very much! –  damica Mar 13 '13 at 21:25
    
Thank you for the second version! –  damica Mar 14 '13 at 10:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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