August 18, 2015 at 2:01 am
Hi Guys,
Received a lot of help from SQL Guru WayneS on SQL listed below however since there are new requests from the client I thought I start a new topic.
My client wants IN_PUNCH to be the first in punch of the day and the OUT_PUNCH to be the last out punch of the day based on the TransactionDate as it is showing in the desired results table for 07/29 and 07/30 (Changes are highlighted in Bold).
I tried using MIN and MAX at different areas but to no avail.
Also, is there a way to add a counter at the end of each row so in case if there were two shifts in a day then it be a '1' for row 1 and a '2' for row 2?
Thanks a million for all your help..
Just an FYI that I'm trying to learn SQL tips and tricks from articles on this on site but it will take some time and a lot of practice to get there 🙂
http://www.sqlservercentral.com/articles/T-SQL/62867/
Thank you,
Please see example below.
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 2nd','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/29/2015','07/30/2015', '23:00','00:00','1', 'Regular', 'Batax 2nd','Wednesday' UNION ALL
SELECT 1234,'07/30/2015','07/30/2015','07/30/2015', '00:00','06:00','6', 'Regular', 'Batax 2nd','Thursday'
)
, cte2 AS (
SELECT PERSON, TRANSACTDATE, MIN(STARTDATE)STARTDATE, MAX (END_DATE) END_DATE
,CONVERT(VARCHAR(5), IN_PUNCH,108) IN_PUNCH
,CONVERT(VARCHAR(5), OUT_PUNCH,108) OUT_PUNCH
,SHIFT_LABEL, DOW,
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') 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') 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
GROUP BY PERSON
,TRANSACTDATE
,IN_PUNCH
,OUT_PUNCH
,HOURS
,PAYCODE
,SHIFT_LABEL
,DOW
)
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
-- get the max RN (ROW_NUMBER) for this person/date
CROSS APPLY (SELECT MAX(RN) FROM cte2 t2 WHERE t1.PERSON = t2.PERSON AND t1.TRANSACTDATE = t2.TRANSACTDATE) ca(MaxRN)
-- apply new rule
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 6.00 NULL0.00NULL 0.00 Batax 2ndWednesday
123407/30/201507/30/201507/30/201500:00 06:00 Regular 7.00 NULL0.00NULL 0.00 Batax 2ndThursday
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 13:00 Regular 6.00 NULL0.00NULL 0.00 Batax 2ndWednesday 1
123407/30/201507/29/201507/30/201523:00 06:00 Regular 7.00 NULL0.00NULL 0.00 Batax 2ndThursday 1
August 18, 2015 at 7:26 am
DiabloZA (8/18/2015)
Hi Guys,Received a lot of help from SQL Guru WayneS on SQL listed below however since there are new requests from the client I thought I start a new topic.
My client wants IN_PUNCH to be the first in punch of the day and the OUT_PUNCH to be the last out punch of the day based on the TransactionDate as it is showing in the desired results table for 07/29 and 07/30 (Changes are highlighted in Bold).
I tried using MIN and MAX at different areas but to no avail.
Also, is there a way to add a counter at the end of each row so in case if there were two shifts in a day then it be a '1' for row 1 and a '2' for row 2?
Thanks a million for all your help..
Just an FYI that I'm trying to learn SQL tips and tricks from articles on this on site but it will take some time and a lot of practice to get there 🙂
http://www.sqlservercentral.com/articles/T-SQL/62867/
Thank you,
Please see example below.
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 2nd','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/29/2015','07/30/2015', '23:00','00:00','1', 'Regular', 'Batax 2nd','Wednesday' UNION ALL
SELECT 1234,'07/30/2015','07/30/2015','07/30/2015', '00:00','06:00','6', 'Regular', 'Batax 2nd','Thursday'
)
, cte2 AS (
SELECT PERSON, TRANSACTDATE, MIN(STARTDATE)STARTDATE, MAX (END_DATE) END_DATE
,CONVERT(VARCHAR(5), IN_PUNCH,108) IN_PUNCH
,CONVERT(VARCHAR(5), OUT_PUNCH,108) OUT_PUNCH
,SHIFT_LABEL, DOW,
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') 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') 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
GROUP BY PERSON
,TRANSACTDATE
,IN_PUNCH
,OUT_PUNCH
,HOURS
,PAYCODE
,SHIFT_LABEL
,DOW
)
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
-- get the max RN (ROW_NUMBER) for this person/date
CROSS APPLY (SELECT MAX(RN) FROM cte2 t2 WHERE t1.PERSON = t2.PERSON AND t1.TRANSACTDATE = t2.TRANSACTDATE) ca(MaxRN)
-- apply new rule
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 6.00 NULL0.00NULL 0.00 Batax 2ndWednesday
123407/30/201507/30/201507/30/201500:00 06:00 Regular 7.00 NULL0.00NULL 0.00 Batax 2ndThursday
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 13:00 Regular 6.00 NULL0.00NULL 0.00 Batax 2ndWednesday 1
123407/30/201507/29/201507/30/201523:00 06:00 Regular 7.00 NULL0.00NULL 0.00 Batax 2ndThursday 1
Given the data, take a good close look at the punches that start at 23:00 on 7/29 and ends at 00:00 on 7/30. How does one judge which day that last punch belongs to, because if you want the last punch of the day, then for practical purposes, the person has "clocked" 1 hour of time, all of which occurs on 7/29, and the out punch just happens to also be the start of the next day... ??? A rather inconvenient fact from a processing perspective, as it certainly complicates matters, but that's why I'm asking what value the client wants in that specific circumstance. Of course, that leads to the next question: What do we do with a set of punches that perhaps represents a night shift worker who punches in at 11 pm and out at 7 am. Which day does that time period belong to?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 21, 2015 at 12:35 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply