?
Solved

Cursors in sql server

Posted on 2009-02-17
8
Medium Priority
?
407 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
0
Comment
Question by:VarmaCorporation
  • 3
  • 2
  • 2
7 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23667372
What purpose is SNO?  is it an autonumber?  What version of SQL Server?
0
 

Author Comment

by:VarmaCorporation
ID: 23667376
Sno is an autonumber... SQL server version is 2005
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 23667542
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:VarmaCorporation
ID: 23667679
I want to select only those rows where the time difference between two successive rows is more than 15 mins...
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 100 total points
ID: 23667691
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
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23670875
;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

0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 100 total points
ID: 23670880
;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
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question