Hi Jeff,
The output requirements are still the same, which is weekly output, I think for daily output you are referring to is what I posted for Sean when I was trying to breakdown how hours were being allocated between REG, DAILY_OT and WEEKLY_OT.
I think Sean's query is working great with the exception that it doesn't count Saturday's (04/12/2014) hours towards Weekly_OT instead it is sending the 2 hours worked on Saturday (04/12/2014) towards REG bucket.
I hope this makes sense and thank you very much for your help.
I'm posting the sample data with Sean's query.
with SampleData (PERSON, [DATE], [HOURS], [DOW]) as
(
SELECT 1234,'03/31/2014','8.00','Monday'
UNION ALL SELECT 1234,'04/01/2014','8.00','Tuesday'
UNION ALL SELECT 1234,'04/02/2014','8.00','Wednesday'
UNION ALL SELECT 1234,'04/03/2014','8.00','Thursday'
UNION ALL SELECT 1234,'04/04/2014','12.00','Friday'
UNION ALL SELECT 1234,'04/07/2014','9.00','Monday'
UNION ALL SELECT 1234,'04/08/2014','8.00','Tuesday'
UNION ALL SELECT 1234,'04/09/2014','8.00','Wednesday'
UNION ALL SELECT 1234,'04/10/2014','8.00','Thursday'
UNION ALL SELECT 1234,'04/11/2014','8.00','Friday'
UNION ALL SELECT 1234,'04/12/2014','2.00','Saturday'
UNION ALL SELECT 1234,'04/14/2014','9.00','Monday'
UNION ALL SELECT 1234,'04/15/2014','9.00','Tuesday'
UNION ALL SELECT 1234,'04/16/2014','9.00','Wednesday'
UNION ALL SELECT 1234,'04/17/2014','8.00','Thursday'
UNION ALL SELECT 1234,'04/18/2014','6.00','Friday'
)
, OrderedData as
(
select
row_number() over (partition by person order by person desc) rn
,PERSON
, convert(datetime,[DATE]) as MyDate, convert(decimal(8,2),[HOURS]) as MyHours, [DOW]
FROM SampleData
)
select
Person
,min(MyDate) as BeginningOfWeek
,sum(reg_hours)[reg_hours]
,sum(daily_ot)[daily_ot]
,sum(weekly_ot)[weekly_ot]
from
(
select
a.person
,a.MyDate
,a.MyHours
,sum(b.MyHours) [RunTotal]
,case when a.MyHours > 8 then 8 else a.MyHours end [Reg_Hours]
,case when a.MyHours > 8 and sum(b.MyHours) <= 40 then a.MyHours - 8 else 0 end [Daily_OT]
,case when a.MyHours > 8 and sum(b.MyHours) > 40 then a.MyHours - 8 else 0 end [Weekly_OT]
from OrderedData a
join OrderedData b on a.rn >= b.rn and a.person = b.person and DATEPART(week, a.MyDate) = DATEPART(week, b.MyDate)
group by a.person, a.MyDate , a.MyHours
) a
group by a.person
, DATEPART(week, MyDate)
order by a.PERSON, DATEPART(week, MyDate)
The results from the query
PERSON DATE REG_HOURS DAILY_OT WEEKLY_OT
1234 03/31/2014 40.00 0.00 4.00
1234 04/07/2014 42.00 1.00 0.00
1234 04/14/2014 38.00 3.00 0.00
Expected results
PERSON DATE REG_HOURS DAILY_OT WEEKLY_OT
1234 03/31/2014 40.00 0.00 4.00
1234 04/07/2014 40.00 1.00 2.00
1234 04/14/2014 38.00 3.00 0.00