tsql two rows time difference calculation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

tsql two rows time difference calculation

Author

Topic

musbdc
Starting Member

5 Posts

Posted - 2013-06-10 : 04:32:43

Hi,
I will really appreciate if you could help me to solve a very complicated problem.
table: machinedata
ID CODE(1=working,0=stopped) dt
1 1 2013-06-01 22:12:23
2 1 2013-06-02 20:11:11
3 0 2013-06-03 12:02:45
4 1 2013-06-04 04:35:25
5 0 2013-06-05 09:42:01
6 0 2013-06-05 17:10:10
I need to find the total working and stopage time for the machine.
One of the difficulty, it may be 10 records with code 1 or 0 in sequense with different times.
Many thanks for your time.
chris

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-10 : 04:48:42

what should be the output for above sample data?

--
Chandu

musbdc
Starting Member

5 Posts

Posted - 2013-06-10 : 04:56:32

quote:
Originally posted by bandi

what should be the output for above sample data?

Thanks.
--
Chandu

codechanges total(seconds)worked total(seconds)stopped
4 times 32000 6500

manually calculated result should be similar to the following:
worked 2013-06-01 22:12:23 - 2013-06-02 20:11:11 = 79128 seconds
worked 2013-06-02 20:11:11 - 2013-06-03 12:02:45=57094 seconds
stopped 2013-06-03 12:02:45 - 2013-06-04 04:35:25 = 59560 seconds
worked 2013-06-04 04:35:25 - 2013-06-05 09:42:01 = 104796 seconds
stopped 2013-06-05 09:42:01 - 2013-06-05 17:10:10= 26899 seconds

expected output
changes totalworked totalstopage
4 79128 + 57094 +104796 =241018 59560 + 26899 =86459

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-10 : 05:28:31

Is there any other columns like datetime/any ?
Tell us the logic behind the expected result...
Do you want sum of records which has CODE=1 and sum of records which has CODE=0?

Have a look at this link
http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server
--
Chandu

musbdc
Starting Member

5 Posts

Posted - 2013-06-10 : 05:59:59

quote:
Originally posted by bandi

Is there any other columns like datetime/any ?
Tell us the logic behind the expected result...
Do you want sum of records which has CODE=1 and sum of records which has CODE=0?

Have a look at this link
http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server
--
Chandu

Hi,
I am afraid it is not a simple sum of 1 or 0s.
we have to find the totals up to a statu changes ocur.
when it is 1 we find the totals but it extends to the next line, 0.
We have to find the total seconds for 1 and 0 at the end.

Many thanks for the link. But We have no end date for our case. We have to get the end date next record ahead when the changes ocur.
in our sample the start date for 1 is 2013-06-01 22:12:23 and the end date is 2013-06-03 12:02:45 when the status became 0.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-10 : 06:38:50

how come the column Changes is 4 ?

;WITH CTE AS
(SELECT ID,
	CODE,
	MinDate AS timeOut,
	MAX(dt) TimeIn
FROM machinheData t
OUTER APPLY (SELECT MIN(dt) AS MinDate
             FROM machinheData
             WHERE  dt > t.dt
             )t1
GROUP BY ID, CODE, MinDate
)
SELECT 
	SUM(CASE WHEN CODE = 1 THEN DATEDIFF( SS, TimeIn, TimeOut) END) WorkedSeconds
	,SUM(CASE WHEN CODE = 0 THEN DATEDIFF( SS, TimeIn, TimeOut) END) WorkedSeconds
FROM CTE

--
Chandu

musbdc
Starting Member

5 Posts

Posted - 2013-06-10 : 07:00:52

quote:

Originally posted by bandi

how come the column Changes is 4 ?

;WITH CTE AS
(SELECT ID,
	CODE,
	MinDate AS timeOut,
	MAX(dt) TimeIn
FROM machinheData t
OUTER APPLY (SELECT MIN(dt) AS MinDate
             FROM machinheData
             WHERE  dt > t.dt
             )t1
GROUP BY ID, CODE, MinDate
)
SELECT 
	SUM(CASE WHEN CODE = 1 THEN DATEDIFF( SS, TimeIn, TimeOut) END) WorkedSeconds
	,SUM(CASE WHEN CODE = 0 THEN DATEDIFF( SS, TimeIn, TimeOut) END) WorkedSeconds
FROM CTE

--
Chandu

IT IS EXCELLENT AND WORKS PERFECTLY!!!. many many thanks. Could you please add someting to find the number of changes. I mean when goes 1 to 0 or 0 to 1 we say 1 changes happened. For our example there are four distinct changes: 1 - 0 - 1 - 0 (or three 0 - 1 - 0)

We read data many times as 1 in sequence and we have to accept as only one changes until 0 comes. 1 to 0 = 1 changes. After several 0s may come. when it turns to 1 again, the second changes happens.
So 1 turns to 0 is one change and when 0 turns to 1 is another change. We should not count the repeating 1s and 0s.

Many thanks bandi. I hope you will do a last touch.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-10 : 08:05:00

[code];WITH CTE AS
(SELECT ID
,CODE
,MinDate AS timeOut
,MAX(dt) TimeIn
,COUNT( distinct Grp) Change
FROM machinheData t
OUTER APPLY (SELECT MIN(dt) OVER() AS MinDate
,ROW_NUMBER() OVER(ORDER BY dt) -
ROW_NUMBER() OVER(PARTITION BY code ORDER BY dt) AS Grp
FROM machinheData
WHERE dt > t.dt
)t1
GROUP BY ID, CODE, MinDate
)
SELECT MAX(Change) change
,SUM(CASE WHEN CODE = 1 THEN DATEDIFF( SS, TimeIn, TimeOut) END) WorkedSeconds
,SUM(CASE WHEN CODE = 0 THEN DATEDIFF( SS, TimeIn, TimeOut) END) WorkedSeconds
FROM CTE[/code]

--
Chandu

musbdc
Starting Member

5 Posts

Posted - 2013-06-10 : 08:18:29

Perfection!!! I donot know how to thank you. I hope I can generate some Money from this Project and share with you:)
Best wishes.
Chris

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-10 : 08:41:17

quote:
Originally posted by musbdc

Perfection!!! I donot know how to thank you. I hope I can generate some Money from this Project and share with you:)
Best wishes.
Chris

Glad to be of...........

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 02:00:25

quote:
Originally posted by visakh16

sorry but isnt this just enough so far as only concrened thing is date sequence and the finding timedifference between the records

;With CTE
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY dt) AS Seq,*
FROM table
)

SELECT SUM(CASE WHEN c1.[Code] = 0 THEN DATEDIFF(ss,c1.[dt],c2.[dt]) ELSE 0 END) AS StoppedTime,
SUM(CASE WHEN c1.[Code] = 1 THEN DATEDIFF(ss,c1.[dt],c2.[dt]) ELSE 0 END) AS WorkededTime,
SUM(CASE WHEN c1.[Code] <> c2.[Code] THEN 1 ELSE 0 END) AS Changes
FROM CTE c1
INNER JOIN CTE c2
ON c2.Seq = c1.Seq + 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-11 : 02:11:40

quote:
Originally posted by visakh16

sorry but isnt this just enough so far as only concrened thing is date sequence and the finding timedifference between the records

;With CTE
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY dt) AS Seq,*
FROM table
)

SELECT SUM(CASE WHEN c1.[Code] = 0 THEN DATEDIFF(ss,c1.[dt],c2.[dt]) ELSE 0 END) AS StoppedTime,
SUM(CASE WHEN c1.[Code] = 1 THEN DATEDIFF(ss,c1.[dt],c2.[dt]) ELSE 0 END) AS WorkededTime,
SUM(CASE WHEN c1.[Code] <> c2.[Code] THEN 1 ELSE 0 END) AS Changes
FROM CTE c1
INNER JOIN CTE c2
ON c2.Seq = c1.Seq + 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Nice visakh...

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 02:16:48

quote:
Originally posted by bandi

Nice visakh...

--
Chandu
[/quote]
Thx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources