July 29, 2015 at 12:27 pm
Hi Guys,
In need of some SQL help here, I've been trying to hammer this out since over the weekend and it's just not giving me what I need.
Here are the rules:
1. One row per day unless there are more than one shifts (column SHIFT_LABEL), on a given day. In my sample data I just have one shift on each day so it should be easier to combine the rows but not for me 🙁
2. For the IN_PUNCH and OUT_PUNCH columns Only use the earliest IN_PUNCH and OUT_PUNCH times
3. Total all regular paycode hours such Regular and Hol-Wrkd into a new column called WORK_HRS
4. Total all Overtime paycode hours such as Day-OT into a new column called OT_HRS
5. Total all absent paycode hours such as Hol, Absent, Personal and EO into a new column called ABSENT_HRS
6. Each new total hours column have their respective new paycode holder columns such as WORK_PAY, OT_PAY and ABSENT_PAY
If anyone can help me out on this that will be awesome.
My desired results are listed below so please help if you can and I will be very grateful.
Thank you,
Sample Data:
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', '06:15','10:00','3.75','Regular','Batam 1st','Tuesday'
UNION ALL SELECT 1234,'07/28/2015','07/28/2015', '11:00','11:00','0','Absent','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'
UNION ALL SELECT 1234,'07/30/2015','07/30/2015', '12:00','12:00','8','Personal','Batam 1st','Thursday'
UNION ALL SELECT 1234,'07/31/2015','07/31/2015', '07:00','12:00','5','Regular','Batam 1st','Friday'
UNION ALL SELECT 1234,'07/31/2015','07/31/2015', '13:00','16:00','3','Regular','Batam 1st','Friday'
UNION ALL SELECT 1234,'07/31/2015','07/31/2015', '16:00','18:55','2.92','Day-OT','Batam 1st','Friday'
UNION ALL SELECT 1234,'08/01/2015','08/01/2015', '12:00','12:00','0','NULL','Batam 1st','Saturday'
UNION ALL SELECT 11,'07/27/2015','07/27/2015', '12:00','12:00','8','Hol','Batam Nor','Monday'
UNION ALL SELECT 11,'07/27/2015','07/27/2015', '08:00','10:00','2','Hol-Wrkd','Batam Nor','Monday'
UNION ALL SELECT 11,'07/28/2015','07/28/2015', '08:00','12:00','4','Regular','Batam Nor','Tuesday'
UNION ALL SELECT 11,'07/28/2015','07/28/2015', '12:00','12:00','1','EO','Batam Nor','Tuesday'
UNION ALL SELECT 11,'07/28/2015','07/28/2015', '13:00','16:00','3','Regular','Batam Nor','Tuesday'
UNION ALL SELECT 11,'07/29/2015','07/29/2015', '06:00','10:00','4','Regular','Batam Nor','Wednesday'
UNION ALL SELECT 11,'07/29/2015','07/29/2015', '11:00','13:00','2','Regular','Batam Nor','Wednesday'
UNION ALL SELECT 11,'07/30/2015','07/30/2015', '08:00','12:00','4','Regular','Batam Nor','Thursday'
UNION ALL SELECT 11,'07/30/2015','07/30/2015', '13:00','17:00','4','Regular','Batam Nor','Thursday'
UNION ALL SELECT 11,'07/31/2015','07/31/2015', '12:00','12:00','0','NULL','Batam Nor','Friday'
UNION ALL SELECT 11,'08/01/2015','08/01/2015', '08:00','12:00','4','Regular','Batam Nor','Saturday'
UNION ALL SELECT 11,'08/01/2015','08/01/2015', '13:00','14:00','1','Regular','Batam Nor','Saturday'
)
SELECT *
FROM SampleData
The results from the above are as follows:
PERSON START_DATE END_DATE IN_PUNCH OUT_PUNCH HOURS PAYCODE SHIFT_LABEL DOW
123407/27/201507/27/201512:0012:00 8 Hol NULL Monday
123407/28/201507/28/201506:1510:00 3.75 RegularBatam 1stTuesday
123407/28/201507/28/201511:0011:00 0 AbsentBatam 1stTuesday
123407/29/201507/29/201506:0010:00 4 RegularBatam 2ndWednesday
123407/29/201507/29/201511:0013:00 2 RegularBatam 2ndWednesday
123407/30/201507/30/201512:0012:00 8 PersonalBatam 1stThursday
123407/31/201507/31/201507:0012:00 5 RegularBatam 1stFriday
123407/31/201507/31/201513:0016:00 3 RegularBatam 1stFriday
123407/31/201507/31/201516:0018:55 2.92 Day-OTBatam 1stFriday
123408/01/201508/01/201512:0012:00 0 NULL Batam 1stSaturday
1107/27/201507/27/201508:0010:00 2 Hol-WrkdBatam NorMonday
1107/28/201507/28/201508:0012:00 4 RegularBatam NorTuesday
1107/28/201507/28/201512:0012:00 1 EO Batam NorTuesday
1107/28/201507/28/201513:0016:00 3 RegularBatam NorTuesday
1107/29/201507/29/201506:0010:00 4 RegularBatam NorWednesday
1107/29/201507/29/201511:0013:00 4 RegularBatam NorThursday
1107/30/201507/30/201513:0017:00 4 RegularBatam NorThursday
1107/31/201507/31/201512:0012:00 0 NULL Batam NorFriday
1108/01/201508/01/201508:0012:00 4 RegularBatam NorSaturday
1108/01/201508/01/201513:0014:00 1 RegularBatam NorSaturday
My desired results with desired headers are as follows - Sorry about the column alignment I tried to fix it to make it look as clean as possible:
PERSONSTARTDATEEND_DATEIN_PUNCHOUT_PUNCH WORK_PAY WORK_HRS OT_PAYOT_HRS ABSNT_PAY ABSNT_HRS SHIFT_LABELDOW
123407/27/201507/27/201512:0012:00 NULL 0 NULL0Hol 8 SHIFTMonday
123407/28/201507/28/201506:1510:00 Regular 3.75 NULL0Absent 0 SHIFTTuesday
123407/29/201507/29/201506:0010:00 Regular 6 NULL0NULL 0 SHIFTWednesday
123407/30/201507/30/201512:0012:00 NULL 0 NULL0Personal 8 SHIFTThursday
123407/31/201507/31/201507:0012:00 Regular 8 Day-OT2.92NULL 0 SHIFTFriday
123408/01/201508/01/201512:0012:00 NULL 0 NULL0NULL 0 SHIFTSaturday
1107/27/201507/27/201512:0012:00 Hol-Wrkd 2 NULL0Hol 8 NORMALMonday
1107/28/201507/28/201508:0012:00 Regular 7 NULL0EO 1 NORMALTuesday
1107/29/201507/29/201506:0010:00 Regular 6 NULL0NULL 0 NORMALWednesday
1107/30/201507/30/201508:0012:00 Regular 8 NULL0NULL 0 NORMALThursday
1107/31/201507/31/201512:0012:00 NULL 0 NULL0NULL 0 SHIFTFriday
1108/01/201508/01/201508:0012:00 Regular 5 NULL0NULL 0 SHIFTSaturday
July 29, 2015 at 12:58 pm
Can you state what the actual datatypes are for these columns? I hope they are not all varchars. Is there a reason that In_Punch and Out_Punch aren't just a single datetime column? Seems that you will have to constantly concatenate columns together to get the actual datetime. Also, your sample data has a bunch of 'NULL' scattered around. Are these supposed to actually be NULL or are they the string "NULL"???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 29, 2015 at 1:23 pm
DiabloZA (7/29/2015)
Hi Guys,In need of some SQL help here, I've been trying to hammer this out since over the weekend and it's just not giving me what I need.
Here are the rules:
1. One row per day unless there are more than one shifts (column SHIFT_LABEL), on a given day. In my sample data I just have one shift on each day so it should be easier to combine the rows but not for me 🙁
2. For the IN_PUNCH and OUT_PUNCH columns Only use the earliest IN_PUNCH and OUT_PUNCH times
3. Total all regular paycode hours such Regular and Hol-Wrkd into a new column called WORK_HRS
4. Total all Overtime paycode hours such as Day-OT into a new column called OT_HRS
5. Total all absent paycode hours such as Hol, Absent, Personal and EO into a new column called ABSENT_HRS
6. Each new total hours column have their respective new paycode holder columns such as WORK_PAY, OT_PAY and ABSENT_PAY
If anyone can help me out on this that will be awesome.
My desired results are listed below so please help if you can and I will be very grateful.
Thank you,
Sample Data:
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', '06:15','10:00','3.75','Regular','Batam 1st','Tuesday'
UNION ALL SELECT 1234,'07/28/2015','07/28/2015', '11:00','11:00','0','Absent','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'
UNION ALL SELECT 1234,'07/30/2015','07/30/2015', '12:00','12:00','8','Personal','Batam 1st','Thursday'
UNION ALL SELECT 1234,'07/31/2015','07/31/2015', '07:00','12:00','5','Regular','Batam 1st','Friday'
UNION ALL SELECT 1234,'07/31/2015','07/31/2015', '13:00','16:00','3','Regular','Batam 1st','Friday'
UNION ALL SELECT 1234,'07/31/2015','07/31/2015', '16:00','18:55','2.92','Day-OT','Batam 1st','Friday'
UNION ALL SELECT 1234,'08/01/2015','08/01/2015', '12:00','12:00','0','NULL','Batam 1st','Saturday'
UNION ALL SELECT 11,'07/27/2015','07/27/2015', '12:00','12:00','8','Hol','Batam Nor','Monday'
UNION ALL SELECT 11,'07/27/2015','07/27/2015', '08:00','10:00','2','Hol-Wrkd','Batam Nor','Monday'
UNION ALL SELECT 11,'07/28/2015','07/28/2015', '08:00','12:00','4','Regular','Batam Nor','Tuesday'
UNION ALL SELECT 11,'07/28/2015','07/28/2015', '12:00','12:00','1','EO','Batam Nor','Tuesday'
UNION ALL SELECT 11,'07/28/2015','07/28/2015', '13:00','16:00','3','Regular','Batam Nor','Tuesday'
UNION ALL SELECT 11,'07/29/2015','07/29/2015', '06:00','10:00','4','Regular','Batam Nor','Wednesday'
UNION ALL SELECT 11,'07/29/2015','07/29/2015', '11:00','13:00','2','Regular','Batam Nor','Wednesday'
UNION ALL SELECT 11,'07/30/2015','07/30/2015', '08:00','12:00','4','Regular','Batam Nor','Thursday'
UNION ALL SELECT 11,'07/30/2015','07/30/2015', '13:00','17:00','4','Regular','Batam Nor','Thursday'
UNION ALL SELECT 11,'07/31/2015','07/31/2015', '12:00','12:00','0','NULL','Batam Nor','Friday'
UNION ALL SELECT 11,'08/01/2015','08/01/2015', '08:00','12:00','4','Regular','Batam Nor','Saturday'
UNION ALL SELECT 11,'08/01/2015','08/01/2015', '13:00','14:00','1','Regular','Batam Nor','Saturday'
)
SELECT *
FROM SampleData
The results from the above are as follows:
PERSON START_DATE END_DATE IN_PUNCH OUT_PUNCH HOURS PAYCODE SHIFT_LABEL DOW
123407/27/201507/27/201512:0012:00 8 Hol NULL Monday
123407/28/201507/28/201506:1510:00 3.75 RegularBatam 1stTuesday
123407/28/201507/28/201511:0011:00 0 AbsentBatam 1stTuesday
123407/29/201507/29/201506:0010:00 4 RegularBatam 2ndWednesday
123407/29/201507/29/201511:0013:00 2 RegularBatam 2ndWednesday
123407/30/201507/30/201512:0012:00 8 PersonalBatam 1stThursday
123407/31/201507/31/201507:0012:00 5 RegularBatam 1stFriday
123407/31/201507/31/201513:0016:00 3 RegularBatam 1stFriday
123407/31/201507/31/201516:0018:55 2.92 Day-OTBatam 1stFriday
123408/01/201508/01/201512:0012:00 0 NULL Batam 1stSaturday
1107/27/201507/27/201508:0010:00 2 Hol-WrkdBatam NorMonday
1107/28/201507/28/201508:0012:00 4 RegularBatam NorTuesday
1107/28/201507/28/201512:0012:00 1 EO Batam NorTuesday
1107/28/201507/28/201513:0016:00 3 RegularBatam NorTuesday
1107/29/201507/29/201506:0010:00 4 RegularBatam NorWednesday
1107/29/201507/29/201511:0013:00 4 RegularBatam NorThursday
1107/30/201507/30/201513:0017:00 4 RegularBatam NorThursday
1107/31/201507/31/201512:0012:00 0 NULL Batam NorFriday
1108/01/201508/01/201508:0012:00 4 RegularBatam NorSaturday
1108/01/201508/01/201513:0014:00 1 RegularBatam NorSaturday
My desired results with desired headers are as follows - Sorry about the column alignment I tried to fix it to make it look as clean as possible:
PERSONSTARTDATEEND_DATEIN_PUNCHOUT_PUNCH WORK_PAY WORK_HRS OT_PAYOT_HRS ABSNT_PAY ABSNT_HRS SHIFT_LABELDOW
123407/27/201507/27/201512:0012:00 NULL 0 NULL0Hol 8 SHIFTMonday
123407/28/201507/28/201506:1510:00 Regular 3.75 NULL0Absent 0 SHIFTTuesday
123407/29/201507/29/201506:0010:00 Regular 6 NULL0NULL 0 SHIFTWednesday
123407/30/201507/30/201512:0012:00 NULL 0 NULL0Personal 8 SHIFTThursday
123407/31/201507/31/201507:0012:00 Regular 8 Day-OT2.92NULL 0 SHIFTFriday
123408/01/201508/01/201512:0012:00 NULL 0 NULL0NULL 0 SHIFTSaturday
1107/27/201507/27/201512:0012:00 Hol-Wrkd 2 NULL0Hol 8 NORMALMonday
1107/28/201507/28/201508:0012:00 Regular 7 NULL0EO 1 NORMALTuesday
1107/29/201507/29/201506:0010:00 Regular 6 NULL0NULL 0 NORMALWednesday
1107/30/201507/30/201508:0012:00 Regular 8 NULL0NULL 0 NORMALThursday
1107/31/201507/31/201512:0012:00 NULL 0 NULL0NULL 0 SHIFTFriday
1108/01/201508/01/201508:0012:00 Regular 5 NULL0NULL 0 SHIFTSaturday
Hi Diablo,
This code gets us about 95% there. The one part that I'm still missing is what are the rules for SHIFT_LABEL being "SHIFT", and for "NORMAL".
Edit: I also have one discrepancy between your output and the rules. PERSON=11, STARTDATE="07/27/2015" - I have the IN_PUNCH being "08:00", and you have "12:00", even though the rule states the earliest. How is "12:00" before "08:00"?
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', '06:15','10:00','3.75', 'Regular', 'Batam 1st','Tuesday' UNION ALL
SELECT 1234,'07/28/2015','07/28/2015', '11:00','11:00','0', 'Absent', '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' UNION ALL
SELECT 1234,'07/30/2015','07/30/2015', '12:00','12:00','8', 'Personal', 'Batam 1st','Thursday' UNION ALL
SELECT 1234,'07/31/2015','07/31/2015', '07:00','12:00','5', 'Regular', 'Batam 1st','Friday' UNION ALL
SELECT 1234,'07/31/2015','07/31/2015', '13:00','16:00','3', 'Regular', 'Batam 1st','Friday' UNION ALL
SELECT 1234,'07/31/2015','07/31/2015', '16:00','18:55','2.92', 'Day-OT', 'Batam 1st','Friday' UNION ALL
SELECT 1234,'08/01/2015','08/01/2015', '12:00','12:00','0', 'NULL', 'Batam 1st','Saturday' UNION ALL
SELECT 11, '07/27/2015','07/27/2015', '12:00','12:00','8', 'Hol', 'Batam Nor','Monday' UNION ALL
SELECT 11, '07/27/2015','07/27/2015', '08:00','10:00','2', 'Hol-Wrkd', 'Batam Nor','Monday' UNION ALL
SELECT 11, '07/28/2015','07/28/2015', '08:00','12:00','4', 'Regular', 'Batam Nor','Tuesday' UNION ALL
SELECT 11, '07/28/2015','07/28/2015', '12:00','12:00','1', 'EO', 'Batam Nor','Tuesday' UNION ALL
SELECT 11, '07/28/2015','07/28/2015', '13:00','16:00','3', 'Regular', 'Batam Nor','Tuesday' UNION ALL
SELECT 11, '07/29/2015','07/29/2015', '06:00','10:00','4', 'Regular', 'Batam Nor','Wednesday' UNION ALL
SELECT 11, '07/29/2015','07/29/2015', '11:00','13:00','2', 'Regular', 'Batam Nor','Wednesday' UNION ALL
SELECT 11, '07/30/2015','07/30/2015', '08:00','12:00','4', 'Regular', 'Batam Nor','Thursday' UNION ALL
SELECT 11, '07/30/2015','07/30/2015', '13:00','17:00','4', 'Regular', 'Batam Nor','Thursday' UNION ALL
SELECT 11, '07/31/2015','07/31/2015', '12:00','12:00','0', 'NULL', 'Batam Nor','Friday' UNION ALL
SELECT 11, '08/01/2015','08/01/2015', '08:00','12:00','4', 'Regular', 'Batam Nor','Saturday' UNION ALL
SELECT 11, '08/01/2015','08/01/2015', '13:00','14:00','1', 'Regular', 'Batam Nor','Saturday'
)
, 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;
Results:
PERSON STARTDATE END_DATE IN_PUNCH OUT_PUNCH WORK_PAY WORK_HRS OT_PAY OT_HRS ABSNT_PAY ABSNT_HRS SHIFT_LABEL DOW
----------- ---------- ---------- -------- --------- -------- --------------------------------------- -------- --------------------------------------- --------- --------------------------------------- ----------- ---------
1234 07/27/2015 07/27/2015 12:00 12:00 NULL 0.00 NULL 0.00 Hol 8.00 NULL Monday
1234 07/28/2015 07/28/2015 06:15 10:00 Regular 3.75 NULL 0.00 Absent 0.00 Batam 1st Tuesday
1234 07/29/2015 07/29/2015 06:00 10:00 Regular 6.00 NULL 0.00 NULL 0.00 Batam 2nd Wednesday
1234 07/30/2015 07/30/2015 12:00 12:00 NULL 0.00 NULL 0.00 Personal 8.00 Batam 1st Thursday
1234 07/31/2015 07/31/2015 07:00 12:00 Regular 8.00 Day-OT 2.92 NULL 0.00 Batam 1st Friday
1234 08/01/2015 08/01/2015 12:00 12:00 NULL 0.00 NULL 0.00 NULL 0.00 Batam 1st Saturday
11 07/27/2015 07/27/2015 08:00 10:00 Hol-Wrkd 2.00 NULL 0.00 Hol 8.00 Batam Nor Monday
11 07/28/2015 07/28/2015 08:00 12:00 Regular 7.00 NULL 0.00 EO 1.00 Batam Nor Tuesday
11 07/29/2015 07/29/2015 06:00 10:00 Regular 6.00 NULL 0.00 NULL 0.00 Batam Nor Wednesday
11 07/30/2015 07/30/2015 08:00 12:00 Regular 8.00 NULL 0.00 NULL 0.00 Batam Nor Thursday
11 07/31/2015 07/31/2015 12:00 12:00 NULL 0.00 NULL 0.00 NULL 0.00 Batam Nor Friday
11 08/01/2015 08/01/2015 08:00 12:00 Regular 5.00 NULL 0.00 NULL 0.00 Batam Nor Saturday
I've used the SQL Window Functions here. You can read more about them in my book - the link is in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 29, 2015 at 2:16 pm
Hi Sean,
They are not all varchar, STARTDATE and ENDDATE are datetime but in my sample data I used the 'CONERT(VARCHAR(10), STARTDATE, 101) method to display the results cleaner.
They (the client) would like to see the IN_PUNCH and OUT_PUNCH on separate columns as times so I used 'CONVERT(VARCHAR(5), STARTDATE, 108) method.
As for NULL values, they are actually NULL.
Thank you again for helping out ..:)
(PERSON VARCHAR(20)NOT NULL,
STARTDATE DATETIME,
ENDDATE DATETIME,
IN_PUNCH VARCHAR(20),
OUT_PUNCH VARCHAR(20),
HOURS decimal (6,3),
PAYCODE VARCHAR(60),
SHIFT_LABEL VARCHAR(20),
DOW VARCHAR(20)
)
July 29, 2015 at 2:49 pm
Hi Wayne,
I can remove the SHIFTS_LABEL column as it will confuse the matter more, I can always add it back on later once the main query is outputting the desired results. Sorry about this.
You are right about the discrepancy for PERSON = 11, he should have had two rows for 07/27/2015.
as below:
1107/27/201507/27/201512:0012:00 2 Hol-WrkdBatam NorMonday
1107/27/201507/27/201508:0010:00 2 Hol-WrkdBatam NorMonday
In each row if there no data to insert I'm just putting a NULL but we can leave it BLANK as well.
I have adjusted the sample data, the output results and desired results below
Sample Data:
WITH SampleData (PERSON,STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS,PAYCODE,DOW) AS
(
SELECT 1234,'07/27/2015','07/27/2015', '12:00','12:00','8','Hol','Monday'
UNION ALL SELECT 1234,'07/28/2015','07/28/2015', '06:15','10:00','3.75','Regular','Tuesday'
UNION ALL SELECT 1234,'07/28/2015','07/28/2015', '11:00','11:00','0','Absent','Tuesday'
UNION ALL SELECT 1234,'07/29/2015','07/29/2015', '06:00','10:00','4','Regular','Wednesday'
UNION ALL SELECT 1234,'07/29/2015','07/29/2015', '11:00','13:00','2','Regular','Wednesday'
UNION ALL SELECT 1234,'07/30/2015','07/30/2015', '12:00','12:00','8','Personal','Thursday'
UNION ALL SELECT 1234,'07/31/2015','07/31/2015', '07:00','12:00','5','Regular','Friday'
UNION ALL SELECT 1234,'07/31/2015','07/31/2015', '13:00','16:00','3','Regular','Friday'
UNION ALL SELECT 1234,'07/31/2015','07/31/2015', '16:00','18:55','2.92','Day-OT','Friday'
UNION ALL SELECT 1234,'08/01/2015','08/01/2015', '12:00','12:00','0','NULL','Saturday'
UNION ALL SELECT 11,'07/27/2015','07/27/2015', '08:00','10:00','8','Hol','Monday'
UNION ALL SELECT 11,'07/27/2015','07/27/2015', '08:00','10:00','2','Hol-Wrkd','Monday'
UNION ALL SELECT 11,'07/28/2015','07/28/2015', '08:00','12:00','4','Regular','Tuesday'
UNION ALL SELECT 11,'07/28/2015','07/28/2015', '12:00','12:00','1','EO','Tuesday'
UNION ALL SELECT 11,'07/28/2015','07/28/2015', '13:00','16:00','3','Regular','Tuesday'
UNION ALL SELECT 11,'07/29/2015','07/29/2015', '06:00','10:00','4','Regular','Wednesday'
UNION ALL SELECT 11,'07/29/2015','07/29/2015', '11:00','13:00','2','Regular','Wednesday'
UNION ALL SELECT 11,'07/30/2015','07/30/2015', '08:00','12:00','4','Regular','Thursday'
UNION ALL SELECT 11,'07/30/2015','07/30/2015', '13:00','17:00','4','Regular','Thursday'
UNION ALL SELECT 11,'07/31/2015','07/31/2015', '12:00','12:00','0','NULL','Friday'
UNION ALL SELECT 11,'08/01/2015','08/01/2015', '08:00','12:00','4','Regular','Saturday'
UNION ALL SELECT 11,'08/01/2015','08/01/2015', '13:00','14:00','1','Regular','Saturday'
)
SELECT *
FROM SampleData
The results from the above are as follows:
PERSON START_DATE END_DATE IN_PUNCH OUT_PUNCH HOURS PAYCODE DOW
123407/27/201507/27/201512:0012:00 8 Hol Monday
123407/28/201507/28/201506:1510:00 3.75 Regular Tuesday
123407/28/201507/28/201511:0011:00 0 Absent Tuesday
123407/29/201507/29/201506:0010:00 4 Regular Wednesday
123407/29/201507/29/201511:0013:00 2 Regular Wednesday
123407/30/201507/30/201512:0012:00 8 Personal Thursday
123407/31/201507/31/201507:0012:00 5 Regular Friday
123407/31/201507/31/201513:0016:00 3 Regular Friday
123407/31/201507/31/201516:0018:55 2.92 Day-OT Friday
123408/01/201508/01/201512:0012:00 0 NULL Saturday
1107/27/201507/27/201508:0010:00 2 Hol-Wrkd Monday
1107/27/201507/27/201512:0012:00 8 Hol Monday
1107/28/201507/28/201508:0012:00 4 Regular Tuesday
1107/28/201507/28/201512:0012:00 1 EO Tuesday
1107/28/201507/28/201513:0016:00 3 Regular Tuesday
1107/29/201507/29/201506:0010:00 4 Regular Wednesday
1107/29/201507/29/201511:0013:00 4 Regular Thursday
1107/30/201507/30/201513:0017:00 4 Regular Thursday
1107/31/201507/31/201512:0012:00 0 NULL Friday
1108/01/201508/01/201508:0012:00 4 Regular Saturday
1108/01/201508/01/201513:0014:00 1 Regular Saturday
My desired results with desired headers are as follows :
PERSONSTARTDATEEND_DATEIN_PUNCHOUT_PUNCH WORK_PAY WORK_HRS OT_PAYOT_HRS ABSNT_PAY ABSNT_HRS DOW
123407/27/201507/27/201512:0012:00 NULL 0 NULL0Hol 8 Monday
123407/28/201507/28/201506:1510:00 Regular 3.75 NULL0Absent 0 Tuesday
123407/29/201507/29/201506:0010:00 Regular 6 NULL0NULL 0 Wednesday
123407/30/201507/30/201512:0012:00 NULL 0 NULL0Personal 8 Thursday
123407/31/201507/31/201507:0012:00 Regular 8 Day-OT2.92NULL 0 Friday
123408/01/201508/01/201512:0012:00 NULL 0 NULL0NULL 0 Saturday
1107/27/201507/27/201508:0010:00 Hol-Wrkd 2 NULL0Hol 8 Monday
1107/28/201507/28/201508:0012:00 Regular 7 NULL0EO 1 Tuesday
1107/29/201507/29/201506:0010:00 Regular 6 NULL0NULL 0 Wednesday
1107/30/201507/30/201508:0012:00 Regular 8 NULL0NULL 0 Thursday
1107/31/201507/31/201512:0012:00 NULL 0 NULL0NULL 0 Friday
1108/01/201508/01/201508:0012:00 Regular 5 NULL0NULL 0 Saturday
July 29, 2015 at 3:46 pm
Hi Wayne,
Thank you looking into this for me, I could and have removed the SHIFTS as I think this would just create more confusion, I can always add it back in the logic later once the main SQL query is producing the desired results.
And, you are right about the discrepancy and I have fixed it below.
Sorry about that and THANK YOU again..
Here are the updated Sample Data, Current and Desired Output.
Sample Data:
WITH SampleData (PERSON,STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS,PAYCODE,DOW) AS
(
SELECT 1234,'07/27/2015','07/27/2015', '12:00','12:00','8','Hol','Monday'
UNION ALL SELECT 1234,'07/28/2015','07/28/2015', '06:15','10:00','3.75','Regular','Tuesday'
UNION ALL SELECT 1234,'07/28/2015','07/28/2015', '11:00','11:00','0','Absent','Tuesday'
UNION ALL SELECT 1234,'07/29/2015','07/29/2015', '06:00','10:00','4','Regular','Wednesday'
UNION ALL SELECT 1234,'07/29/2015','07/29/2015', '11:00','13:00','2','Regular','Wednesday'
UNION ALL SELECT 1234,'07/30/2015','07/30/2015', '12:00','12:00','8','Personal','Thursday'
UNION ALL SELECT 1234,'07/31/2015','07/31/2015', '07:00','12:00','5','Regular','Friday'
UNION ALL SELECT 1234,'07/31/2015','07/31/2015', '13:00','16:00','3','Regular','Friday'
UNION ALL SELECT 1234,'07/31/2015','07/31/2015', '16:00','18:55','2.92','Day-OT','Friday'
UNION ALL SELECT 1234,'08/01/2015','08/01/2015', '12:00','12:00','0','NULL','Saturday'
UNION ALL SELECT 11,'07/27/2015','07/27/2015', '12:00','12:00','8','Hol','Monday'
UNION ALL SELECT 11,'07/27/2015','07/27/2015', '08:00','10:00','2','Hol-Wrkd','Monday'
UNION ALL SELECT 11,'07/28/2015','07/28/2015', '08:00','12:00','4','Regular','Tuesday'
UNION ALL SELECT 11,'07/28/2015','07/28/2015', '12:00','12:00','1','EO','Tuesday'
UNION ALL SELECT 11,'07/28/2015','07/28/2015', '13:00','16:00','3','Regular','Tuesday'
UNION ALL SELECT 11,'07/29/2015','07/29/2015', '06:00','10:00','4','Regular','Wednesday'
UNION ALL SELECT 11,'07/29/2015','07/29/2015', '11:00','13:00','2','Regular','Wednesday'
UNION ALL SELECT 11,'07/30/2015','07/30/2015', '08:00','12:00','4','Regular','Thursday'
UNION ALL SELECT 11,'07/30/2015','07/30/2015', '13:00','17:00','4','Regular','Thursday'
UNION ALL SELECT 11,'07/31/2015','07/31/2015', '12:00','12:00','0','NULL','Friday'
UNION ALL SELECT 11,'08/01/2015','08/01/2015', '08:00','12:00','4','Regular','Saturday'
UNION ALL SELECT 11,'08/01/2015','08/01/2015', '13:00','14:00','1','Regular','Saturday'
)
SELECT *
FROM SampleData
The results from the above are as follows:
PERSON START_DATE END_DATE IN_PUNCH OUT_PUNCH HOURS PAYCODE DOW
123407/27/201507/27/201512:0012:00 8 Hol Monday
123407/28/201507/28/201506:1510:00 3.75 Regular Tuesday
123407/28/201507/28/201511:0011:00 0 Absent Tuesday
123407/29/201507/29/201506:0010:00 4 Regular Wednesday
123407/29/201507/29/201511:0013:00 2 Regular Wednesday
123407/30/201507/30/201512:0012:00 8 Persona Thursday
123407/31/201507/31/201507:0012:00 5 Regular Friday
123407/31/201507/31/201513:0016:00 3 Regular Friday
123407/31/201507/31/201516:0018:55 2.92 Day-OT Friday
123408/01/201508/01/201512:0012:00 0 NULL Saturday
1107/27/201507/27/201508:0010:00 2 Hol-Wrkd Monday
1107/27/201507/27/201512:0018:00 8 Hol Monday
1107/28/201507/28/201508:0012:00 4 Regular Tuesday
1107/28/201507/28/201512:0012:00 1 EO Tuesday
1107/28/201507/28/201513:0016:00 3 Regular Tuesday
1107/29/201507/29/201506:0010:00 4 Regular Wednesday
1107/29/201507/29/201511:0013:00 4 Regular Thursday
1107/30/201507/30/201513:0017:00 4 Regular Thursday
1107/31/201507/31/201512:0012:00 0 NULL Friday
1108/01/201508/01/201508:0012:00 4 Regular Saturday
1108/01/201508/01/201513:0014:00 1 Regular Saturday
My desired results with desired headers are as follows - Sorry about the column alignment I tried to fix it to make it look as clean as possible:
PERSONSTARTDATEEND_DATEIN_PUNCHOUT_PUNCH WORK_PAY WORK_HRS OT_PAYOT_HRS ABSNT_PAY ABSNT_HRS DOW
123407/27/201507/27/201512:0012:00 NULL 0 NULL0Hol 8 Monday
123407/28/201507/28/201506:1510:00 Regular 3.75 NULL0Absent 0 Tuesday
123407/29/201507/29/201506:0010:00 Regular 6 NULL0NULL 0 Wednesday
123407/30/201507/30/201512:0012:00 NULL 0 NULL0Personal 8 Thursday
123407/31/201507/31/201507:0012:00 Regular 8 Day-OT2.92NULL 0 Friday
123408/01/201508/01/201512:0012:00 NULL 0 NULL0NULL 0 Saturday
1107/27/201507/27/201508:0010:00 Hol-Wrkd 2 NULL0Hol 8 Monday
1107/28/201507/28/201508:0012:00 Regular 7 NULL0EO 1 Tuesday
1107/29/201507/29/201506:0010:00 Regular 6 NULL0NULL 0 Wednesday
1107/30/201507/30/201508:0012:00 Regular 8 NULL0NULL 0 Thursday
1107/31/201507/31/201512:0012:00 NULL 0 NULL0NULL 0 Friday
1108/01/201508/01/201508:0012:00 Regular 5 NULL0NULL 0 Saturday
July 29, 2015 at 4:38 pm
Hi Wayne,
You are right, there is a discrepancy there for the 07/27/2015 for PERSON it should be 08:00 to 10:00 instead of 12:00 to 12:00.
Secondly, we can get rid of the SHIFT column to make things easy.
Lastly, I don't know what happened but I have replied to this post twice with adjusted Sample Data (without the SHIFT), Adjusted Current Results (without the SHIFT) and adjusted desired results (without the SHIFT) and by fixing the discrepancy but I don't see it posted 🙁
I can re-post the adjusted data if required.
Thank you, thank you.
July 29, 2015 at 4:47 pm
DiabloZA (7/29/2015)
Hi Wayne,You are right, there is a discrepancy there for the 07/27/2015 for PERSON it should be 08:00 to 10:00 instead of 12:00 to 12:00.
Secondly, we can get rid of the SHIFT column to make things easy.
Lastly, I don't know what happened but I have replied to this post twice with adjusted Sample Data (without the SHIFT), Adjusted Current Results (without the SHIFT) and adjusted desired results (without the SHIFT) and by fixing the discrepancy but I don't see it posted 🙁
I can re-post the adjusted data if required.
Thank you, thank you.
Okay, so is the query that I posted earlier then working for you?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 29, 2015 at 11:39 pm
Hi Wayne,
Yes it surely did.
I sincerely appreciate your urgent help on this so thank you again,
God Bless !!
August 3, 2015 at 5:34 pm
Hi Wayne,
So the client came back with more changes to the script 🙁
What they want is that if the WORK_PAY is NULL and the WORK_HRS are 0:00 then they want the IN_PUNCH and OUT_PUNCH to be blank, so basically grab the earliest IN_PUNCH and OUT_PUNCH only when the WORK_PAY is not NULL and WORK_HRS are greater than 0 else the IN_PUNCH and OUT_PUNCH stays blank.
Can this be done?
Thank you very much,
DZA
August 4, 2015 at 11:49 pm
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
August 5, 2015 at 8:25 am
DiabloZA (8/4/2015)
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
Does this work?
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,
-- 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.STARTDATE = t2.STARTDATE) 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, STARTDATE, IN_PUNCH;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 8, 2015 at 11:14 pm
Hi Wayne,
Yes, exactly what the desired results were required..
Awesome work 🙂
Thank you, thank you..
God bless,
DZA
August 15, 2015 at 3:15 am
Hi Wayne,
It's me again 🙂
As last time the customer changed some minor requirements again, now they want the IN_PUNCH to be the first in punch of the day and the OUT_PUNCH to be the last out punch of the day as it is showing the desired results table for 07/29.
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..
I hope I won't have to bother you on this one any longer, just an FYI that I'm trying to learn all this on my own using the following 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,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,
-- 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.STARTDATE = t2.STARTDATE) 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, 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/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
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 13:00 Regular 6.00 NULL0.00NULL 0.00 Batam 2ndWednesday
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply