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

on
Medium Priority
417 Views
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

## View Solutions Only

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

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

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.

Commented:
;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

Commented:
;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.

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