Hi sgmunson,

Sorry for the late reply as I have been traveling and finally came back home to work on this again.

So an excellent question and the answer is that the hours belongs to out punch day based on their pay policies however now they don't care about this rule any longer so the first question is out the door 🙂

The second question was that is there a way to add a counter at the end of each row which is based on SHIFT_LABEL OR ABSENT_PAY PAYCODE (only when the PAYODDE is in Leave1 and Leave 2 on the same day) so in case if there were two shifts in a day OR PAYCODES of Leave1 and Leave2 on the same day then the last newly added COUNTER column could be a '1' for first shift and a '2' for row of 2nd shift OR a '1 for the Leave1 and a '2 for the Leave2?

Thank you,

Please see example below.

Thank you everyone,

WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS,PAYCODE,SHIFT_LABEL,DOW) AS

(

SELECT 1234,'07/27/2015','07/27/2015','07/27/2015', '12:00','12:00','8', 'Hol', 'NULL', 'Monday' UNION ALL

SELECT 1234,'07/28/2015','07/28/2015','07/28/2015', '08:00','','4.00', 'Absent', 'Batax 1st','Tuesday' UNION ALL

SELECT 1234,'07/28/2015','07/28/2015','07/28/2015', '12:15','14:00','3.75', 'Regular', 'Batax 1st','Tuesday' UNION ALL

SELECT 1234,'07/29/2015','07/29/2015','07/29/2015', '06:00','10:00','4', 'Regular', 'Batax 1st','Wednesday' UNION ALL

SELECT 1234,'07/29/2015','07/29/2015','07/29/2015', '11:00','13:00','2', 'Regular', 'Batax 2nd','Wednesday' UNION ALL

SELECT 1234,'07/30/2015','07/30/2015','07/30/2015', '08:00','14:00','6', 'Regular', 'Batax 1st','Thursday' UNION ALL

SELECT 1234,'07/31/2015','07/31/2015','07/31/2015', '08:00','12:00','4', 'Leave1', 'Batax 1st','Friday' UNION ALL

SELECT 1234,'07/31/2015','07/31/2015','07/31/2015', '13:00','17:00','4', 'Leave2', 'Batax 1st','Friday'

)

, cte2 AS (

SELECT *,

ROW_NUMBER() OVER (PARTITION BY SampleData.PERSON, SampleData.TRANSACTDATE, 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.TRANSACTDATE, 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.TRANSACTDATE, SampleData.SHIFT_LABEL) AS WORK_HRS,

MAX(CASE WHEN PAYCODE IN ('Day-OT') THEN PAYCODE ELSE NULL END) OVER (PARTITION BY SampleData.PERSON, SampleData.TRANSACTDATE, 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.TRANSACTDATE, SampleData.SHIFT_LABEL) AS OT_HRS,

MAX(CASE WHEN PAYCODE IN ('Hol', 'Absent', 'EO', 'Personal','Leave1','Leave2') THEN PAYCODE ELSE NULL END) OVER (PARTITION BY SampleData.PERSON, SampleData.TRANSACTDATE, SampleData.SHIFT_LABEL) AS ABSNT_PAY,

SUM(CASE WHEN PAYCODE IN ('Hol', 'Absent', 'EO', 'Personal','Leave1','Leave2') THEN CONVERT(DECIMAL(4,2), HOURS) ELSE 0.00 END) OVER (PARTITION BY SampleData.PERSON, SampleData.TRANSACTDATE, SampleData.SHIFT_LABEL) AS ABSNT_HRS

FROM SampleData

)

SELECT PERSON, TRANSACTDATE, STARTDATE, END_DATE,

-- apply new rule for blanking out these columns

CASE WHEN ABSNT_HRS > 0 AND WORK_HRS + OT_HRS = 0 THEN '' ELSE IN_PUNCH END AS IN_PUNCH,

CASE WHEN ABSNT_HRS > 0 AND WORK_HRS + OT_HRS = 0 THEN '' ELSE OUT_PUNCH END AS OUT_PUNCH,

WORK_PAY, WORK_HRS,

OT_PAY, OT_HRS,

ABSNT_PAY, ABSNT_HRS,

SHIFT_LABEL, DOW

FROM cte2 t1

CROSS APPLY (SELECT MAX(RN) FROM cte2 t2 WHERE t1.PERSON = t2.PERSON AND t1.TRANSACTDATE = t2.TRANSACTDATE) ca(MaxRN)

WHERE RN = CASE WHEN ABSNT_HRS > 0 AND WORK_HRS + OT_HRS > 0 THEN ca.MaxRN ELSE 1 END

ORDER BY PERSON DESC, TRANSACTDATE, IN_PUNCH;

The results from the above are as follows:

PERSONTRANSACTDATESTARTDATEEND_DATEIN_PUNCHOUT_PUNCHWORK_PAYWORK_HRSOT_PAYOT_HRSABSNT_PAYABSNT_HRSSHIFT_LABELDOW

123407/27/201507/27/201507/27/2015 NULL 0.00 NULL0.00Hol 8.00 NULL Monday

123407/28/201507/28/201507/28/201512:15 14:00 Regular 3.75 NULL0.00Absent 4.00 Batax 1stTuesday

123407/29/201507/29/201507/29/201506:00 10:00 Regular 4.00 NULL0.00NULL 0.00 Batax 1stWednesday

123407/29/201507/29/201507/29/201511:00 13:00 Regular 2.00 NULL0.00NULL 0.00 Batax 2ndWednesday

123407/30/201507/30/201507/30/201508:00 14:00 Regular 6.00 NULL0.00NULL 0.00 Batax 2ndThursday

123407/31/201507/31/201507/31/2015 NULL 0.00 NULL0.00Leave2 8.00 Batax 1stFriday

New desired results:

PERSONTRANSACTDATESTARTDATEEND_DATEIN_PUNCHOUT_PUNCHWORK_PAYWORK_HRSOT_PAYOT_HRSABSNT_PAYABSNT_HRSSHIFT_LABELDOW COUNTER

123407/27/201507/27/201507/27/2015 NULL 0.00 NULL0.00Hol 8.00 NULL Monday 1

123407/28/201507/28/201507/28/201512:15 14:00 Regular 3.75 NULL0.00Absent 4.00 Batax 1stTuesday 1

123407/29/201507/29/201507/29/201506:00 10:00 Regular 4.00 NULL0.00NULL 0.00 Batax 1stWednesday 1

123407/29/201507/29/201507/29/201511:00 13:00 Regular 2.00 NULL0.00NULL 0.00 Batax 2nd Wednesday 2

123407/30/201507/30/201507/30/201508:00 14:00 Regular 6.00 NULL0.00NULL 0.00 Batax 1stThursday 1

123407/31/201507/31/201507/31/201508:00 12:00 NULL 0.00 NULL0.00Leave1 4.00 Batax 1stFriday 1

123407/31/201507/31/201507/31/201513:00 17:00 NULL 0.00 NULL0.00Leave2 4.00 Batax 1stFriday 2