## How do I calculate working time between two datetime values ?

 Author Message goran.p Forum Newbie Group: General Forum Members Points: 5 Visits: 156 HiI'm not very experienced with SQL, of this reason I put my question here.Before I posted this topic I have tried to find a solution or tip on the net, but has not found the correct thing.I would like to create a query so I could calculate how long it has been working time between dtStart and dtStop.As an example I would like to use the following values : dtStart : '2013-06-11 12:40:21' dtStop : '2013-06-12 08:55:16'I have a table, tblSchema, with columns like ID, Shift, Code, dtBegin, dtEnd.Code means as follows: 1 - Start and stop worktime for the shift, 11 - (First) pausetime of this shift, 12 - (Second) pausetime of this shift, 13 - (Third) pausetime of this shift, etc.Example data below from the tblSchemaShift;Code;dtBegin;dtEnd1; 1; 2013-06-11 06:00:00; 2013-06-11 14:21:00;1; 11; 2013-06-11 08:00:00; 2013-06-11 08:15:00;1; 12; 2013-06-11 10:15:00; 2013-06-11 10:30:00;1; 13; 2013-06-11 12:30:00; 2013-06-11 12:45:00;2; 1; 2013-06-11 14:21:00; 2013-06-11 23:31:00;2; 11; 2013-06-11 16:45:00; 2013-06-11 17:00:00;2; 12; 2013-06-11 19:00:00; 2013-06-11 19:15:00;2; 13; 2013-06-11 21:15:00; 2013-06-11 21:30:00;1; 1; 2013-06-12 06:00:00; 2013-06-12 14:21:00;1; 11; 2013-06-12 08:00:00; 2013-06-12 08:15:00;1; 12; 2013-06-12 10:15:00; 2013-06-12 10:30:00;1; 13; 2013-06-12 12:30:00; 2013-06-12 12:45:00;2; 1; 2013-06-12 14:21:00; 2013-06-12 23:31:00;2; 11; 2013-06-12 16:45:00; 2013-06-12 17:00:00;2; 12; 2013-06-12 19:00:00; 2013-06-12 19:15:00;2; 13; 2013-06-12 21:15:00; 2013-06-12 21:30:00;I look forward to any tip.. Adi Cohn SSCoach Group: General Forum Members Points: 19510 Visits: 6653 You can use the function datediff, which works with 3 parameters – Interval type (minutes, hours, days, etc') Start time and end time. For example`Select datediff(mi,'20130612 10:00:00', '20130612 11:00:00')Select datediff(hour,'20130612 10:00:00', '20130612 11:00:00')`Adi --------------------------------------------------------------To know how to ask questions and increase the chances of getting asnwers:http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ Mark Cowne SSChampion Group: General Forum Members Points: 14806 Visits: 26578 Assuming the shift pausetimes don't overlap, this should give the worktimes`DECLARE @tblSchema TABLE (Shift INT, Code INT, dtBegin DATETIME, dtEnd DATETIME)INSERT INTO @tblSchema(Shift,Code,dtBegin,dtEnd)VALUES(1, 1, '20130611 06:00:00', '20130611 14:21:00'),(1, 11, '20130611 08:00:00', '20130611 08:15:00'),(1, 12, '20130611 10:15:00', '20130611 10:30:00'),(1, 13, '20130611 12:30:00', '20130611 12:45:00'),(2, 1, '20130611 14:21:00', '20130611 23:31:00'),(2, 11, '20130611 16:45:00', '20130611 17:00:00'),(2, 12, '20130611 19:00:00', '20130611 19:15:00'),(2, 13, '20130611 21:15:00', '20130611 21:30:00'),(1, 1, '20130612 06:00:00', '20130612 14:21:00'),(1, 11, '20130612 08:00:00', '20130612 08:15:00'),(1, 12, '20130612 10:15:00', '20130612 10:30:00'),(1, 13, '20130612 12:30:00', '20130612 12:45:00'),(2, 1, '20130612 14:21:00', '20130612 23:31:00'),(2, 11, '20130612 16:45:00', '20130612 17:00:00'),(2, 12, '20130612 19:00:00', '20130612 19:15:00'),(2, 13, '20130612 21:15:00', '20130612 21:30:00')SELECT t.Shift, t.dtBegin, t.dtEnd, DATEDIFF(minute,t.dtBegin,t.dtEnd) - COALESCE(SUM(DATEDIFF(minute,t2.dtBegin,t2.dtEnd)),0) AS WorkingTimeMinutesFROM @tblSchema tLEFT OUTER JOIN @tblSchema t2 ON t2.Shift = t.Shift AND t2.Code <> 1 AND t2.dtBegin BETWEEN t.dtBegin AND t.dtEnd AND t2.dtEnd BETWEEN t.dtBegin AND t.dtEndWHERE t.Code = 1GROUP BY t.Shift,t.dtBegin,t.dtEndORDER BY t.Shift,t.dtBegin;` ____________________________________________________Deja View - The strange feeling that somewhere, sometime you've optimised this query beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537 goran.p Forum Newbie Group: General Forum Members Points: 5 Visits: 156 Thanks for your quick answers ...Mark, I have test You code.In original, it calculates the WorkingTimeMinutes for each shift and day.Depending of that my table contains many other records, I made a small modification, like `..WHERE t.Code = 1-- my added codeand t.dtBegin >= '2013-06-11 12:40:21' and t.dtBegin <= '2013-06-12 08:59' `to limit the output.The result I got then was, of natural things, not correct, because it's select from the next coming shift, etc.If we as example use the values from my first post, how could I use them for get a correct calculation ?dtStart : '2013-06-11 12:40:21'dtStop : '2013-06-12 08:55:16'If I calculated it "by hand", I came to that the summary of the WorkingTimeMinutes should be 781, when using the values described above.In some way the "dtStart" should be used for the first calc, and the "dtStop" at last calc. But how fix this in the query ?? Mark Cowne SSChampion Group: General Forum Members Points: 14806 Visits: 26578 Can you explain how you get the value 781? ____________________________________________________Deja View - The strange feeling that somewhere, sometime you've optimised this query beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537 goran.p Forum Newbie Group: General Forum Members Points: 5 Visits: 156 Your comment : Can you explain how you get the value 781?Very easy, I calculated wrong. I saw it for a while ago ..By the way, I have modified Your code as follows, (I ship the create of table and data).`DECLARE @dtStart DATETIME ;DECLARE @dtStop DATETIME ;SET @dtStart = '2013-06-11 12:40' ; -- '2013-06-11 12:40:21' ;set @dtStop = '2013-06-12 08:55' ; -- '2013-06-12 08:59' ;SELECT t.Shift ,t.dtBegin ,t.dtEnd ,DATEDIFF(minute, /*t.dtBegin*/(case when (t.dtBegin < @dtStart) then @dtStart else t.dtBegin end), /*t.dtEnd*/ (case when (t.dtEnd > @dtStop) then @dtStop else t.dtEnd end)) - COALESCE(SUM(DATEDIFF(minute,t2.dtBegin,t2.dtEnd)),0) AS WorkingTimeMinutes----------------------------------------------------------------- Just for verification under the development ...--------------------------------------------------------------- , (case when (t.dtBegin < @dtStart) then @dtStart else t.dtBegin end) as dtStartValue , (case when (t.dtEnd > @dtStop) then @dtStop else t.dtEnd end) as dtStopValueFROM @tblSchema tLEFT OUTER JOIN @tblSchema t2 ON t2.Shift = t.Shift AND t2.Code <> 1 AND t2.dtBegin BETWEEN /*t.dtBegin*/ (case when (t.dtBegin < @dtStart) then @dtStart else t.dtBegin end) AND /* t.dtEnd */ (case when (t.dtEnd > @dtStop) then @dtStop else t.dtEnd end) AND t2.dtEnd BETWEEN /*t.dtBegin*/ (case when (t.dtBegin < @dtStart) then @dtStart else t.dtBegin end) AND /* t.dtEnd */ (case when (t.dtEnd > @dtStop) then @dtStop else t.dtEnd end)WHERE t.Code = 1 and t.dtEnd >= @dtStart and t.dtBegin <= @dtStop GROUP BY t.Shift,t.dtBegin,t.dtEnd, t.CodeORDER BY t.dtBegin, t.Shift;`Note! I have added some fields, just for debuging ...It's (more or less) working as my thoughts, but there is still a problem that I don't see.It's coming when @dtStart is in a pause period, if I for example enter time value = '2013-06-11 12:40' then the result is added with 5 minutes.Do I enter a value outside pausetimes then its OKExamples: '2013-06-11 12:40' gives WorkingTimeMinutes = 101 WRONG (should be 96) '2013-06-11 12:30' gives WorkingTimeMinutes = 96 OK '2013-06-11 12:45' gives WorkingTimeMinutes = 96 OK '2013-06-11 12:00' gives WorkingTimeMinutes = 126 OK '2013-06-11 12:50' gives WorkingTimeMinutes = 91 OK I see the same behavour with the @dtStop .. Outside Pausetime it working okProblerly is the reason easy, but I don't see it right now.Any ideas where I'm is doing wrong ??