SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Help requested to Get Daily and Weekly OT


SQL Help requested to Get Daily and Weekly OT

Author
Message
DiabloSlayer
DiabloSlayer
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 434
Hello Everyone,

I need to pull a report from a table which gives me daily totals, I'm asked to get regular hours (up to 8 hours in a day), daily overtime and weekly overtime out of it, so anything in a day over 8 hours is considered a Daily OT and anything over 40 in a week not including the Daily OT is considered a Weekly OT.

The week starts from Monday thru Sunday, the reason I have two records in the result is because I need to know the totals per week.

Thank you for helping with this 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','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
*
FROM SampleData



From the above data I would like to have the following results.


PERSON DATE REG_HOURS DAILY_OT WEEKLY_OT
1234 03/31/2014 40.00 1.00 1.00
1234 04/07/2014 40.00 3.00 1.00


rhythmk
rhythmk
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1644 Visits: 1099
Can you please explain more about your requirement as I could not understand the resultset you want ?
Can you please post your query, you have tried so far. may be that can help us to understand where are you coming from and what exactly you need ?

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
:-)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59722 Visits: 17947
You are going to need a calendar table for something like this. It makes the breakdown of weeks and such a LOT easier. You can read about calendar tables here. http://www.sqlservercentral.com/articles/T-SQL/70482/

What I don't understand is your desired output. You say that anything on a given day over 8 hours is DailyOT. That makes sense. However, you then state that anything over 40 for the week not including daily OT is weekly. How can you have any hours that are not already part of a day???

Let's look at the second week. Monday you have 10 hours. That would be 2 hours dailyOT by your rules. Thursday and Friday both have 9 hours. That would be 1 hour of daily OT for each day. However, in your results you have 3 hours of dailyOT and 1 hour of weekly. Can you explain the business rules for this? It is very difficult to figure out what you are trying to do here.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
DiabloSlayer
DiabloSlayer
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 434
Hi Sean,

I know it's confusing, I will attempt to explain this as detailed as possible, so on daily basis up to 8 hours are going into REG hours bucket, any hours over 8 goes into Daily OT bucket, when 40 hours are deposited in REG bucket then the Daily OT bucket is ignored and all hours after that goes into Weekly OT bucket, so on 04/11/2014 even though this employee worked 9 hours but because the first 8 hours on that day filled the REG bucket all the way to 40 hours the 9th hour or anything beyond that need to go into Weekly OT bucket.

The hours are broken down as follows, I added running totals columns just to explain the progress, I hope this makes sense.

Thank you,


PERSON DATE ACTUAL_HRS REG_HRS_RT DLY_OT WKLY_OT DLY_OT_RT WKLY_OT_RT
-- First Week --
1234 03/31/2014 8.00 8.00 0.00 0.00 0.00 0.00
1234 04/01/2014 9.00 16.00 1.00 0.00 1.00 0.00
1234 04/02/2014 8.00 24.00 0.00 0.00 1.00 0.00
1234 04/03/2014 8.00 32.00 0.00 0.00 1.00 0.00
1234 04/04/2014 9.00 40.00 0.00 1.00 1.00 1.00

-- Second Week --
1234 04/07/2014 10.00 8.00 2.00 0.00 2.00 0.00
1234 04/08/2014 8.00 16.00 0.00 0.00 2.00 0.00
1234 04/09/2014 8.00 24.00 0.00 0.00 2.00 0.00
1234 04/10/2014 9.00 32.00 1.00 0.00 3.00 0.00
1234 04/11/2014 9.00 40.00 0.00 1.00 3.00 1.00



Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59722 Visits: 17947
DiabloZA (4/18/2014)
Hi Sean,

I know it's confusing, I will attempt to explain this as detailed as possible, so on daily basis up to 8 hours are going into REG hours bucket, any hours over 8 goes into Daily OT bucket, when 40 hours are deposited in REG bucket then the Daily OT bucket is ignored and all hours after that goes into Weekly OT bucket, so on 04/11/2014 even though this employee worked 9 hours but because the first 8 hours on that day filled the REG bucket all the way to 40 hours the 9th hour or anything beyond that need to go into Weekly OT bucket.

The hours are broken down as follows, I added running totals columns just to explain the progress, I hope this makes sense.

Thank you,


PERSON DATE ACTUAL_HRS REG_HRS_RT DLY_OT WKLY_OT DLY_OT_RT WKLY_OT_RT
-- First Week --
1234 03/31/2014 8.00 8.00 0.00 0.00 0.00 0.00
1234 04/01/2014 9.00 16.00 1.00 0.00 1.00 0.00
1234 04/02/2014 8.00 24.00 0.00 0.00 1.00 0.00
1234 04/03/2014 8.00 32.00 0.00 0.00 1.00 0.00
1234 04/04/2014 9.00 40.00 0.00 1.00 1.00 1.00

-- Second Week --
1234 04/07/2014 10.00 8.00 2.00 0.00 2.00 0.00
1234 04/08/2014 8.00 16.00 0.00 0.00 2.00 0.00
1234 04/09/2014 8.00 24.00 0.00 0.00 2.00 0.00
1234 04/10/2014 9.00 32.00 1.00 0.00 3.00 0.00
1234 04/11/2014 9.00 40.00 0.00 1.00 3.00 1.00




OK now I get it. You accumulate anything over 8 hours per day in a Daily bucket until the total hours worked exceeds 40. At that point we no longer accumulate the Daily bucket but anything over 8 goes into the weekly bucket. This is a running total problem. Check out this article from Jeff Moden. It explains one way to tackle the running total problem. http://www.sqlservercentral.com/articles/T-SQL/68467/

Make sure you read and UNDERSTAND that article. There are a number of extremely important things to make sure are in place for this to work correctly. Give it a shot and see how far you can get. Feel free to post back if you get stuck.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
DiabloSlayer
DiabloSlayer
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 434
Hi Sean,

Thank you for your reply, I've been out sick for the last few days, I have to deliver the report by Wednesday but sadly I lost 4 days due to the flu, I will start reading up Jeff Moden's article now, hopefully I will something close to what I'm looking for by tomorrow, I will keep you posted.

Thank you again,
thava
thava
SSC-Addicted
SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)

Group: General Forum Members
Points: 473 Visits: 557
seems something logically missing there
calculating the ot on the last day of the week is it right is it intend or is it a mistake



Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
mcx5000
mcx5000
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 295

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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59722 Visits: 17947
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/

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
DiabloSlayer
DiabloSlayer
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 434
Hi Sean and MCX5000,

You guys are awesome, thank you for the queries, this is very helpful and timely especially for the crunch time that I'm in.

I ran the query against an actual employee's data and the results were somewhat different as expected, I think it's the Saturday in the second week that is throwing off the hours for that week, the week starts from Monday thru Sunday so Saturday's hours in the second are going in to Weekly_OT bucket because 40 hours in that week were already deposited into REG bucket .

Here is a new sample data based on an actual employee's data



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/15/2014','9.00','Wednesday'
UNION ALL SELECT 1234,'04/17/2014','8.00','Thursday'
UNION ALL SELECT 1234,'04/18/2014','6.00','Friday'
)
SELECT
*
FROM SampleData



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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search