mcx5000 (4/22/2014)
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.
Adding the week calculation is trivial. Just need to include the weeknumber. I got rid of the temp table as it just causes extra work to drop. 😉
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'
)
, 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)
I am not thrilled with the triangular join going on here. http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]
If I have some time later today I will hop back here and create a more solution that will perform better than the triangular join.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/