Find the longest duration between dates.

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

Transact-SQL (2000)

Find the longest duration between dates.

Author

Topic

WaterWolf
Starting Member

24 Posts

Posted - 2006-01-03 : 11:53:57

Hello,

I have a column in my table which contains datetime objects. I want to find the longest duration in minutes between each subsequent date in the series.

I know I can do this by selecting all the dates and ordering them, then stepping through each row with a cursor. I can compare each date with the one in the previous row using the datediff function and store greatest duration in a seperate variable.

However, is there a way of doing this without using a cursor ?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-03 : 12:04:49

[code]select
max(datediff(mi,MyDate_1,MyDate_2))
from
(
select
MyDate_1 = a.MyDate,
MyDate_2 = min(b.MyDate)
from
MyTable a
join
Mytable b
on a.MyDate < b.Mydate
group by
a.Mydate
) c[/code]

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-03 : 12:07:01

Something slightly different. take your pick:


create table #dts (dt datetime)
go
insert #dts (dt)
select getdate() dt union all
select dateadd(day,1,getdate()) union all
select dateadd(day,3,getdate()) union all
select dateadd(day,8,getdate()) union all
select dateadd(day,-1,getdate()) 


select	max(datediff(minute, dt, nextdt)) maxDurationMinutes
from	(
	select	dt
		,nextdt = (select min(dt) from #dts where dt > a.dt)
	from #dts a
	) a
where	nextdt is not null
go

drop table #dts

Be One with the Optimizer
TG

WaterWolf
Starting Member

24 Posts

Posted - 2006-01-03 : 12:33:52

Wow, you guys are fast !

That seems to work great, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources