set datefirst 7
;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
--modified [reg_hours]
,case when sum(reg_hours) > 40 then 40 else sum(reg_hours) end[reg_hours]
,sum(daily_ot)[daily_ot]
--modified [weekly_ot]
,case when sum(reg_hours) > 40 then sum(reg_hours) - 40 + sum(weekly_ot) else sum(weekly_ot) end [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)
Two things. First, we need to set datefirst to 7. And, we need to test the [reg_hours] and the [weekly_ot] hours in the final result set to make sure we don't go over 40 reg_hours and if we do them move those hours to weekly_ot. I've only tested with the given data so before you pay somebody based on this please make sure you test with a wider range of data.