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','9.00','Wednesday'
UNION ALL SELECT 1234,'04/03/2014','8.00','Thursday'
UNION ALL SELECT 1234,'04/04/2014','9.00','Friday'
--UNION ALL SELECT 1234,'04/07/2014','10.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','9.00','Thursday'
--UNION ALL SELECT 1234,'04/11/2014','9.00','Friday'
)
select
row_number() over (partition by person order by person desc) rn
,PERSON
, convert(datetime,[DATE]) [Date], convert(decimal(8,2),[HOURS]) [Hours], [DOW]
into #t
FROM SampleData
select
person
,min(date)
,sum(reg_hours)[reg_hours]
,sum(daily_ot)[daily_ot]
,sum(weekly_ot)[weekly_ot]
from
(
select
a.person
,a.date
,a.hours
,sum(b.hours) [RunTotal]
,case when a.hours > 8 then 8 else a.hours end [Reg_Hours]
,case when a.hours > 8 and sum(b.hours) <= 40 then a.hours - 8 else 0 end [Daily_OT]
,case when a.hours > 8 and sum(b.hours) > 40 then a.hours - 8 else 0 end [Weekly_OT]
from #t a
join #t b on a.rn >= b.rn and a.person = b.person
group by a.person, a.date , a.hours
) a
group by a.person
This may not be the most elegant solution but it works. Especially if you are in a time crunch. Hope it helps. I'll let you figure out the each week issue.