using Datediff function between Rows

Question

Post reply

using Datediff function between Rows

Siva Chandrasekaran-432494

SSC Veteran

Points: 249
More actions
September 25, 2007 at 3:10 am

#198313

I wanted to calculate the date difference between first row and the second row in the same table. I was wondering if anybody can help me. Current date (first row) and Previous date (second row) in the date column. I would be extremely grateful if anybody can help me ?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi there,

Could you give us the structure of your table please.

thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Phil Factor SSC-Insane Points: 20244 More actions · Answer 2

CREATE TABLE #Dates

(

MyId INT IDENTITY (1,1),

TheDate Datetime)

INSERT INTO #dates(TheDate) VALUES('1 Sep 2007 10:00')

INSERT INTO #dates(TheDate) VALUES('2 Sep 2007 12:00')

INSERT INTO #dates(TheDate) VALUES('3 Sep 2007 17:00')

INSERT INTO #dates(TheDate) VALUES('8 Sep 2007 02:00')

INSERT INTO #dates(TheDate) VALUES('10 Sep 2007 11:00')

INSERT INTO #dates(TheDate) VALUES('13 Sep 2007 13:00')

SELECT MyID,

TheDate,

DATEDIFF(hour,

(SELECT f.TheDate

FROM #dates f

WHERE f.MyID=d.MyID-1)

,TheDate)

FROM #dates d

Best wishes,
Phil Factor

Ram Yadav SSC Enthusiast Points: 143 More actions · Answer 3

I would be better, if you little modify it in this manner

SELECT MyID,

TheDate,

isnull( DATEDIFF(hour,

(SELECT f.TheDate

FROM #dates f

WHERE f.MyID=d.MyID-1)

,TheDate),0) diff

FROM #dates d

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

A Join will work a LOT better than a correlated sub-query. If you have a reasonable sized table you'd see a difference trying to run this:

select d1.Id, d1.theDate,

isnull(datediff(hour, d2.theDate, d1.thedate),0)

from #dates d1

left outer join #dates d2 on d1.id=d2.id-1

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 5

Phil's method only works if the ID is contiguous and sequential. However, you can make that true if you copy the data you want into a temp table that has a new RowNum column in SQL Server 2000 and I think you might be able to use just "derived tables" if you use the ROWNUMBER function.

Since I don't have access to 2k5, I can't test it in 2k5... so I'll let someone else do the deed 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 6

Spot on! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Factor SSC-Insane Points: 20244 More actions · Answer 7

Jeff Moden (9/25/2007)
Phil's method only works if the ID is contiguous and sequential.

Er.. The temporary table had an identity column in there that was intended to ensure precisely this. It is part of the method!

And on another subject, the guy who 'corrected' my code to put a zero in the first row rather than a NULL misses my intention. The date difference is NULL (meaning unknown) as it is the first entry. A 0 would be misleading in this context. There has to be a way of distinguishing the fact that this was the first row.

However, it is all good fun.

Best wishes,
Phil Factor

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 8

Phil Factor (9/25/2007)
Jeff Moden (9/25/2007)
Phil's method only works if the ID is contiguous and sequential.
Er.. The temporary table had an identity column in there that was intended to ensure precisely this. It is part of the method!

Yep... I agree and I saw that. But, without saying otherwise, a lot of folks might mistake your good solution for something that would work on an existing permanent that has gaps. Just wanted to make sure folks understood that and I'm sorry that intent didn't show up in my all too brusque comment about the code. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)