Hi Guys,
So Wayne's solution worked perfectly until they changed the requirements a bit, now IN_PUNCH and OUT_PUNCH columns should be blank if there are only ABSNET_HRS in a day, when there are ABSENT_HRS in a day together with WORK_HRS or OT_HRS then the IN_PUNCH and OUT_PUNCH would be the latest pair from WORK_HRS or OT_HRS if no WORK_HRS present.
So basically everything remains the except the IN_PUNCH and OUT_PUNCH calculations, I have tried to play around with the query to no luck.
If leaving the IN_PUNCH and OUT_PUNCH blank is not possible as it shows on 7/27 in the desired results then we can leave it the way it is in the original results, but the results on 7/28 in the desired results are more important to the client.
Can you kindly help?
Thank you,
Please see example below.
WITH SampleData (PERSON,STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS,PAYCODE,SHIFT_LABEL,DOW) AS
(
SELECT 1234,'07/27/2015','07/27/2015', '12:00','12:00','8', 'Hol', 'NULL', 'Monday' UNION ALL
SELECT 1234,'07/28/2015','07/28/2015', '08:00','','4.00', 'Absent', 'Batam 1st','Tuesday' UNION ALL
SELECT 1234,'07/28/2015','07/28/2015', '12:15','14:00','3.75', 'Regular', 'Batam 1st','Tuesday' UNION ALL
SELECT 1234,'07/29/2015','07/29/2015', '06:00','10:00','4', 'Regular', 'Batam 2nd','Wednesday' UNION ALL
SELECT 1234,'07/29/2015','07/29/2015', '11:00','13:00','2', 'Regular', 'Batam 2nd','Wednesday'
)
, cte2 AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY SampleData.PERSON, SampleData.STARTDATE, SampleData.SHIFT_LABEL ORDER BY SampleData.IN_PUNCH) AS RN,
MAX(CASE WHEN PAYCODE IN ('Regular', 'Hol-Wrkd') THEN PAYCODE ELSE NULL END) OVER (PARTITION BY SampleData.PERSON, SampleData.STARTDATE, SampleData.SHIFT_LABEL) AS WORK_PAY,
SUM(CASE WHEN PAYCODE IN ('Regular', 'Hol-Wrkd') THEN CONVERT(DECIMAL(4,2), HOURS) ELSE 0.00 END) OVER (PARTITION BY SampleData.PERSON, SampleData.STARTDATE, SampleData.SHIFT_LABEL) AS WORK_HRS,
MAX(CASE WHEN PAYCODE IN ('Day-OT') THEN PAYCODE ELSE NULL END) OVER (PARTITION BY SampleData.PERSON, SampleData.STARTDATE, SampleData.SHIFT_LABEL) AS OT_PAY,
SUM(CASE WHEN PAYCODE IN ('Day-OT') THEN CONVERT(DECIMAL(4,2), HOURS) ELSE 0.00 END) OVER (PARTITION BY SampleData.PERSON, SampleData.STARTDATE, SampleData.SHIFT_LABEL) AS OT_HRS,
MAX(CASE WHEN PAYCODE IN ('Hol', 'Absent', 'EO', 'Personal') THEN PAYCODE ELSE NULL END) OVER (PARTITION BY SampleData.PERSON, SampleData.STARTDATE, SampleData.SHIFT_LABEL) AS ABSNT_PAY,
SUM(CASE WHEN PAYCODE IN ('Hol', 'Absent', 'EO', 'Personal') THEN CONVERT(DECIMAL(4,2), HOURS) ELSE 0.00 END) OVER (PARTITION BY SampleData.PERSON, SampleData.STARTDATE, SampleData.SHIFT_LABEL) AS ABSNT_HRS
FROM SampleData
)
SELECT PERSON, STARTDATE, END_DATE, IN_PUNCH, OUT_PUNCH,
WORK_PAY, WORK_HRS,
OT_PAY, OT_HRS,
ABSNT_PAY, ABSNT_HRS,
SHIFT_LABEL, DOW
FROM cte2
WHERE RN = 1
ORDER BY PERSON DESC, STARTDATE, IN_PUNCH;
The results from the above are as follows:
PERSONSTARTDATEEND_DATEIN_PUNCHOUT_PUNCHWORK_PAYWORK_HRSOT_PAYOT_HRSABSNT_PAYABSNT_HRSSHIFT_LABELDOW
123407/27/201507/27/201512:00 12:00 NULL 0.00 NULL0.00Hol 8.00 NULL Monday
123407/28/201507/28/201508:00 Regular 3.75 NULL0.00Absent 4.00 Batam 1stTuesday
123407/29/201507/29/201506:00 10:00 Regular 6.00 NULL0.00NULL 0.00 Batam 2ndWednesday
New desired results:
PERSONSTARTDATEEND_DATEIN_PUNCHOUT_PUNCHWORK_PAYWORK_HRSOT_PAYOT_HRSABSNT_PAYABSNT_HRSSHIFT_LABELDOW
123407/27/201507/27/2015 NULL 0.00 NULL0.00Hol 8.00 NULL Monday
123407/28/201507/28/201512:15 14:00 Regular 3.75 NULL0.00Absent 4.00 Batam 1stTuesday
123407/29/201507/29/201506:00 10:00 Regular 6.00 NULL0.00NULL 0.00 Batam 2ndWednesday