CODEWITHSUNDEEP
sql-server
skyline01
skyline01

Reputation: 2099

SQl Server query to find missing rows by groups

I am working in SQL Server 2016. I have 2 tables -- A and B. A sample of them is as follows:

A:
Product    Day          Job
x          2017-06-15   1
x          2017-06-15   2
x          2017-06-15   3
x          2017-06-14   1
x          2017-06-14   2
y          2017-06-15   1
y          2017-06-14   1
y          2017-06-14   2

B:
Product       Job
x             1
x             2
x             3
y             1
y             2

I want to show which Products and Days in table A are missing Job instances, as defined by table B. So, for example, I want to return:

Product       Day           Job
x             2017-06-14    3
y             2017-06-15    2

I'm struggling with figuring out the query. I can't do a straight FROM A RIGHT JOIN B ON B.Product = A.Product AND B.Job = A.Job WHERE A.Job IS NULL, since that is independent of Day. It's almost as if I need to chop up table A by day and join it against table B multiple times (once for each day), but joining multiple times is probably a bad idea. How can I achieve the output I want?

Upvotes: 0

Views: 303

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

You need to use a list of all the possible dates as another join predicate. In this example I created a cte of all the dates. Then you can get a listing all all jobs for all dates. From there it is a simple left join to a for the missing values.

Notice how I created tables and populated them with sample data. This is how you should post your sample data in the future. Makes it a lot easier for those trying to help. :)

DECLARE @a TABLE 
(
    Product CHAR(1)
    , JobDay DATE
    , Job TINYINT
)

INSERT @a
(
    Product,
    JobDay,
    Job
)
VALUES
('x', '2017-06-15', 1)
,('x', '2017-06-15', 2)
,('x', '2017-06-15', 3)
,('x', '2017-06-14', 1)
,('x', '2017-06-14', 2)
,('y', '2017-06-15', 1)
,('y', '2017-06-14', 1)
,('y', '2017-06-14', 2)

DECLARE @b TABLE
(
    Product CHAR(1)
    , Job tinyint
)

INSERT @b
(
    Product,
    Job
)
VALUES
('x', 1)
,('x', 2)
,('x', 3)
,('y', 1)
,('y', 2)
;

WITH AllDates AS
(
    SELECT DISTINCT JobDay 
    FROM @a
)

SELECT b.Product
    , ad.JobDay
    , b.Job
FROM @b b
CROSS JOIN AllDates ad
LEFT JOIN @a a ON a.Product = b.Product
    AND a.Job = b.Job
    AND a.JobDay = ad.JobDay
WHERE a.Job IS NULL
;

Upvotes: 2

Related Questions