find missing records in corresponding tables

Let’s say I have two temp tables with data that looks like this…

 #Temp1
 Id   IDNo       CdId       ApptDate        Proc
 1    X111       PP         3/3/2020        N
 2    X222       ZZ         3/3/2020        N
 3    X333       HH         3/3/2020        Y


 #Temp2
 ID  IDNo      CdID        ApptDate        Proc
 1   X111      PP          3/3/2020        Y
 2   X222      ZZ          3/3/2020        N
 3   X333      HH          3/3/2020        Y
 4   X444      BB          3/5/2020        Y

Here’s what I am trying to achieve

1) Exclude all records from #TEMP1 and #TEMP2 that have identical match on IdNO, CdId, ApptDate, PROC=Y

I’m only interesting in finding these scenarios:

1) Find record from #Temp1 or #Temp2 that matches on all columns, except PROC. So as an example, #Temp1 has a corresponding record in #Temp2. #Temp1 has PROC=N and #Temp2 has PROC=Y, so show the record in #TEMP1 and add a column like DESC = ‘TEMP1 record has Proc=N’

2) If #TEMP1 Or #TEMP2 does not have a corresponding record in either table, then show that record as DESC = ‘TEMP1 does not have this record), or vice versa.

3) If matching records exists in both table, but both records have PROC=N, then show both records with message ‘Temp1 record has Proc=N’ and ‘Temp2 record has Proc = N’

ALl in all the results I’m hoping to achieve are this:

ID    IdNo           CdID          ApptDate        DESC
 1    X111           PP            3/3/2020        'TEMP1 has Proc = N'
 2    X222           ZZ            3/3/2020        'TEMP1 has PROC = N'
 3    X222           ZZ            3/3/2020        'TEMP2 has PROC = N'
 4    X444           BB            3/5/2020        'TEMP 1 Does not have matching record'

In the results above

Row 1: This record exists in #Temp1 and #Temp2 but #Temp1 Proc = N

Row 2,3: This record exists in #Temp1 and #Temp2 but in both cases PROC = N

Row 4: This record exists in #Temp2 but does not exist in #Temp1

Answer

Hmmm . . . I think the logic you want uses a union all:

select t1.idno, t1.cdid, t1.apptdate,
       (case when t2.idno is not null then 'Temp1 has proc N'
             when t2.idno is null then 'Temp2 is missing' end)
from table1 t1 left join
     table2 t2
     on t1.idno = t2.idno and t1.cdid = t2.cdid and t1.apptdate = t2.appdate
where t1.proc = 'N'
union all
select t2.idno, t2.cdid, t2.apptdate,
       (case when t1.idno is not null then 'Temp2 has proc N'
             when t1.idno is null then 'Temp1 is missing' end)
from table1 t2 left join
     table2 t1
     on t1.idno = t2.idno and t1.cdid = t2.cdid and t1.apptdate = t2.appdate
where t2.proc = 'N';