Hi mcx5000,
This is great, the results are looking great, thank you very much everyone 🙂
One thing I noticed was that if I add hours into Sunday that starts a new week in the results whereas it should not because as I stated in the beginning that the week starts from Monday to Sunday instead of Sunday to Saturday.
I added 1.00 hours on Sunday '04/06/2014', it appended those to the next week's tally and started the next week as of '04/06/2014'.
Thank you again for the help everyone !!
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/06/2014','1.00','Sunday'
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)
Results
PERSON DATE REG_HOURS DAILY_OT WEEKLY_OT
1234 03/31/2014 40.00 0.00 4.00 -- WEEKLY_OT 4 hours should have became 5 because of 1 hour from Sunday
1234 04/06/2014 40.00 1.00 3.00 -- The DATE should have stayed as '04/07/2014'
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 5.00
1234 04/07/2014 40.00 1.00 2.00
1234 04/14/2014 38.00 3.00 0.00