Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I calculate working time between two datetime values ? Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 3:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 15, 2014 10:58 PM
Points: 5, Visits: 142
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..

Post #1462516
Posted Wednesday, June 12, 2013 3:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 2,122, Visits: 5,472
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/
Post #1462521
Posted Wednesday, June 12, 2013 3:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 1,694, Visits: 19,552
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;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1462524
Posted Wednesday, June 12, 2013 4:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 15, 2014 10:58 PM
Points: 5, Visits: 142
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 ??


Post #1462556
Posted Wednesday, June 12, 2013 7:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 1,694, Visits: 19,552
Can you explain how you get the value 781?

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1462631
Posted Wednesday, June 12, 2013 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 15, 2014 10:58 PM
Points: 5, Visits: 142
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 ??
Post #1462654
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse