TSQL Find Missing Values

Question

I have 2 tables:

TableA is a calendar of all possible weeks we should have sales data for per company.

TableB has financial data for sales per company per week that have been reported.

TableA Columns

[cmp_code, Year, Week]

TAbleB Columns

[cmp_code, Year, Week, sales]

Query Criteria:

1) List weeks missing from TableB for Current year and previous year for all companies

2) List the previous year sales value for that company and week if it exists. (example cmp 1234 is missing week 13, 2013, so show the value of that week in 2012)

I've tried joins but I either get 0 values returned or millions of values returned. I just don't really know where to even start.

I'm very new to SQL and greatly appreciate any help offered. Thanks in advance.

RBarryYoung RBarryYoung · Accepted Answer · 2013-09-24T16:34:13

This is one way:

SELECT TableA.*, bPrev.sales
FROM TableA
LEFT OUTER JOIN TableB
   ON  TableA.cmp_code = TableB.cmp_code
   And TableA.[Year]   = TableB.[Year]
   And TableA.[Week]   = TableB.[Week]
LEFT OUTER JOIN TableB As bPrev
   ON  TableA.cmp_code = bPrev.cmp_code
   And TableA.[Year]   = bPrev.[Year]+1
   And TableA.[Week]   = bPrev.[Week]
WHERE TableB.cmp_code IS NULL

TSQL Find Missing Values

1 Answers