• 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