Inserting missing rows with a join

问题:

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…

回答1:

Try this one -

DECLARE @temp TABLE (FDOM DATETIME, Cat NVARCHAR(50), Score INT)

INSERT INTO @temp (FDOM, Cat, Score)
VALUES 
    ('20120101', 'Test1', 17),('20120201', 'Test1', 19),
    ('20120401', 'Test1', 15),('20120501', 'Test1', 16),
    ('20120701', 'Test1', 14),('20120801', 'Test1', 15),
    ('20120901', 'Test1', 15),('20121001', 'Test1', 13),
    ('20121201', 'Test1', 11),('20130101', 'Test1', 10),
    ('20130201', 'Test1', 15),('20130301', 'Test1', 13),
    ('20130501', 'Test1', 18),('20130601', 'Test1', 14),
    ('20130801', 'Test1', 15),('20130901', 'Test1', 14),
    ('20131201', 'Test1', 10),('20120601', 'Test2', 10)

;WITH enum AS 
(
    SELECT Cat, StartDate = MIN(FDOM), EndDate = MAX(FDOM)
    FROM @temp
    GROUP BY Cat

    UNION ALL

    SELECT Cat, DATEADD(MONTH, 1, StartDate), EndDate
    FROM enum 
    WHERE StartDate < EndDate
)
SELECT e.StartDate, t.Cat, Score = ISNULL(t.Score, 0)
FROM enum e
LEFT JOIN @temp t ON e.StartDate = t.FDOM AND e.Cat = t.Cat
ORDER BY e.StartDate, t.Cat

回答2:

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.

回答3:

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

回答4:

Do a left join from "complete table" to "incomplete table" and set a where statement to check the date column of the "incomplete" table. So you will only get the missing results in your select query. After that, just set a "insert into tablename" before.

In the first run it will find two rows, that aren't already in the incomplete table. So it will be inserted by the insert into statement, two rows affected. In a second run the result in the select statement has 0 rows, so nothing happens. Zero rows affected :-)

Sample: http://sqlfiddle.com/#!2/895fe/6 (Just mark the select statement; the insert into statement isn't required to just see, how the join works)

Insert Into supportContacts


Select * FROM 

(

Select
'01/2012' as DDate,   'Test1' as Cat,  17  as Score
UNION 
Select
'02/2012' as DDate,   'Test1' as Cat,  17  as Score
UNION 
Select
'03/2012' as DDate,   'Test1' as Cat,  17  as Score
UNION 
Select
'04/2012' as DDate,   'Test1' as Cat,  17  as Score
UNION 
Select
'05/2012' as DDate,   'Test1' as Cat,  17  as Score

) CompleteTable

LEFT JOIN

(

Select
'01/2012' as DDate,   'Test1' as Cat,  17  as Score
UNION 
Select
'02/2012' as DDate,   'Test1' as Cat,  17  as Score
UNION 
Select
'03/2012' as DDate,   'Test1' as Cat,  17  as Score


) InCompleteTable

ON CompleteTable.DDate = IncompleteTable.DDate

WHERE IncompleteTable.DDate is null