time difference the rows in the same table...

Question

time difference the rows in the same table...

naidu4u

SSC Eights!

Points: 981
More actions
October 21, 2010 at 3:02 pm

#388072

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95410 More actions · Answer 1

In the case of eventid 2, there are three entries. So, are you looking for the difference between two rows, sorted by the eventid/timestamp?

Even better... please read the first link in my signature, and then post CREATE TABLE and INSERT statements, and also show what the expected results should be based upon the sample data provided.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95410 More actions · Answer 2

I'm assuming that you want to compare to the previous row, sorted by the date.

So, does this do what you desire?

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

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

-- for more details on how to do all of this.

DECLARE @test-2 TABLE (eventid INT,

comment char(3),

timestamp datetime,

PRIMARY KEY CLUSTERED (eventid, timestamp));

INSERT INTO @test-2

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 CTE AS

(

-- assign a "row number" to each row, starting at 1.

-- restart at 1 for each eventid

SELECT *,

RN = ROW_NUMBER() OVER (PARTITION BY eventid ORDER BY timestamp)

FROM @test-2

)

-- compare to the previous row.

SELECT t1.*,

Delta = DateDiff(minute, t2.timestamp, t1.timestamp)

FROM CTE t1

LEFT JOIN CTE t2

ON t1.eventid = t2.eventid

AND t1.RN = t2.RN+1;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

naidu4u SSC Eights! Points: 981 More actions · Answer 3

Thanks a lot Wayne...

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...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The second half of that is a CTE, which is not available in SQL 2000. You're going to have to rewrite this section to not use the CTE

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;

p.s. Please post SQL 2000 questions in the SQL 2000 forums. Post in the 2008 forums, we're going to assume you're using SQL 2008 and give you answers using SQL 2008 features

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

naidu4u SSC Eights! Points: 981 More actions · Answer 5

I am sorry

I am new to SQL server(I am working on Cognos a reporting tool)

thank you for the advice and let me google that...

naidu4u SSC Eights! Points: 981 More actions · Answer 6

Hey got exactly what I am looking for through some other friend....:-D

SELECT a.eventid, CONVERT(VARCHAR(10),a.localstarttime,111) xyz, b.refeventid, b.comment, b."timestamp",

DATEDIFF(second, (SELECT MAX("timestamp") FROM asom_comments WHERE refeventid=b.refeventid AND "timestamp"<b."timestamp"), b."timestamp") "difference"

FROM X a JOIN Y b ON a.eventid = b.refeventid

order by a.eventid, a.xyz