SQL Help requested to Get Daily and Weekly OT
-
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
123403/31/201440.00 1.00 1.00
123404/07/201440.00 3.00 1.00
-
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
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂 -
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/[/url]
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 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/ -
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 --
123403/31/2014 8.00 8.00 0.00 0.00 0.00 0.00
123404/01/2014 9.00 16.001.00 0.00 1.00 0.00
123404/02/2014 8.00 24.000.00 0.00 1.00 0.00
123404/03/2014 8.00 32.000.00 0.00 1.00 0.00
123404/04/2014 9.00 40.000.00 1.00 1.00 1.00
-- Second Week --
123404/07/2014 10.00 8.00 2.00 0.00 2.00 0.00
123404/08/2014 8.00 16.000.00 0.00 2.00 0.00
123404/09/2014 8.00 24.000.00 0.00 2.00 0.00
123404/10/2014 9.00 32.001.00 0.00 3.00 0.00
123404/11/2014 9.00 40.000.00 1.00 3.00 1.00
-
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 --
123403/31/2014 8.00 8.00 0.00 0.00 0.00 0.00
123404/01/2014 9.00 16.001.00 0.00 1.00 0.00
123404/02/2014 8.00 24.000.00 0.00 1.00 0.00
123404/03/2014 8.00 32.000.00 0.00 1.00 0.00
123404/04/2014 9.00 40.000.00 1.00 1.00 1.00
-- Second Week --
123404/07/2014 10.00 8.00 2.00 0.00 2.00 0.00
123404/08/2014 8.00 16.000.00 0.00 2.00 0.00
123404/09/2014 8.00 24.000.00 0.00 2.00 0.00
123404/10/2014 9.00 32.001.00 0.00 3.00 0.00
123404/11/2014 9.00 40.000.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/[/url]
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 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/ -
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,
-
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 [/url] -
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.
-
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/ -
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
-
Sean, Thanks for pointing me to the article on triangular joins http://www.sqlservercentral.com/articles/T-SQL/61539/. I do not encounter the running total problem very often so I guess I've been lucky in the past with the benefit of relatively small data sets. I always enjoy reading your comments.
-
DiabloZA (4/22/2014)
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
So, do you need the daily output that you previously posted or just the weekly output that you posted above?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
i think op is still not clear about what he want,
ok one more guess
Hi OP What is the output of the below data
SELECT * FROM
(
VALUES
(1234,'03/31/2014','7.00','Monday'),
(1234,'04/01/2014','7.00','Tuesday'),
(1234,'04/02/2014','7.00','Wednesday'),
(1234,'04/03/2014','7.00','Thursday'),
(1234,'04/04/2014','7.00','Friday'),
(1234,'04/07/2014','8.00','Monday'),
(1234,'04/09/2014','8.00','Wednesday'),
(1234,'04/10/2014','8.00','Thursday'),
(1234,'04/11/2014','8.00','Friday'),
(1234,'04/12/2014','4.00','Saturday')
) AS vtable ([PERSON],[DATE],[HOURS],[DOW])
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 [/url] -
Hi Jeff,
The output requirements are still the same, which is weekly output, I think for daily output you are referring to is what I posted for Sean when I was trying to breakdown how hours were being allocated between REG, DAILY_OT and WEEKLY_OT.
I think Sean's query is working great with the exception that it doesn't count Saturday's (04/12/2014) hours towards Weekly_OT instead it is sending the 2 hours worked on Saturday (04/12/2014) towards REG bucket.
I hope this makes sense and thank you very much for your help.
I'm posting the sample data with Sean's 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','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/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
,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)
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
-
Hi Thava,
Thank you for your post, I will attempt to be more clear as to what is the required output. This client has a Weekly payroll (Mon - Sun), they want to track weekly REG hours, daily OT hours for the week and weekly OT for the week, hours worked daily up to 8 are counted as REG, hours over 8 in a day are counted as daily OT, once the REG hours in a week reaches 40 then all hours then are counted as weekly OT.
Based on your sample data (below) the results are also shown below.
WITH SampleData (PERSON, [DATE], [HOURS], [DOW]) AS
(
SELECT 1234,'03/31/2014','7.00','Monday'
UNION ALL SELECT 1234,'04/01/2014','7.00','Tuesday'
UNION ALL SELECT 1234,'04/02/2014','7.00','Wednesday'
UNION ALL SELECT 1234,'04/03/2014','7.00','Thursday'
UNION ALL SELECT 1234,'04/04/2014','7.00','Friday'
UNION ALL SELECT 1234,'04/07/2014','8.00','Monday'
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','4.00','Saturday'
)
SELECT
*
FROM SampleData
Results
PERSON DATE REG_HOURS DAILY_OT WEEKLY_OT
1234 03/31/2014 35.00 0.00 0.00
1234 04/07/2014 36.00 0.00 0.00
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply