We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Cursors in sql server

Medium Priority
417 Views
Last Modified: 2012-05-06
One of the tables has a date column in the sql server database. I need to calculate the time difference between all the consecutive rows i.e., difference between row1 and row2,differance between row2 and row3 and so on... I do know that this can be possible using cursors... Please help me in how to do so... My table structure is as follows
sno numeric(18,0)
Description varchar(150)
datentime datetime
Comment
Watch Question

What purpose is SNO?  is it an autonumber?  What version of SQL Server?

Author

Commented:
Sno is an autonumber... SQL server version is 2005
Top Expert 2006

Commented:
select a.sno, datediff(hh,(select c.datentime from yourtable c where c.sno = (select max(b.sno) from yourtable b where b.sno < a.sno)), a.datentime) , a.description
from yourtable a

Author

Commented:
I want to select only those rows where the time difference between two successive rows is more than 15 mins...
Top Expert 2006
Commented:
select a.sno, datediff(MINUTE,(select c.datentime from yourtable c where c.sno = (select max(b.sno) from yourtable b where b.sno < a.sno)), a.datentime) , a.description
from yourtable a
where
datediff(MINUTE,(select c.datentime from yourtable c where c.sno = (select max(b.sno) from yourtable b where b.sno < a.sno)), a.datentime)>15

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
;with Diffs as (select row_number() over (order by datenTime) rn, sno, datentime,description from YourTable)
select d1.sno, d1.description, d1.datenTime, datediff(ms, d1.datentime, d2.datentime) 'ms from Prev to Curr', d2.datentime as 'Previous DateNTime'
from diffs d1
left join diffs d2
on d1.rn=d2.rn+1

;with Diffs as (select row_number() over (order by datenTime) rn, sno, datentime,description from YourTable)
select d1.sno, d1.description, d1.datenTime, datediff(n, d1.datentime, d2.datentime) 'n from Prev to Curr', d2.datentime as 'Previous DateNTime'
from diffs d1
join diffs d2
on d1.rn=d2.rn+1
where  datediff(n, d1.datentime, d2.datentime) > 15
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.