DiabloZA (4/18/2014)
Hi Sean,I know it's confusing, I will attempt to explain this as detailed as possible, so on daily basis up to 8 hours are going into REG hours bucket, any hours over 8 goes into Daily OT bucket, when 40 hours are deposited in REG bucket then the Daily OT bucket is ignored and all hours after that goes into Weekly OT bucket, so on 04/11/2014 even though this employee worked 9 hours but because the first 8 hours on that day filled the REG bucket all the way to 40 hours the 9th hour or anything beyond that need to go into Weekly OT bucket.
The hours are broken down as follows, I added running totals columns just to explain the progress, I hope this makes sense.
Thank you,
PERSON DATE ACTUAL_HRS REG_HRS_RT DLY_OT WKLY_OT DLY_OT_RT WKLY_OT_RT
-- First Week --
123403/31/2014 8.00 8.00 0.00 0.00 0.00 0.00
123404/01/2014 9.00 16.001.00 0.00 1.00 0.00
123404/02/2014 8.00 24.000.00 0.00 1.00 0.00
123404/03/2014 8.00 32.000.00 0.00 1.00 0.00
123404/04/2014 9.00 40.000.00 1.00 1.00 1.00
-- Second Week --
123404/07/2014 10.00 8.00 2.00 0.00 2.00 0.00
123404/08/2014 8.00 16.000.00 0.00 2.00 0.00
123404/09/2014 8.00 24.000.00 0.00 2.00 0.00
123404/10/2014 9.00 32.001.00 0.00 3.00 0.00
123404/11/2014 9.00 40.000.00 1.00 3.00 1.00
OK now I get it. You accumulate anything over 8 hours per day in a Daily bucket until the total hours worked exceeds 40. At that point we no longer accumulate the Daily bucket but anything over 8 goes into the weekly bucket. This is a running total problem. Check out this article from Jeff Moden. It explains one way to tackle the running total problem. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
Make sure you read and UNDERSTAND that article. There are a number of extremely important things to make sure are in place for this to work correctly. Give it a shot and see how far you can get. Feel free to post back if you get stuck.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/