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