SQL Help requested to Get Daily and Weekly OT
-
WITH SampleData (PERSON, [DT], [HRS], [DOW]) AS
(
SELECT 1234,CAST('03/31/2014' AS DATETIME),CAST('8.00' AS NUMERIC(5,2)),'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'
), OThours AS (
SELECT s.person,s.dt,DATEPART(week, s.dt) AS YWeek,
CASE WHEN s.hrs>8 THEN 8 ELSE s.hrs END AS Reghours,
CASE WHEN s.hrs>8 THEN s.hrs-8 ELSE 0 END AS Othours
FROM SampleData s
)
SELECT o.person, MIN(o.dt) AS [Date],
CASE WHEN SUM(o.reghours) >40 THEN 40 ELSE SUM(o.reghours) END AS Reg_hours,
SUM(o.Othours)Daily_ot,
CASE WHEN SUM(o.reghours) >40 THEN SUM(o.reghours) -40 ELSE 0 END AS Weekly_OT
FROM OThours o
GROUP BY o.Yweek, o.person
the result would be some what difference for the first week
on the first week friday 12 hours it means 8 hours reg hour and 4 hours in Daily OT isn't it? then my query will be right
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] -
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/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)
Two things. First, we need to set datefirst to 7. And, we need to test the [reg_hours] and the [weekly_ot] hours in the final result set to make sure we don't go over 40 reg_hours and if we do them move those hours to weekly_ot. I've only tested with the given data so before you pay somebody based on this please make sure you test with a wider range of data.
-
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
-
Ok then. Set datefirst 1 should be all you need.
-
Can you explain why is this not correct?
SET DATEFIRST 1;
WITH SampleData (PERSON, [DATE], [HOURS], [DOW]) AS
(
SELECT 1234,'03/31/2014',8,'Monday'
UNION ALL SELECT 1234,'04/01/2014',8,'Tuesday'
UNION ALL SELECT 1234,'04/02/2014',8,'Wednesday'
UNION ALL SELECT 1234,'04/03/2014',8,'Thursday'
UNION ALL SELECT 1234,'04/04/2014',12,'Friday'
UNION ALL SELECT 1234,'04/06/2014',1,'Sunday'
UNION ALL SELECT 1234,'04/07/2014',9,'Monday'
UNION ALL SELECT 1234,'04/08/2014',8,'Tuesday'
UNION ALL SELECT 1234,'04/09/2014',8,'Wednesday'
UNION ALL SELECT 1234,'04/10/2014',8,'Thursday'
UNION ALL SELECT 1234,'04/11/2014',8,'Friday'
UNION ALL SELECT 1234,'04/12/2014',2,'Saturday'
UNION ALL SELECT 1234,'04/14/2014',9,'Monday'
UNION ALL SELECT 1234,'04/15/2014',9,'Tuesday'
UNION ALL SELECT 1234,'04/16/2014',9,'Wednesday'
UNION ALL SELECT 1234,'04/17/2014',8,'Thursday'
UNION ALL SELECT 1234,'04/18/2014',6,'Friday'
)
SELECT PERSON
,MIN(DATE) DATE
,SUM(CASE WHEN DATEPART(dw, DATE) <= 5
THEN CASE WHEN HOURS > 8
THEN 8
ELSE HOURS
END
END) AS REG_HOURS
,SUM(CASE WHEN DATEPART(dw, DATE) <= 5
THEN CASE WHEN HOURS > 8
THEN HOURS - 8
ELSE 0
END
END) AS DAILY_OT
,CASE WHEN SUM(HOURS) > 40
THEN SUM(HOURS) - 40
ELSE 0 END AS WEEKLY_OT
,SUM(HOURS) Real_Hours
FROM SampleData
GROUP BY PERSON
,DATEPART(WK, DATE);
-
Louis,
The OP's requirements are a little complicated. He most likely wants to pay workers at three different rates: Regular, Daily OT, and Weekly OT. For instance $10, $15, and $20 per hour.
He needs this as his output:
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
Rather than:
PERSONDATE REG_HOURSDAILY_OT WEEKLY_OTReal_Hours
123403/31/201440 4 5 45
123404/07/201440 1 3 43
123404/14/201438 3 1 41
He needs some form of a running total because once the worker hits 40 hours in a given week the hours for each day in excess of 8 are no longer Daily OT but rather Weekly OT.
-
Yep, that did it 🙂
You guys ROCK !!
Thank you Sir..
-
Hi mcx5000,
I'm sorry to bug again, I'm in a bind again, can you kindly help some more please?
My client finally had the chance to test this further on their actual environment, the following data's result has a little more Weekly OT than it should.
I've been playing with the 'datefirst' settings and the numbers within nested query to no avail.
set datefirst 1
;with SampleData (PERSON, [DATE], [HOURS], [DOW]) as
(
SELECT 1234,'01/06/2014','14.25','Monday' -- REG = 8.00 , DAILY_OT = 6.25, WEEKLY_OT = 0.00
UNION ALL SELECT 1234,'01/07/2014','12.25','Tuesday' -- REG = 8.00 , DAILY_OT = 4.25, WEEKLY_OT = 0.00
UNION ALL SELECT 1234,'01/08/2014','13.25','Wednesday' -- REG = 8.00 , DAILY_OT = 5.25, WEEKLY_OT = 0.00
UNION ALL SELECT 1234,'01/09/2014','10.50','Thursday' -- REG = 8.00 , DAILY_OT = 2.50, WEEKLY_OT = 0.00
UNION ALL SELECT 1234,'01/10/2014','12.25','Friday' -- REG = 8.00 , DAILY_OT = 0.00, WEEKLY_OT = 4.25 (40 hours REG reached so all hours goes into WEEKLY_OT)
)
, 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 01/06/2014 40.0015.75 6.50
Expected Results
PERSON DATE REG_HOURS DAILY_OT WEEKLY_OT
1234 01/06/2014 40.0018.25 4.25
-
At this point we are beyond help with a tough sql problem. You are trying to solve a payroll problem. When the hours are going over 40 your client wants part of the hours allocated to a specific bucket and the remainder to another bucket. Does your client have specific requirements or are they iterating through the results you are providing and trying to figure out how they want to pay their workers? Either is perfectly valid but I don't want to spend time on the iterative process helping them determine their requirements. If you can provide the specific requirements I will help you with the sql.
-
Hi mcx5000,
I totally agree with you, and I'm frustrated as well, the requirements are still the same, meaning up to 8 hours daily hours goes into REG bucket, over 8 goes into DailyOT, as soon as 40 hours are into REG then all hours goes into WeeklyOT.
So what's puzzling me that the query works perfectly with one example (the one that you helped me with), but in this recent example Thursday's hours over 8 went into Weekly instead of Daily, if we can adjust the query to match the desired results then I will let them know that this is as good as I can get the report to work.
Is that something doable?
Thank you.
-
I totally agree with you, and I'm frustrated as well, the requirements are still the same, meaning up to 8 hours daily hours goes into REG bucket, over 8 goes into DailyOT, as soon as 40 hours are into REG then all hours goes into WeeklyOT.
I'm not frustrated at all. I just don't want to shoot at a moving target. If what you say above is true then wouldn't the Daily OT for Jan 9 be .25 which would give him 40 for the week so the remainder of 2.25 would go to Weekly OT? There is a conflict between what you are saying the desired output from your last post.
So what's puzzling me that the query works perfectly with one example (the one that you helped me with), but in this recent example Thursday's hours over 8 went into Weekly instead of Daily, if we can adjust the query to match the desired results then I will let them know that this is as good as I can get the report to work.
I can adjust the query to match this one desired result but we will be playing whack-a-mole. Another requirement will pop up. Before we do anything else let's spell out the requirements.
-
Hi mcx5000,
Here is the breakdown and allocation of the hours for REG, DAILY_OT and WEEKLY_OT.
**The DAILY_OT hours do not count towards the REG 40 hours in the week goal**
1. 01/06/2014 Total Hours worked = 14.25 - REG = 8.00 , DAILY_OT = 6.25, WEEKLY_OT = 0.00 - Total REG = 8, Total DAILY_OT = 6.25, Total WEEKLY_OT = 0.00
2. 01/07/2014 Total Hours worked = 12.25 - REG = 8.00 , DAILY_OT = 4.25, WEEKLY_OT = 0.00 - Total REG = 16, Total DAILY_OT = 10.50, Total WEEKLY_OT = 0.00
3. 01/08/2014 Total Hours worked = 13.25 - REG = 8.00 , DAILY_OT = 5.25, WEEKLY_OT = 0.00 - Total REG = 24, Total DAILY_OT = 15.75, Total WEEKLY_OT = 0.00
4. 01/09/2014 Total Hours worked = 10.50 - REG = 8.00 , DAILY_OT = 2.50, WEEKLY_OT = 0.00 - Total REG = 32, Total DAILY_OT = 18.25, Total WEEKLY_OT = 0.00
5. 01/10/2014 Total Hours worked = 12.25 - REG = 8.00 , DAILY_OT = 0.00, WEEKLY_OT = 4.25 - Total REG = 40 (On 1/10/2014 the REG 40 hours in a week goal reached, so now all hours will go in to WEEKL_OT) - Total DAILY_OT = 18.25, Total WEEKLY_OT = 4.25
Hope this makes sense and answers your question.
-
might not be the elegant one but it is a solution
WITH SampleData(PERSON, [DT], [HRS], [DOW]) AS
(
SELECT 1234, CAST('03/31/2014' AS DATETIME), CAST('8.00' AS NUMERIC(5, 2)),
'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'
UNION ALL SELECT 1234, '04/19/2014', '12.00', 'Saturday'
UNION ALL SELECT 1234, '01/06/2014', '14.25', 'Monday' -- REG = 8.00 , DAILY_OT = 6.25, WEEKLY_OT = 0.00
UNION ALL SELECT 1234, '01/07/2014', '12.25', 'Tuesday' -- REG = 8.00 , DAILY_OT = 4.25, WEEKLY_OT = 0.00
UNION ALL SELECT 1234, '01/08/2014', '13.25', 'Wednesday' -- REG = 8.00 , DAILY_OT = 5.25, WEEKLY_OT = 0.00
UNION ALL SELECT 1234, '01/09/2014', '10.50', 'Thursday' -- REG = 8.00 , DAILY_OT = 2.50, WEEKLY_OT = 0.00
UNION ALL SELECT 1234, '01/10/2014', '12.25', 'Friday' -- REG = 8.00 , DAILY_OT = 0.00, WEEKLY_OT = 4.25
), OThours AS (
SELECT s.person, s.dt, DATEPART(week, s.dt) AS YWeek,
CASE
WHEN (x.runhours+s.hrs)<40 THEN CASE
WHEN s.hrs>8 THEN 8
ELSE s.hrs
END
ELSE ABS(x.runhours-40)
END AS Reghours,
CASE
WHEN s.hrs>8 AND (x.runhours+s.hrs)<40 THEN s.hrs- 8
ELSE 0
END AS DOthours,
CASE
WHEN (x.runhours+s.hrs)>=40 THEN (x.runhours+s.hrs)-40
ELSE 0
END AS WOthours, ISNULL(x.runhours, 0)+s.hrs AS Rhours, s.hrs
FROM SampleData s
CROSS APPLY
(
SELECT ISNULL(SUM(CASE WHEN s1.hrs>8 THEN 8 ELSE s1.hrs END), 0) AS
Runhours
FROM sampledata s1
WHERE s1.person = s.person AND
DATEPART(week, s1.dt) = DATEPART(week, s.dt) AND
s1.dt<s.dt
)x
)
SELECT person, MIN(o.dt) AS Date, SUM(REghours) AS Reg_hours, SUM(o.Dothours) AS
Daily_ot, SUM(o.Wothours) AS Weekly_ot, SUM(o.hrs) TotlaWorkinghours
FROM OThours o
GROUP BY
person, yweek
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] -
'01/06/2014', '14.25', 'Monday'-- REG = 8.00, DAILY_OT = 6.25, WEEKLY_OT = 0.00, TOTAL HOURS = 14.25'01/07/2014', '12.25', 'Tuesday'-- REG = 8.00, DAILY_OT = 4.25, WEEKLY_OT = 0.00, TOTAL HOURS = 26.5
'01/08/2014', '13.25', 'Wednesday'-- REG = 8.00, DAILY_OT = 5.25, WEEKLY_OT = 0.00, TOTAL HOURS = 39.75
-- I think this is incorrect because 40 hours are exceeded before daily O/T comes into effect
'01/09/2014', '10.50', 'Thursday'-- REG = 8.00, DAILY_OT = 2.50, WEEKLY_OT = 0.00
-- I think this is correct
'01/09/2014', '10.50', 'Thursday'-- REG = 8.00, DAILY_OT = 0.00, WEEKLY_OT = 2.50,
'01/10/2014', '12.25', 'Friday'-- REG = 8.00, DAILY_OT = 0.00, WEEKLY_OT = 4.25
-- OThours stops on the row before total hours exceeds 40-- i.e. if total hours exceeds 40, extra hours become weekly not daily
;WITH RiggedData AS (
SELECT person, dt, dow, hrs,
[OThours] = CASE WHEN hrs > 8 THEN hrs - 8 ELSE 0 END,
[weekno] = DATEDIFF(day,0,dt)/7
FROM SampleData
)
SELECT
ro.person,
[date] = MIN(ro.dt),
REG_hours = CASE WHEN SUM(ro.hrs) >= 40 THEN 40 ELSE SUM(ro.hrs) END,
Daily_OT = MAX(CASE WHEN x.hrs <= 40 THEN x.OThours ELSE 0 END),
Weekly_OT = SUM(ro.hrs) - MAX(CASE WHEN x.hrs <= 40 THEN x.OThours ELSE 0 END) - 40,
TotalWorkingHours = SUM(ro.hrs)
FROM RiggedData ro
CROSS APPLY ( -- 'triangular join' over max 7 rows may be acceptable
SELECT [hrs] = SUM(hrs), [OThours] = SUM(OThours)
FROM RiggedData ri
WHERE ri.person = ro.person AND ri.weekno = ro.weekno AND ri.dt <= ro.dt
) x
GROUP BY person, weekno
ORDER BY person, weekno
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
On second thoughts, Thava's query appears to do the trick - the boundary between daily OT and weekly OT isn't total hours, it's regular hours. My query becomes this:
-- Daily_OT stops on the row before total regular hours exceeds 40-- i.e. if total regular hours exceeds 40, extra hours become weekly not daily
;WITH RiggedData AS (
SELECT person, dt, dow, hrs,
[weekno] = DATEDIFF(day,0,dt)/7,
[OThours] = CASE WHEN hrs > 8 THEN hrs - 8 ELSE 0 END,
[RegHours] = CASE WHEN hrs < 8 then hrs ELSE 8 END
FROM #SampleData
)
SELECT
ro.person,
[date] = MIN(ro.dt),
[REG_hours] = CASE WHEN SUM(ro.hrs) >= 40 THEN 40 ELSE SUM(ro.hrs) END,
[Daily_OT] = MAX(CASE WHEN x.RegHours < 40 THEN x.OThours ELSE 0 END),
[Weekly_OT] = SUM(ro.hrs) - MAX(CASE WHEN x.RegHours < 40 THEN x.OThours ELSE 0 END) - 40,
[TotalWorkingHours] = SUM(ro.hrs)
FROM RiggedData ro
CROSS APPLY ( -- 'triangular join' over max 7 rows may be acceptable
SELECT
[hrs] = SUM(hrs), [OThours] = SUM(OThours), [REGHours] = SUM(RegHours)
FROM RiggedData ri
WHERE ri.person = ro.person AND ri.weekno = ro.weekno AND ri.dt <= ro.dt
) x
GROUP BY person, weekno
ORDER BY person, weekno
which is essentially the same as Thava's.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply