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