SQL 2016 Need help with Comparing two tables for similarities and differences

Question

Post reply

SQL 2016 Need help with Comparing two tables for similarities and differences

DiabloSlayer

SSCrazy

Points: 2232

June 3, 2021 at 10:37 pm

#3891685

Hello Everyone,

I have two tables A & B as shown below, if table A has differences with table B then based on date column all records from table A should be sent in the output file.

In the attached example, records with date ending 5/24/2021 had changes between both tables so in the output file ALL records from table A for date ending 5/24/201 will be sent. However, records with date ending 5/31/2021 matched between both tables so nothing will be sent for from table A for date ending 5/31/2021.

I tried using EXCEPT, INTERSECT and UNION but nothing is returning the desired results.

Really appreciate the help and god bless !

DS

WITH SampleData1 (PERSON, [PAYCODE],[HOURS],[DATE],[SHIFT]) AS

(
    SELECT 121564,'110','15','05/24/2021','1'
    UNION ALL SELECT 121564,'117','32','05/24/2021','1'
    UNION ALL SELECT 121564,'Prem','32','05/24/2021','1'
    UNION ALL SELECT 121564,'Prem OT','1.5','05/24/2021','1'

    UNION ALL SELECT 121564,'110','30','05/31/2021','1'
    UNION ALL SELECT 121564,'111','7.5','05/31/2021','2'
    UNION ALL SELECT 121564,'117','15','05/31/2021','1'
    UNION ALL SELECT 121564,'126','0.5','05/31/2021','1'
)
SELECT * FROM SampleData1;

WITH SampleData2 (PERSON, [PAYCODE],[HOURS],[DATE],[SHIFT]) AS

(
    SELECT 121564,'110','15','05/24/2021','1'
    UNION ALL SELECT 121564,'115','24.5','05/24/2021','1'
    UNION ALL SELECT 121564,'117','32','05/24/2021','1'

    UNION ALL SELECT 121564,'110','30','05/31/2021','1'
    UNION ALL SELECT 121564,'111','7.5','05/31/2021','2'
    UNION ALL SELECT 121564,'117','15','05/31/2021','1'
    UNION ALL SELECT 121564,'126','0.5','05/31/2021','1'
)
SELECT * FROM SampleData2;

Desired results

PERSONPAYCODEHOURSDATESHIFT
1215641101505/24/20211
1215641173205/24/20211
121564Prem3205/24/20211
121564Prem OT1.505/24/20211

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MarcoAntonio SSC Veteran Points: 253 More actions · Answer 1

Is this what you're looking for?

select * from SampleData1
 where date in (
    select distinct sd1.date
      from SampleData1 sd1
           left outer join SampleData2 sd2 on sd1.date=sd2.date and sd1.person=sd2.person and sd1.paycode=sd2.paycode and sd1.hours=sd2.hours and sd1.shift=sd2.shift
     where sd2.date is null
 )

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 2

Hi Marco,

It works exactly how I want it but only when I have one employee's data as provided in the sample file.

When I include more employees in the table it returns both weeks data from table A.

Not sure why though.

Than you for helping out sir !

DS

MarcoAntonio SSC Veteran Points: 253 More actions · Answer 3

Glad to hear it's (mostly) doing what you're looking for. If I understand correctly, you only want to see all records for a given week where there's been a change for an individual 'person'. In that case this might do the trick:

select sd1.*
  from SampleData1 sd1
       inner join (select distinct sd1.date,sd1.person
                     from SampleData1 sd1
                          left outer join SampleData2 sd2 on sd1.date=sd2.date and sd1.person=sd2.person and sd1.paycode=sd2.paycode and sd1.hours=sd2.hours and sd1.shift=sd2.shift
                    where sd2.date is null
                  ) differences
                    on sd1.date=differences.DATE and sd1.PERSON=differences.PERSON