Get the smallest timespan between two entries

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
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
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||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 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
>
|||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
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:
>
>
>
>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
>
|||On Thu, 28 Oct 2004 20:31:00 -0400, Steve Kass wrote:

>and I shouldn't have replied to your post specifically - sorry
Hi Steve,
De nada. As long as the original poster sees it, all's well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get Missing Dates

Hi,
I have 2 tables, one a list of people, and the other a table of daily
diary entries for those people.
Now i need to get a list of all people and dates that don't have an entry.
I have created a calendar table in order to assist but i can't figure
out how to pull back the results as i need.
What i need is something along the lines of
PersonId | Date
--
1 | '2006-01-01'
1 | '2006-01-02'
1 | '2006-01-12'
2 | '2006-01-01'
2 | '2006-01-21'
etc... | etc...
Any help would be greatly appreciated.
Many thanks
MattSo we have some idea...
http://www.aspfaq.com/5006
"Matt Brailsford" <matt@.gradiation.co.uk> wrote in message
news:OkGE6nvQGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have 2 tables, one a list of people, and the other a table of daily
> diary entries for those people.
> Now i need to get a list of all people and dates that don't have an entry.
> I have created a calendar table in order to assist but i can't figure out
> how to pull back the results as i need.
> What i need is something along the lines of
> PersonId | Date
> --
> 1 | '2006-01-01'
> 1 | '2006-01-02'
> 1 | '2006-01-12'
> 2 | '2006-01-01'
> 2 | '2006-01-21'
> etc... | etc...
> Any help would be greatly appreciated.
> Many thanks
> Matt|||Try this:
declare @.Person table (personid int)
insert @.person values (1)
insert @.person values (2)
declare @.Date table (dt datetime)
insert @.Date values ('01 Jan 2006')
insert @.Date values ('02 Jan 2006')
insert @.Date values ('03 Jan 2006')
insert @.Date values ('04 Jan 2006')
insert @.Date values ('05 Jan 2006')
insert @.Date values ('06 Jan 2006')
insert @.Date values ('07 Jan 2006')
declare @.Diary table (personid int, dt datetime)
insert @.Diary values(1, '01 Jan 2006')
insert @.Diary values(1, '03 Jan 2006')
insert @.Diary values(1, '04 Jan 2006')
insert @.Diary values(2, '05 Jan 2006')
insert @.Diary values(2, '06 Jan 2006')
select p.personid, d.dt
from @.Person p
cross join @.Date d
left outer join @.Diary e
on p.personid = e.personid and d.dt = e.dt
where e.personid is null|||Excellent,
That looks exactly what i need.
I'll give it a try.
jeff.bolton@.citigatehudson.com wrote:
> Try this:
> declare @.Person table (personid int)
> insert @.person values (1)
> insert @.person values (2)
> declare @.Date table (dt datetime)
> insert @.Date values ('01 Jan 2006')
> insert @.Date values ('02 Jan 2006')
> insert @.Date values ('03 Jan 2006')
> insert @.Date values ('04 Jan 2006')
> insert @.Date values ('05 Jan 2006')
> insert @.Date values ('06 Jan 2006')
> insert @.Date values ('07 Jan 2006')
> declare @.Diary table (personid int, dt datetime)
> insert @.Diary values(1, '01 Jan 2006')
> insert @.Diary values(1, '03 Jan 2006')
> insert @.Diary values(1, '04 Jan 2006')
> insert @.Diary values(2, '05 Jan 2006')
> insert @.Diary values(2, '06 Jan 2006')
> select p.personid, d.dt
> from @.Person p
> cross join @.Date d
> left outer join @.Diary e
> on p.personid = e.personid and d.dt = e.dt
> where e.personid is null
>