How do I calculate working time between two datetime values ?
-
Hi
I'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 tblSchema
Shift;Code;dtBegin;dtEnd
1; 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..
-
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/ -
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 WorkingTimeMinutes
FROM @tblSchema t
LEFT 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.dtEnd
WHERE t.Code = 1
GROUP BY t.Shift,t.dtBegin,t.dtEnd
ORDER BY t.Shift,t.dtBegin;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
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 code
and 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 ??
-
Can you explain how you get the value 781?
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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 dtStopValue
FROM @tblSchema t
LEFT 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.Code
ORDER 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 OK
Examples:
'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 ok
Problerly is the reason easy, but I don't see it right now.
Any ideas where I'm is doing wrong ??
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply