Missing Weeks View

1
votes

I have 2 tables which I need to compare to find missing data.

TableA: Definition table

Year, Week, cmp_code, [other columns]

TableB: Cash Receipts

Year, WeekNo, FranchiseID

TableA has all the possible combinations of ID week and year we should have data for. TableB is the data we actually have. I need to list out what we don't have yet, so the delta for B-A. How do I construct the query to find these missing values?

2
sql-server-2008-r2

2 Answers

1
votes

You can use NOT EXISTS

SELECT  [Year], [Week], ID 
FROM    TableA AS a
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    TableB AS b
            WHERE   b.[Year] = a.[Year]
            AND     b.[Week] = a.[Week]
            AND     b.ID = a.ID
        );
1
votes

You can use theexceptset operator to return the difference between two sets:

SELECT [Year], [Week], cmp_code FROM TableA
EXCEPT
SELECT [Year], [WeekNo], FranchiseID FROM TableB

This will return the rows in TableA that doesn't have exact matches in TableB. The same result can be achieved using a correlatednot existsquery, or aleft join. Thenot existsshould perform best.