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