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