Date difference in the same field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date difference in the same field

Author

Topic

ciggs
Starting Member

8 Posts

Posted - 2003-04-10 : 10:02:00

Hi All,
I'm looking for a fairly simple solution to this problem - although I suspect there isn't one. I have a table with a datetime field. Which methods could be employed to calculate the difference between one records date and the record with the nearest date within that table. ie.

Datetime Difference
10/04/03 12:05 0
10/04/03 12:09 4
10/04/03 12:15 6
10/04/03 12:17 2
10/04/03 12:19 2

Any thoughts or advice appreciated.
ciggs

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-10 : 10:21:25

Ciggs -- when you say nearest date, based on your sample data, I assume you mean nearest date BEFORE. And by difference, based on your example, I assume you mean difference by minutes? (as opposed to seconds, milliseconds, days, etc).

If so

Select Date, CASE WHEN DateBefore Is Null THEN 0 ELSE DateDiff(m,Date,DateBefore) as Difference
FROM
(
SELECT Date, (select max(date) from tbl t2 where t2.date < t.date) as DateBefore
From tbl t
)
a

- Jeff

macka
Posting Yak Master

162 Posts

Posted - 2003-04-10 : 10:22:29

I've called the columns booking_date and minutes, as both of your column names are reserved keywords.

This should get you started:


SELECT booking_date,COALESCE(
(
  SELECT TOP 1 DATEDIFF(mi,booking_date,b1.booking_date) 
  FROM booking b2 where b2.booking_date < b1.booking_date 
  ORDER BY booking_date desc)
,0) AS minutes
FROM booking b1

Cheers,

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

(ooopppps - minutes not seconds - DOH!)

Edited by - macka on 04/10/2003 10:27:04

Edited by - macka on 04/10/2003 10:45:46

Edited by - macka on 04/10/2003 10:57:47

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-10 : 10:39:57

Just to join in

select dte, coalesce(datediff(mi, dte, (select max(dte) from tbl t2 where t2.dte < t1.dte)), 0)
from tbl t1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ciggs
Starting Member

8 Posts

Posted - 2003-04-10 : 11:35:23

Thanks guys, just goes to show that things you think will require cursors and all other nonsense usually doesn't!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources