June 3, 2021 at 10:37 pm
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
June 4, 2021 at 2:46 pm
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
)
June 5, 2021 at 8:04 am
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
June 5, 2021 at 2:47 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply