Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

SQL Help requested to Get Daily and Weekly OT Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 12:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
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

Post #1562923
Posted Friday, April 18, 2014 1:27 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:46 PM
Points: 569, Visits: 903
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/
Post #1562929
Posted Friday, April 18, 2014 7:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1563013
Posted Friday, April 18, 2014 1:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
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


Post #1563166
Posted Friday, April 18, 2014 2:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1563167
Posted Tuesday, April 22, 2014 1:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
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,
Post #1563700
Posted Tuesday, April 22, 2014 6:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
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
Post #1563812
Posted Tuesday, April 22, 2014 7:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:02 AM
Points: 39, Visits: 258
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.
Post #1563851
Posted Tuesday, April 22, 2014 9:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1563903
Posted Tuesday, April 22, 2014 11:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
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

Post #1563980
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse