time difference between rows in the same table

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

General SQL Server Forums

New to SQL Server Programming

time difference between rows in the same table

Author

Topic

naidu4u
Starting Member

23 Posts

Posted - 2010-10-21 : 16:55:17

Hello,

I have three columns in my table

eventid comment timestamp
1 fiu 2010-10-12 12:25:00
2 abc 2010-10-13 03:19:00
2 xyz 2010-10-13 03:20:00
2 123 2010-10-13 03:29:00
3 kri 2010-10-14 20:29:00

and I am trying to get the time difference between the rows with same eventid

Thanks in advance.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-21 : 17:18:03

[code]declare @t table (eventid int, comment char(3), timestamp datetime)
insert @t select
1, 'fiu', '2010-10-12 12:25:00' union all select
2, 'abc', '2010-10-13 03:19:00' union all select
2, 'xyz', '2010-10-13 03:20:00' union all select
2, '123', '2010-10-13 03:29:00' union all select
3, 'kri', '2010-10-14 20:29:00'

;with t(eventid, comment, timestamp, rn) as
(select *, row_number() over (partition by eventid order by timestamp) from @t)
select a.*, datediff(s, b.timestamp, a.timestamp) from t a
left join t b on a.eventid=b.eventid and a.rn=b.rn+1[/code]Change table name accordingly.

naidu4u
Starting Member

23 Posts

Posted - 2010-10-21 : 17:45:23

I am trying to understand this but my actual prob is that I have two tables(X,Y) in which
X:
'eventID' 'Station' 'StartTime'

with 'z' no .of rows

Y:
'ID' 'refEventID' 'Comment' 'TimeStamp'

with 's' no .of rows

I need to find the time difference between each comment with the same eventID (refEventID) per station and StartTime

so what I have done is that

select a.eventid, a.stn,
substring(convert(varchar(8),a.localstarttime,112),1,4)+'/'+substring(convert(varchar(8),a.localstarttime,112),5,2)+'/'
+substring(convert(varchar(8),a.localstarttime,112),7,2) xyz,
b.refeventid, b.comment, b."timestamp"
from X a join Y b
on a.eventid = b.refeventid
group by a.eventid, a.stn, a.localstarttime, b.refeventid, b.comment, b."timestamp"

so after writing this query I got the result like

(eventid) (comment) (timestamp)
1 fiu 2010-10-12 12:25:00
2 abc 2010-10-13 03:19:00
2 xyz 2010-10-13 03:20:00
2 123 2010-10-13 03:29:00
3 kri 2010-10-14 20:29:00
with 'q' no. of rows and now I want to get the time difference between the timestamps with same eventID.

naidu4u
Starting Member

23 Posts

Posted - 2010-10-22 : 10:48:15

Thanks a lot Robvolk

Just now implemented the query but I am using SQL Server 2000 which is not allowing me to use row_number() as a function so I tried to write something inorder to generate the rownumber and am getting an error msg when have written this query

select k.* from
(select a.eventid,
substring(convert(varchar(8),a.localstarttime,112),1,4)+'/'
+substring(convert(varchar(8),a.localstarttime,112),5,2)+'/'
+substring(convert(varchar(8),a.localstarttime,112),7,2) xyz,
b.refeventid, b.comment, b."timestamp"
from a join b
on a.eventid = b.refeventid
group by a.eventid, a.localstarttime, b.refeventid, b.comment, b."timestamp") k;
with k as (select * from (select ( select count(*) from k as e2 where e2.timestamp <= e1.timestamp) as rn, e1.* from k as e1) h)
select i1.*, datediff(mm, i1."timestamp", i2."timestamp") from k i1
left join k i2
on i1.eventid = i2.eventid and i1.rn=i2.rn+1;

error: incorrect syntax near the keyword 'with'.

Can you please help me with that....

Thanks in advance...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-22 : 13:00:47

Try this:

SELECT a.eventid, a.stn, CONVERT(VARCHAR(8),a.localstarttime,111) xyz, b.refeventid, b.comment, b."timestamp", 
DATEDIFF(second, (SELECT MAX("timestamp") FROM Y WHERE refeventid=b.refeventid AND "timestamp"<b."timestamp"), b."timestamp") seconds
FROM X a JOIN Y b ON a.eventid = b.refeventid

I'm not sure this is exactly what you need as I couldn't test it.

For future reference, please post SQL Server 2000 questions in the SQL 2000 forums. And please post your actual table structures at the outset. It impolite and annoying to reply with "sorry, my real tables are like this..." after someone posts a solution based on the first structure(s).

naidu4u
Starting Member

23 Posts

Posted - 2010-10-22 : 13:19:13

quote:
Originally posted by robvolk

Try this:
SELECT a.eventid, a.stn, CONVERT(VARCHAR(8),a.localstarttime,111) xyz, b.refeventid, b.comment, b."timestamp",
DATEDIFF(second, (SELECT MAX("timestamp") FROM Y WHERE refeventid=b.refeventid AND "timestamp"<b."timestamp"), b."timestamp") seconds
FROM X a JOIN Y b ON a.eventid = b.refeventid
I'm not sure this is exactly what you need as I couldn't test it.

For future reference, please post SQL Server 2000 questions in the SQL 2000 forums. And please post your actual table structures at the outset. It impolite and annoying to reply with "sorry, my real tables are like this..." after someone posts a solution based on the first structure(s).

I am really sorry and I dint mean that...

But this is what I am exactly looking for...Thanks a lot dude...I love the sql.
Actually I am working on Cognos so I was weak in SQL but trying to learn some good points from seniors like you...
Thanks a ton and I really mean it.

naidu4u
Starting Member

23 Posts

Posted - 2010-10-22 : 16:37:30

need to find the average frequency of the comments for each eventid

I tried writing this query but its giving me some errors...Please help me with this...

select (select avg("difference") from t where eventid=k.eventid and "timestamp"< k."timestamp") average from

(SELECT a.eventid, CONVERT(VARCHAR(10),a.localstarttime,111) xyz, b.refeventid, b.comment, b."timestamp",
DATEDIFF(second, (SELECT MAX("timestamp") FROM Y WHERE refeventid=b.refeventid AND "timestamp"<b."timestamp"), b."timestamp") "difference"
FROM X a JOIN Y b ON a.eventid = b.refeventid
group by a.eventid, a.localstarttime, b.refeventid, b.comment, b."timestamp" ) t

join t k
on t.eventid = k.eventid

but its giving me an error saying that
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 't'.

My table t is

12827 2010/07/28 12827 waitin 2010-07-28 18:50:00 NULL
12827 2010/07/28 12827 wait/> 2010-07-28 18:52:00 120
12827 2010/07/28 12827 check 2010-07-28 19:28:00 2160
15167 2010/09/08 15167 test. 2010-09-09 18:35:00 NULL
15923 2010/09/22 15923 testt 2010-09-23 05:21:00 NULL
16812 2010/10/12 16812 wor.. 2010-10-13 03:19:00 NULL
16812 2010/10/12 16812 fie... 2010-10-13 03:20:00 60
16812 2010/10/12 16812 test.. 2010-10-13 03:29:00 540

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-22 : 18:04:49

If you need the average for all comments:

select avg("difference") average from
(SELECT a.eventid, CONVERT(VARCHAR(10),a.localstarttime,111) xyz, b.refeventid, b.comment, b."timestamp", 
DATEDIFF(second, (SELECT MAX("timestamp") FROM Y WHERE refeventid=b.refeventid AND "timestamp"<b."timestamp"), b."timestamp") "difference"
FROM X a JOIN Y b ON a.eventid = b.refeventid
group by a.eventid, a.localstarttime, b.refeventid, b.comment, b."timestamp" ) t

If you need the average for each event:

select t.eventid, avg("difference") average from
(SELECT a.eventid, CONVERT(VARCHAR(10),a.localstarttime,111) xyz, b.refeventid, b.comment, b."timestamp", 
DATEDIFF(second, (SELECT MAX("timestamp") FROM Y WHERE refeventid=b.refeventid AND "timestamp"<b."timestamp"), b."timestamp") "difference"
FROM X a JOIN Y b ON a.eventid = b.refeventid
group by a.eventid, a.localstarttime, b.refeventid, b.comment, b."timestamp" ) t
group by t.eventid

naidu4u
Starting Member

23 Posts

Posted - 2010-10-25 : 10:55:33

quote:

Originally posted by robvolk

If you need the average for all comments:

select avg("difference") average from
(SELECT a.eventid, CONVERT(VARCHAR(10),a.localstarttime,111) xyz, b.refeventid, b.comment, b."timestamp", 
DATEDIFF(second, (SELECT MAX("timestamp") FROM Y WHERE refeventid=b.refeventid AND "timestamp"<b."timestamp"), b."timestamp") "difference"
FROM X a JOIN Y b ON a.eventid = b.refeventid
group by a.eventid, a.localstarttime, b.refeventid, b.comment, b."timestamp" ) t

If you need the average for each event:

select t.eventid, avg("difference") average from
(SELECT a.eventid, CONVERT(VARCHAR(10),a.localstarttime,111) xyz, b.refeventid, b.comment, b."timestamp", 
DATEDIFF(second, (SELECT MAX("timestamp") FROM Y WHERE refeventid=b.refeventid AND "timestamp"<b."timestamp"), b."timestamp") "difference"
FROM X a JOIN Y b ON a.eventid = b.refeventid
group by a.eventid, a.localstarttime, b.refeventid, b.comment, b."timestamp" ) t
group by t.eventid

Thank you robvolk...
you simply rock...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources