March 14, 2016 at 1:19 am
Hello Friends,
I have a challenging problem to solve !
I have two tables as shown below, I'm trying to join them together and come up with the following desired results.
1. If an employee has an schedule that matches the PERSON, STARTDATE, ENDDATE, STARTTIME and ENDTIME between both tables then I don't want the records in the results
2. If in the first table there is an IMPORT labeled record then I want this record in the result with a DELETE value in last column
3. If an MANUAL record from the first table does not have an exact match of PERSON, STARTDATE, ENDDATE, STARTTIME and ENDTIME with the second table then I need it in the results with an IGNORE in the last column
4. Else ALL records from the second table should be in the final result with a ADD in the last column
Thank you and really appreciate all the help I get from this wonderful group.
WITH SampleData_1 (PERSON,STARTDATE, END_DATE, STARTTIME,ENDTIME,DURATION, CODE) AS
(
SELECT 1234,'03/16/2016','03/16/2016', '06:00','14:00','08:30', 'MANUAL' UNION ALL
SELECT 1234,'03/17/2016','03/17/2016', '06:00','14:00','08:30', 'IMPORT' UNION ALL
SELECT 4567,'03/15/2016','03/15/2016', '07:30','15:00','07:30', 'MANUAL' UNION ALL
SELECT 7890,'03/16/2016','03/16/2016', '06:00','13:00','07:00', 'MANUAL'
)
SELECT *
FROM SampleData_1
;
WITH SampleData_2 (PERSON,STARTDATE, END_DATE, STARTTIME,ENDTIME,DURATION, CODE) AS
(
SELECT 1234,'03/14/2016','03/14/2016', '06:00','14:00','08:30', 'IMPORT' UNION ALL
SELECT 1234,'03/15/2016','03/15/2016', '06:00','14:00','08:30', 'IMPORT' UNION ALL
SELECT 1234,'03/16/2016','03/16/2016', '06:00','14:00','08:30', 'IMPORT' UNION ALL
SELECT 1234,'03/17/2016','03/17/2016', '06:00','14:00','08:30', 'IMPORT' UNION ALL
SELECT 4567,'03/14/2016','03/14/2016', '07:30','15:00','07:30', 'IMPORT' UNION ALL
SELECT 4567,'03/15/2016','03/15/2016', '07:30','15:00','07:30', 'IMPORT' UNION ALL
SELECT 4567,'03/16/2016','03/16/2016', '07:30','15:00','07:30', 'IMPORT' UNION ALL
SELECT 4567,'03/17/2016','03/17/2016', '07:30','15:30','07:30', 'IMPORT' UNION ALL
SELECT 7890,'03/14/2016','03/14/2016', '06:00','13:00','07:00', 'IMPORT' UNION ALL
SELECT 7890,'03/16/2016','03/16/2016', '07:00','11:30','04:30', 'IMPORT' UNION ALL
SELECT 7890,'03/17/2016','03/17/2016', '07:30','15:30','07:30', 'IMPORT'
)
SELECT *
FROM SampleData_2
Desired Results
PERSONSTARTDATEEND_DATESTARTTIMEENDTIMEDURATIONCODEADD_DELETE_IGNORE
123403/14/201603/14/201606:00 14:0008:30 IMPORTADD
123403/15/201603/15/201606:00 14:0008:30 IMPORTADD
123403/17/201603/17/201606:00 14:0008:30 IMPORTADD
123403/17/201603/17/201606:00 14:0008:30 IMPORTDELETE
456703/14/201603/14/201607:30 15:0007:30 IMPORTADD
456703/16/201603/16/201607:30 15:0007:30 IMPORTADD
456703/17/201603/17/201607:30 15:3007:30 IMPORTADD
789003/14/201603/14/201606:00 13:0007:00 IMPORTADD
789003/16/201603/16/201607:00 11:3004:30 IMPORTADD
789003/16/201603/16/201606:00 13:0007:00 MANUAL IGNORE
789003/17/201603/17/201607:30 15:3007:30 IMPORTADD
March 14, 2016 at 8:45 am
This looks like a rather contrived example for a FULL JOIN to me.
March 14, 2016 at 6:29 pm
DiabloSlayer (3/14/2016)
Hello Friends,I have a challenging problem to solve !
Not that challenging, really.
Just take each requirement 1 by 1 and put it in SQL.
1. If an employee has an schedule that matches the PERSON, STARTDATE, ENDDATE, STARTTIME and ENDTIME between both tables then I don't want the records in the results
OK.
That means, we need to have in each SELECT from a Table
WHERE NOT EXISTS (select from AnotherTable where {Matching Criteria}
2. If in the first table there is an IMPORT labeled record then I want this record in the result with a DELETE value in last column
OK, we need to select all records with 'IMPORT' and put 'DELETE' as a code instead:
SELECT PERSON,STARTDATE, END_DATE, STARTTIME,ENDTIME,DURATION, 'DELETE'
FROM SampleData_1
WHERE CODE = 'IMPORT'
-- don't forget the requirement from item 1
AND NOT EXISTS (select * from SampleData_2
where {Matching Criteria})
3. If an MANUAL record from the first table does not have an exact match of PERSON, STARTDATE, ENDDATE, STARTTIME and ENDTIME with the second table then I need it in the results with an IGNORE in the last column
Well, pretty much the same request, just use 'MANUAL' instead of 'IMPORT' and 'IGNORE' instead of 'DELETE':
SELECT PERSON,STARTDATE, END_DATE, STARTTIME,ENDTIME,DURATION, 'IGNORE'
FROM SampleData_1
WHERE CODE = 'MANUAL'
-- don't forget the requirement from item 1
AND NOT EXISTS (select * from SampleData_2
where {Matching Criteria})
4. Else ALL records from the second table should be in the final result with a ADD in the last column
Well all records, except the matching ones, as per item1.
That's straight forward:
SELECT PERSON,STARTDATE, END_DATE, STARTTIME,ENDTIME,DURATION, 'ADD'
FROM SampleData_2
WHERE NOT EXISTS (select * from SampleData_1
where {Matching Criteria})
Now you just put UNION ALL between those 3 queries and - here it is, your result.
You may wish to replace 2 almost identical queries from items 2 and 3 with this one:
SELECT PERSON,STARTDATE, END_DATE, STARTTIME,ENDTIME,DURATION,
CASE CODE WHEN 'IMPORT' THEN 'DELETE' WHEN 'MANUAL' THEN 'IGNORE' END
FROM SampleData_1
WHERE CODE IN ( 'IMPORT' , 'MANUAL' )
-- don't forget the requirement from item 1
AND NOT EXISTS (select * from SampleData_2
where {Matching Criteria})
Then you'll have just 2 queries to UNION ALL.
_____________
Code for TallyGenerator
March 15, 2016 at 12:42 am
Hi Sergiy,
Thank you for the quick help.
I'm not sure what to use in the {Matching Criteria} section of each query, here is the Item # 1 query.
Once I have it all I will UNION ALL 3 queries as you suggested.
SELECT PERSON,STARTDATE, END_DATE, STARTTIME,ENDTIME,DURATION, 'DELETE'
FROM SampleData_1
WHERE NOT EXISTS (select * from SampleData_2
where {Matching Criteria})
Can you kindly help further?
Best Regards,
March 15, 2016 at 5:25 am
Your description of what you want does not tie up with your results.
I agree with Sergiy, it is not complicated. Try playing with:
SELECT COALESCE(S1.PERSON, S2.PERSON) AS Person
,COALESCE(S1.STARTDATE, S2.STARTDATE) AS StartDate
,COALESCE(S1.END_DATE, S2.END_DATE) AS EndDate
,COALESCE(S1.STARTTIME, S2.STARTTIME) AS StartTime
,COALESCE(S1.ENDTIME, S2.ENDTIME) AS EndTime
,COALESCE(S1.DURATION, S2.DURATION) AS Duration
,COALESCE(S1.CODE, S2.CODE) AS Code
,CASE S1.CODE
WHEN 'IMPORT' THEN 'DELETE'
WHEN 'MANUAL' THEN 'IGNORE'
ELSE 'ADD'
END AS ADD_DELETE_IGNORE
FROM SampleData_1 S1
FULL JOIN SampleData_2 S2
ON S1.PERSON = S2.PERSON
AND S1.STARTDATE = S2.STARTDATE
AND S1.END_DATE = S2.END_DATE
AND S1.STARTTIME = S2.STARTTIME
AND S1.ENDTIME = S2.ENDTIME
WHERE (S2.PERSON IS NULL AND S1.CODE IN ('IMPORT','MANUAL'))
OR (S1.PERSON IS NULL)
ORDER BY Person, StartDate;
If you cannot get the FULL JOIN to work, break it down as Sergiy has shown you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply