Calculate Time Detla between 2 rows

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)

Calculate Time Detla between 2 rows

Author

Topic

janrichards
Starting Member

2 Posts

Posted - 2011-02-09 : 14:55:14

Dear Sql Team,

I am trying to calculate the time delta between two rows without using a cursor. If you could point me in the proper direction that would be great.

Here is my table:
Flush1, Flush2, Flush3, Flush4, TranTimeStamp
1, 0, 0, 0, 2/8/2011 12:00:38.470
0, 0, 0, 0, 2/8/2011 12:01:03.720
0, 1, 0, 0, 2/8/2011 12:26:37.053
0, 0, 0, 0, 2/8/2011 12:26:46.570
0, 0, 1, 0, 2/8/2011 12:26:51.570
0, 0, 0, 0, 2/8/2011 12:27:02.320

This data comes from a PLC (only provide a BIT of information). All flushes = 0 means the flush has stopped. I need to calculate the start and stop time and format it as follows:

Flush, Start, Stop, ElapsedTime
Flush1, 2/8/2011 12:00:38.470, 2/8/2011 12:01:04.000, 0:00:25
Flush2, 2/8/2011 12:26:37.053, 2/8/2011 12:26:46.570, 0:00:09
Flush3, 2/8/2011 12:26:51.570, 2/8/2011 12:27:02.320, 0:00:10

We know the flush start when the bit turns to 1 for a flush and stops when all bits turn to 0. Onlu one flush at at time can have the bit turn to 1 becuase only one flush can run at a time.

janrichards
Starting Member

2 Posts

Posted - 2011-02-09 : 19:06:16

ok, here is what I did:

--declare report variables - starting time and ending time for report data to be pulled
declare @start datetime
declare @end datetime

--setting variable for testing
--should comment out later
set @start = '2011-02-09 02:39:14.363'
set @end = '2011-02-10 02:39:14.363'

--creating a temp table with extra column to determine start and stop
select Water_Flush1_Front,Water_Flush1_Rear, Water_Flush3_Front,Water_Flush3_Rear,
TransTimeStamp,
Water_Code = 'S' into #FlushTrends
from dbo.Water_Flush_Trending_dev --can change this to dbo.Water_Flush_Trending later
where TransTimeStamp between @start and @end

--updating new temp table with End Code 'E'
update #FlushTrends
set Water_Code = 'E'
where Water_Flush1_Front = 0 and Water_Flush1_Rear = 0
and Water_Flush3_Front= 0 and Water_Flush3_Rear = 0;

--double checking the data
--Select * from #FlushTrends;

--using with rows to determine the time between rows but only returning values that have an S marked for start time.
WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY transTimeStamp) AS rn
FROM #FlushTrends
)
SELECT
mc.water_Flush1_Front,mc.Water_Flush1_Rear,mc.water_Flush3_Front, mc.Water_Flush3_Rear,
mc.transtimestamp as StartFlush,mp.transTimeStamp as EndFlush,
DATEDIFF(second, mc.transtimeStamp, mp.transtimeStamp)as Time_In_Seconds,
CONVERT(varchar,(mp.transtimeStamp-mc.transtimeStamp),108) as Time_Elasped, mc.Water_Code
into #FlushTrends2
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1
where mc.Water_code = 'S' -- can comment the where clause out if you want to determine the time between starts

--double-check again
--select * from #FlushTrends2

Select
Sort = 'Flush1_Front',StartFlush, EndFlush, Time_In_Seconds, Time_Elasped
from #FlushTrends2 where water_Flush1_Front = 1
union all
Select
Sort = 'Flush1_Rear',StartFlush, EndFlush, Time_In_Seconds, Time_Elasped
from #FlushTrends2 where water_Flush1_Rear = 1
union all
Select
Sort = 'Flush3_Front',StartFlush, EndFlush, Time_In_Seconds, Time_Elasped
from #FlushTrends2 where water_Flush3_Front = 1
union all
Select
Sort = 'Flush3_Rear',StartFlush, EndFlush, Time_In_Seconds, Time_Elasped
from #FlushTrends2 where water_Flush3_Rear = 1

drop table #FlushTrends, #Flushtrends2

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-09 : 19:29:38

How is this? no overhead or temp tables.


DECLARE @flush table (
 Flush1 bit not null
,Flush2 bit not null
,Flush3 bit not null
,Flush4 bit not null
,TranTimeStamp datetime not null)

INSERT INTO @flush
SELECT 1, 0, 0, 0, '2/8/2011 12:00:38.470' UNION
SELECT 0, 0, 0, 0, '2/8/2011 12:01:03.720' UNION
SELECT 0, 1, 0, 0, '2/8/2011 12:26:37.053' UNION
SELECT 0, 0, 0, 0, '2/8/2011 12:26:46.570' UNION
SELECT 0, 0, 1, 0, '2/8/2011 12:26:51.570' UNION
SELECT 0, 0, 0, 0, '2/8/2011 12:27:02.320'

;With FlushStart as
	(SELECT	 StartTime=TranTimeStamp
			 ,FlushNum = Case When Flush1 = 1 then 1
							 When Flush2 = 1 then 2
							 When Flush3 = 1 then 3
							 When Flush4 = 1 then 4
						else null end
			,ROWNUM = ROW_NUMBER() OVER (  ORDER BY TranTimeStamp asc)
	 FROM @Flush
	 WHERE Flush1 = 1 or Flush2 = 1 or Flush3 = 1 or Flush4 = 1
	)
 , FlushStop as 
	(SELECT	 EndTime = TranTimeStamp
			,ROWNUM = ROW_NUMBER() OVER (  ORDER BY TranTimeStamp asc)
	 FROM @Flush
	 WHERE Flush1 = 0 and Flush2 =0 and Flush3 =0 and Flush4 = 0
	)

SELECT FlushNum,StartTime,EndTime, DELTA = Convert(char(10),EndTime-StartTime,108)
FROM FlushStart,FlushStop
Where FlushStart.RowNum = FlushStop.Rownum

/*results 
FlushNum	StartTime	EndTime	DELTA
1	2011-02-08 12:00:38.470	2011-02-08 12:01:03.720	00:00:25  
2	2011-02-08 12:26:37.053	2011-02-08 12:26:46.570	00:00:09  
3	2011-02-08 12:26:51.570	2011-02-08 12:27:02.320	00:00:10  
*/

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources