• 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.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2