I have a situation that I need to calculate peoples working hours, the HR cannot share the data. We use workers working records time data - Resolved_Time field to get end(MAX)/start(MIN) time, then use MAX-MIN to get roughly working time. For people do not work overnight, it is fine. But some workers will work across midnight like 01/01/2015 15:00, ... 01/01/2015 23:00... 01/02/2015 1:00, 01/02/2015 2:00 etc. Most case worker will work 7-9 hours and do not continuously work 13 hours. Worker will start next work day on 01/02/2015 16:00 maybe. So I can't easy find the MAX/MIN time for each work days. Here are example data, I want to get work hours as results. There are historical data all store in Resolved_Time field, it is not easy to separate the Start_time, End_Time. Between start time and end time, there are hundreds time records. But from 16:00PM and 0:30AM there was a large gap, we can think the worker back home for rest, so 16:00PM will be new start time for day 4/6 and so on. resolved_time -- work hours 4/5/2015 15:30 4/5/2015 23:15 4/6/2015 0:30 -- 9= 4/6/2015 0:30-4/5/2015 15:30 4/6/2015 16:00 4/6/2015 23:59 4/7/2015 1:00 -- 9 4/7/2015 16:00 4/7/2015 21:28 4/7/2015 23:09 4/8/2015 3:00 -- 11 Could someone with the experiences or professional on SQL code help to solved this situation?
Because you have 24-hour based working time, using MAX/MIN time is not good idea (you explain that in your question). It is possible but it's complicated. First, you need to separate your Resolved_Time field for every worker in two columns - StarTime and StopTime if possible (do you have any flag on each record for start time and for end time maybe?). I had similar problem inside MS Lync server with user statuses (active, offline, in a call etc...). All statuses I insert into one table with start time and end time. So you can easily calculate time for each status (or working time in your case) with DATEDIFF function. OK, if I figured it out, from your data you can get StartTime and EndTime like this: With recursive function (SQL CTE - google it if you don't know what is it and find few examples) calculate time difference between previous time (T1) and next time (T2) for your Resolved_time column. If time difference between two time stamps is bigger than X (10?) hours, you get stop time from previous working day (T1) and start time for next working day (T2). If time difference is smaller then X hours, take next datatime and calculate again. Result of CTE insert into new table where you will have 2 columns for StartTime and EndTime. Your final step is calculating DATEDIFF from this two columns in new table. This is logical algorithm for your problem.