WITH SampleData (PERSON, [DT], [HRS], [DOW]) AS

(

SELECT 1234,CAST('03/31/2014' AS DATETIME),CAST('8.00' AS NUMERIC(5,2)),'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/16/2014','9.00','Wednesday'

UNION ALL SELECT 1234,'04/17/2014','8.00','Thursday'

UNION ALL SELECT 1234,'04/18/2014','6.00','Friday'

), OThours AS (

SELECT s.person,s.dt,DATEPART(week, s.dt) AS YWeek,

CASE WHEN s.hrs>8 THEN 8 ELSE s.hrs END AS Reghours,

CASE WHEN s.hrs>8 THEN s.hrs-8 ELSE 0 END AS Othours

FROM SampleData s

)

SELECT o.person, MIN(o.dt) AS [Date],

CASE WHEN SUM(o.reghours) >40 THEN 40 ELSE SUM(o.reghours) END AS Reg_hours,

SUM(o.Othours)Daily_ot,

CASE WHEN SUM(o.reghours) >40 THEN SUM(o.reghours) -40 ELSE 0 END AS Weekly_OT

FROM OThours o

GROUP BY o.Yweek, o.person

the result would be some what difference for the first week

on the first week friday 12 hours it means 8 hours reg hour and 4 hours in Daily OT isn't it? then my query will be right

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]