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