You are going to need a calendar table for something like this. It makes the breakdown of weeks and such a LOT easier. You can read about calendar tables here. http://www.sqlservercentral.com/articles/T-SQL/70482/[/url]

What I don't understand is your desired output. You say that anything on a given day over 8 hours is DailyOT. That makes sense. However, you then state that anything over 40 for the week not including daily OT is weekly. How can you have any hours that are not already part of a day???

Let's look at the second week. Monday you have 10 hours. That would be 2 hours dailyOT by your rules. Thursday and Friday both have 9 hours. That would be 1 hour of daily OT for each day. However, in your results you have 3 hours of dailyOT and 1 hour of weekly. Can you explain the business rules for this? It is very difficult to figure out what you are trying to do here.