SQL Datediff by rows? - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

SQL Datediff by rows?

Author

Topic

headstrong
Starting Member

3 Posts

Posted - 2005-11-29 : 01:47:45

I will try to explain this as best as I can
Lets say I have a query
SELECT * FROM users WHERE pid=55
and the result is

bill 55 1/1/2005 1:25
bob 55 1/1/2005 1:35
ray 55 1/1/2005 1:38
bert 55 1/1/2005 1:42

I need to get the elapse time, (I guess using the datediff) using the first row and the next for comparing. so elapsetime would be 10 minutes for the first 2 rows and 5 minutes for the next 2 rows.

not sure how to do this, can it be done in SQL or will I need to do some vbscript. any help?
thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-29 : 02:03:18

Select T1.col1,DateDiff(minute,T1.datecol,T2.datecol) from users T1 inner join users T2
on T1.datecol<T2.datecol

Madhivanan

Failing to plan is Planning to fail

JoshuaF
Starting Member

10 Posts

Posted - 2005-11-29 : 05:29:02

Try a collated subquery. They have their uses..

Select U1.FirstName, U1.CreatorTimeStamp, DateDiff(minute, U1.CreatorTimeStamp, (select top 1 U2.CreatorTimeStamp from Users U2 where U2.UserID > U1.UserID))
from Users U1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources