June 29, 2009 at 5:59 am
Hi All
I need a successive date difference for consecutive rows. Means second row will get (2nd rowdate-1st row date)
and third row wil get (3rd row date- 2nd row date). I hav done using correlated sub queries.
Pls suggest me a query better than this.
For example :-
declare @tbl table(id int ,date datetime)
insert @tbl(id,date) select 1,getdate()
insert @tbl(id,date) select 2,getdate()+5
insert @tbl(id,date) select 3,getdate()+1
insert @tbl(id,date) select 4,getdate()+4
insert @tbl(id,date) select 5,getdate()+7
insert @tbl(id,date) select 6,getdate()+6
insert @tbl(id,date) select 7,getdate()+3
insert @tbl(id,date) select 8,getdate()+8
insert @tbl(id,date) select 9,getdate()+2
insert @tbl(id,date) select 10,getdate()+12
select a.id ,a.date, datediff(day,(select top 1 date from @tbl b where
a.id > b.id order by id desc),a.date)from @tbl a
Thanks in advance 🙂
June 29, 2009 at 9:31 am
Not sure exactly what your after.
something like this might do the job for you though.
DECLARE @ID INT
DECLARE @Date1 DATETIME
DECLARE @Date2 DATETIME
DECLARE @MaxID INT
SET @ID = 1
SET @MaxID = (SELECT MAX(id) FROM @tbl)
WHILE @ID <= @MaxID
BEGIN
SELECT @Date1 = date FROM @tbl WHERE id = @ID
SELECT @Date2 = date FROM @tbl WHERE id = @ID+1
SELECT @ID,@date1,@Date2,DATEDIFF(DAY,@Date1,@date2)
SET @ID = @ID + 1
END
June 29, 2009 at 9:35 am
mandirkumar (6/29/2009)
Hi AllI need a successive date difference for consecutive rows. Means second row will get (2nd rowdate-1st row date)
and third row wil get (3rd row date- 2nd row date). I hav done using correlated sub queries.
Pls suggest me a query better than this.
For example :-
declare @tbl table(id int ,date datetime)
insert @tbl(id,date) select 1,getdate()
insert @tbl(id,date) select 2,getdate()+5
insert @tbl(id,date) select 3,getdate()+1
insert @tbl(id,date) select 4,getdate()+4
insert @tbl(id,date) select 5,getdate()+7
insert @tbl(id,date) select 6,getdate()+6
insert @tbl(id,date) select 7,getdate()+3
insert @tbl(id,date) select 8,getdate()+8
insert @tbl(id,date) select 9,getdate()+2
insert @tbl(id,date) select 10,getdate()+12
select a.id ,a.date, datediff(day,(select top 1 date from @tbl b where
a.id > b.id order by id desc),a.date)from @tbl a
Thanks in advance 🙂
The records are in order of ID? Just want to be sure.
Edit: changed question.
June 29, 2009 at 9:43 am
Here is another solution:
select
t1.id,
t1.date,
datediff(dd,t2.date, t1.date)
from
@tbl t1
left outer join @tbl t2
on (t1.id - 1 = t2.id)
All code based on sample code provided in original post.
June 29, 2009 at 9:49 am
Good effort Lynn...thats much better :D.
Better to avoid the while loop wherever possible.
Ta
Matt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply