Inserting missing rows with a join

Question

I have a SQL script that returns this derived table.

MM/YYYY  Cat    Score
01/2012  Test1  17
02/2012  Test1  19
04/2012  Test1  15
05/2012  Test1  16
07/2012  Test1  14
08/2012  Test1  15
09/2012  Test1  15
12/2012  Test1  11
01/2013  Test2  10
02/2013  Test2  15
03/2013  Test2  13
05/2013  Test2  18
06/2013  Test2  14
08/2013  Test2  15
09/2013  Test2  14
12/2013  Test2  10

As you can see, I am missing some MM/YYYYs (03/2012, 06/2012, 11/2012, etc).

I would like to fill in the missing MM/YYYYs with the Cat & a 0 (zero) form the score.

I have tried to join a table that contains the all MM/YYYY for the ranges the query will be run, but this only returns the missing rows for the first occurrence, it does not repeat for each Cat (should have known that).

So my question is this, can I do this using a join or will I have to do this in a temp table, and then output the data.

AHIGA, LarryR…

Answer 1

You need to cross join your categories and a list of all dates in the range. Since you have posted no table structures I'll have to guess at your structure slightly, but assuming you have a calendar table you can use something like this:

SELECT  calendar.Date,
        Category.Cat,
        Score = ISNULL(Scores.Score, 0)
FROM    Calendar
        CROSS JOIN Catogory
        LEFT JOIN Scores
            ON Scores.Cat = Category.Cat
            AND Scores.Date = Calendar.Date
WHERE   Calendar.DayOfMonth = 1;

If you do not have a calendar table you can generate a list of dates using the system table Master..spt_values:

    SELECT  Date = DATEADD(MONTH, Number, '20120101')
    FROM    Master..spt_values
    WHERE   Type = 'P';

Where the hardcoded date '20120101' is the first date in your range.

ADDENDUM

If you need to actually insert the missing rows, rather than just have a query that fills in the blanks you can use this:

INSERT Scores (Date, Cat, Score)
SELECT  calendar.Date,
        Category.Cat,
        Score = 0
FROM    Calendar
        CROSS JOIN Catogory
WHERE   Calendar.DayOfMonth = 1
AND     NOT EXISTS
        (   SELECT  1
            FROM    Scores
            WHERE   Scores.Cat = Category.Cat
            AND     Scores.Date = Calendar.Date
        )

Although, in my opinion if you have a query that fills in the blanks inserting the data is a bit of a waste of time.

Answer 2

To get what you want, start with a driver table and then use left outer join. The result is something like this:

select driver.cat, driver.MMYYYY, coalesce(t.score, 0) as score
from (select cat, MMYYYY
      from (select distinct cat  from t) c cross join
           themonths  -- use where to get a date range
     ) driver left outer join
     t
     on t.cat = driver.cat and t.MMMYYYY = driver.MMYYYY