Re: Get the smallest timespan between two entries

From: Steve Kass (skass_at_drew.edu)
Date: 10/29/04 

Date: Thu, 28 Oct 2004 20:22:10 -0400

This might be more efficient:

select top 1
-- add WITH TIES if you select additional columns and want duplicates
  datediff (minute, T2.rectime, min(T1.rectime)) as timeDiffMinutes
from yourTable T1 join yourTable T2
on T1.rectime > T2.rectime
group by T2.rectime
order by min(T1.rectime) - T2.rectime

Steve Kass
Drew University

Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:
>
>
>
>>Hi all,
>>
>>we have a table with a column of type "datetime"
>>
>>We want to get the smallest timespan between two entries.
>>
>>Now we get this timespan with the following query (it works but it's to
>>slow, it runs 5 secs with 250000 entries):
>>
>>select min(Datediff(minute,a.rectime,b.rectime))
>>
>>
>>from dbo.value a, dbo.value b
>
>
>>where b.rectime = ( select min(rectime) from dbo.value
>> where rectime > a.rectime )
>>
>>Any idea? Thanks in advance,
>>
>>Mike
>>
>>
>
>Hi Mike,
>
>Try changing the query to
>
>SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
>FROM dbo.value AS a, dbo.value b
>WHERE b.rectime > a.rectime
>
>You might also add something like
>
>AND b.rectime < DATEADD(minute, a.rectime, 200)
>
>where you change the 200 to a value that you know to be higher that the
>timespan you are looking for, but low enough to greatly reduce the number
>of matches between the a and b version of the value table.
>
>
>If that doesn't work, look at your indexes. This query would greatly
>benefit from an index on rectime (or rectime plus extra columns). If the
>rate of change of this table is not too high and a small performance hit
>on inserts, updates and deletes is acceptable, create a nonclustered index
>on only rectime - that should yield the best possible performance.
>
>Best, Hugo
>
>

Relevant Pages

  • Re: Get the smallest timespan between two entries
    ... >We want to get the smallest timespan between two entries. ... Try changing the query to ... benefit from an index on rectime. ... rate of change of this table is not too high and a small performance hit ...
    (microsoft.public.sqlserver.mseq)
  • Re: Get the smallest timespan between two entries
    ... Oops - the suggestion I gave doesn't give a good query plan. ... datediff(minute,rectime, Nextrectime) as TimeDiff ... >>We want to get the smallest timespan between two entries. ... >benefit from an index on rectime. ...
    (microsoft.public.sqlserver.mseq)