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);