SQL build timesheet time ranges from a lookup start and Hrs

Background: A table contains timesheet records with a Start and Finish time – like the first record below. Unfortunately, many records don’t record the times, only the amounts of hours (hrbk is Hours Booked – eg. One and a half hrs in the 3rd record). Task: Write SQL2008 Stored Proc to complete the Start and Finish times for those records with nulls. Each Worker is assigned an Attendance record to declare what they should be doing that day. Use that to determine the first Start time of the day, then build up the Finish time from the number of hours. Eg. The second timesheet shown (unique ROWSTAMP 435068320) needs to have Start and Finish times assigned. Their earliest Attendance record for that day shows a 7:30 Start, so that can be assigned together with the datediffed 1 hour (hrbk) which would make an 8:30 Finish time. The next record for this worker is on the same day so must continue – so ROWSTAMP 435069339 will Start at 8:30 and Finish at 10:00 (1.5 hrs). Don’t worry about lunchtimes for now – all times can be continuous with no breaks. It’s unlikely that the times will go past midnight – leave them null if so. Note, you will likely need to use some kind of temporary table – use a Table Variable rather than a #table, as they don’t mess up in memory so easily. The timesheet table has a new default 0 field at the end called UpdatedNullTimes = use this to flag 1 that it is to be processed and 2 that it has been processed. 3 can be used to show any problem with it. Write the SQL as a Stored Procedure that can be called to process any records (UpdatedNullTimes = 1) not already done (UpdatedNullTimes = 2). This script: select TSM.Pay_Ref, [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL], A.Att_Pattern_ID, A.st_date from tbl_Timesheet_Maximo TSM inner join tbl_attend A on A.Pay_Ref = TSM.Pay_Ref and [login to view URL] = [login to view URL] --where [login to view URL] is null or [login to view URL] is null order by TSM.Pay_Ref, A.st_date, A.Att_Pattern_ID … should give you the following (should be line by line if you paste into Word, email etc but have put into .xls attched as will probably be messed up on publishing on site): Pay_Ref ROWSTAMP JDate Sttime Fntime hrbk Att_Pattern_ID st_date ------- ----------- ----------------------- ----------------------- ----------------------- --------------------------------------- -------------- ----------------------- J01464 430675274 2009-01-05 00:00:00.000 2009-01-05 07:30:00.000 2009-01-05 11:30:00.000 4.00 3 2009-01-05 07:30:00.000 J01464 435068320 2009-01-07 00:00:00.000 NULL NULL 1.00 3 2009-01-07 07:30:00.000 J01464 435069339 2009-01-07 00:00:00.000 NULL NULL 1.50 3 2009-01-07 07:30:00.000 J01464 435069990 2009-01-07 00:00:00.000 NULL NULL 1.00 3 2009-01-07 07:30:00.000 J01464 435112099 2009-01-07 00:00:00.000 NULL NULL 1.50 3 2009-01-07 07:30:00.000 J01464 435112242 2009-01-07 00:00:00.000 NULL NULL 1.00 3 2009-01-07 07:30:00.000 J01464 435112496 2009-01-07 00:00:00.000 NULL NULL 1.50 3 2009-01-07 07:30:00.000 J01464 435112842 2009-01-07 00:00:00.000 NULL NULL 1.50 3 2009-01-07 07:30:00.000 J01464 449318687 2009-01-07 00:00:00.000 NULL NULL 2.00 3 2009-01-07 07:30:00.000 J01464 435114670 2009-01-09 00:00:00.000 NULL NULL 2.00 3 2009-01-09 07:30:00.000 J01464 435114738 2009-01-09 00:00:00.000 NULL NULL 5.00 3 2009-01-09 07:30:00.000 J01464 433252608 2009-01-09 00:00:00.000 NULL NULL 2.00 3 2009-01-09 07:30:00.000 J01464 435264519 2009-01-12 00:00:00.000 2009-01-12 07:30:00.000 2009-01-12 16:30:00.000 9.00 3 2009-01-12 07:30:00.000 J01464 435741715 2009-01-13 00:00:00.000 NULL NULL 4.50 3 2009-01-13 07:30:00.000 J01464 435746205 2009-01-13 00:00:00.000 NULL NULL 4.50 3 2009-01-13 07:30:00.000 Notes: Will give copy of DB .bak to successful bidder. you should have SQL2008 DEVELOPER/STANDARD edition, not Express, but let me know if latter as may be able to transfer into an Express Instance. This should take around 4 hours to understand, write and test if you have experience of temp table iterative updates. It would take less than 1 hour if it was straight updates, but you need to build each set of records per worker's day as above, that's why it's more tricky (but not rocket science). The code should be fast and as easy to debug as possible (ie can adjust code in future). Comment it reasonably too (just enough to add value, no need to state the obvious).