asked
Calculate working hours between two days
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?
I have removed the TSQL tag and rettagged as PostgreSQL - maybe that will help you target the right people. You also might have better luck posting this on a PostgreSQL forum, whilst we do cover other flavours here, it is a predominantly SQL Server forum.
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.
CREATE TABLE TestTable ( [ID] int, [Event] varchar(100), [time] datetime, [Duration] int ) INSERT INTO TestTable SELECT 1,'logon','2012-05-23 04:00:00',NULL UNION SELECT 2,'incomming call','2012-05-23 06:17:00',NULL UNION SELECT 3,'call ended','2012-05-23 10:02:00',NULL UNION SELECT 4,'logoff','2012-05-23 23:04:00',NULL SELECT * FROM TestTable; WITH rows AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY [time]) AS rn FROM TestTable ) SELECT mc.[time] AS StarTime ,mp.[time] AS EndTime , DATEDIFF(minute, mc.[time], mp.[time]) as TimeDiffInMinutes FROM rows mc JOIN rows mp ON mc.rn = mp.rn-1
19 People are following this question.
