asked on

TSQL return all non-matched values for a common column in two tables.

Windows 2019, Transact SQL.

I have a table called ProdMonth. It contains a column called Interval which is the first of each month since 2022-01-01.

I have another table called BillingDetail with a column called Interval that has the first day of the month matching other items in the record. There is also another Column called JobID. There can be many records with the same JobID and Interval.

I need a query that will return JobID, Interval pairs that do not have a match.

Say that there are BillingDetail records that have three different Intervals for the same JobID. I need to see JobId, Interval for all non-used Intervals.

I am flailing and I know this can't be hard.

Thank you.

Qlemo

select ref.*
  from (select distinct pm.Interval, bd.JobID
          from ProdMonth pm
          join BillingDetail bd
            on pm.Interval = bd.Interval
       ) as ref
left join BillingDetail bd
       on ref.Interval = bd.Interval and ref.JobID = bd.JobID
where bd.JobID is null

Open in new window

Anders Ebro (Microsoft MVP)

Personally I prefer the Except which is faster, and simpler to write

SELECT JobID ,Interval  FROM ProdMonth 
EXCEPT
SELECT JobID, Interval   FROM BillingDetail

Open in new window

PortletPaul

Are you trying to locate any intervals that are missing between the earliest and latest recorded intervals for the same jobid?

Recorded

Jobid Interval

Ab123 2023-07-01

Ab123 2023-09-01

So, you want?

Ab123 2023-08-01

Perhaps you could elaborate by supplying sample data and expected result.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer