sql server mseq: Get the smallest timespan between two entries -- sql server mseq

Re: Get the smallest timespan between two entries

Hugo Kornelis
10/27/2004 11:28:56 PM

[quoted text, click to view]

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
--

Re: Get the smallest timespan between two entries

Steve Kass
10/28/2004 8:22:10 PM

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

[quoted text, click to view]

Re: Get the smallest timespan between two entries

Steve Kass
10/28/2004 8:31:00 PM

Oops - the suggestion I gave doesn't give a good query plan. This is
probably much better:

select top 1
datediff(minute,rectime, Nextrectime) as TimeDiff
from (
select
T1.rectime,
(select top 1 T2.rectime
from yourTable T2
where T2.rectime> T1.rectime
order by T2.rectime) as Nextrectime
from yourTable T1
) T
where Nextrectime is not null
order by Nextrectime - rectime

[and I shouldn't have replied to your post specifically - sorry]

SK

[quoted text, click to view]

Re: Get the smallest timespan between two entries

Hugo Kornelis
10/29/2004 8:56:29 AM

[quoted text, click to view]

Hi Steve,

De nada. As long as the original poster sees it, all's well.

Best, Hugo
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.