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 Tuesday, April 22, 2014 2:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:05 PM
Points: 39, Visits: 242
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.

Post #1564035
Posted Tuesday, April 22, 2014 5:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1564063
Posted Tuesday, April 22, 2014 10:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:25 AM
Points: 249, Visits: 529
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
Post #1564085
Posted Tuesday, April 22, 2014 11:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 63, Visits: 245
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

Post #1564100
Posted Wednesday, April 23, 2014 12:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 63, Visits: 245
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

Post #1564106
Posted Wednesday, April 23, 2014 4:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:25 AM
Points: 249, Visits: 529
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
Post #1564181
Posted Wednesday, April 23, 2014 8:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:05 PM
Points: 39, Visits: 242
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.
Post #1564285
Posted Wednesday, April 23, 2014 11:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 63, Visits: 245
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

Post #1564403
Posted Wednesday, April 23, 2014 12:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:05 PM
Points: 39, Visits: 242
Ok then. Set datefirst 1 should be all you need.
Post #1564411
Posted Wednesday, April 23, 2014 12:59 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:22 PM
Points: 3,325, Visits: 7,174
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);




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1564433
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse