SQL Query...can't seem to wrap my head around this | Tech Off

ploe

I feel like there exists a query that returns the data I'm looking for. I just can't seem to get my brain to tell me what it is Tongue Out Perhaps I've been staring at it too long. Any help would be appreciated Smiley

Let's say you have a table called WINDOW that has a START date, an END date and a unique ID.

I want to select all the rows that are within 10 minutes of each other and at least one of the rows falls within a given datetime (which I pass in my query).

Here's a sample dataset:

ID, START, END

1, 9/21/09 00:00:00, 9/21/09 00:30:00

2, 9/21/09 00:38:00, 9/21/09 02:00:00

3, 9/21/09 02:05:00, 9/21/09 04:00:00

4, 9/21/09 04:15:00, 9/21/09 04:30:00

5, 9/21/09 04:35:00, 9/21/09 07:30:00

Rows 1 -> 2 -> 3 are a set because 2's START is only 8 minutes greater than 1's END and 3's START is only 5 greater than 2's END. Following that rule, 4 and 5 are a set as well.

Passing in the datetime 9/21/09 02:00:00 should return rows 1, 2, and 3 because that time falls within the min and max time those 3 rows cover. If I were to pass in 9/21/09 04:20:00, I would expect rows 4 and 5 to be returned.

I started with this query, but that's only going to return rows 2 and 3 (the immediately adjacent rows), not N rows.

SELECT * FROM WINDOW WHERE ABS(DATEDIFF(mi, END, '9/21/09 02:00:00')) <= 10 OR ABS(DATEDIFF(mi, START, '9/21/09 02:00:00')) <= 10

And I tried another approach, but this will return rows 4 and 5 too when I only want 1, 2, and 3 Sad

SELECT * FROM WINDOW w WHERE EXISTS(SELECT TOP 1 ID FROM WINDOW w2 WHERE w2.ID != w.ID AND ABS(DATEDIFF(mi, w2.END, w.START)) <= 10 OR ABS(DATEDIFF(mi, w2.START, w.END)) <= 10)

Grr

Maddus Mattus
Do, or do not. There is no try. - Yoda

So if I understand correctly, you only want rows included that have a ten minute intersect?

And if no rows are found you want a default selection?

Then I would suggest you develop a cursor to fill a temp table and if the table has 0 rows, fire off another query,..

Then select the temp table as a resultset,..

ploe

In reply to Maddus Mattus

#Sep 21st @ 1:09 PM

Yes, if a row overlaps a specified datetime, not only return that row, but all the rows that are in the 10-minute adjacent set this row belongs to.

Maddus Mattus
Do, or do not. There is no try. - Yoda

In reply to ploe

#Sep 21st @ 2:11 PM

You need to make a cursor and evaluate on a row basis, I dont think it's possible to write this in one query.

Sabot
My name is Dave Oliver. I'm a Technical Architect.

In reply to Maddus Mattus

#Sep 21st @ 10:44 PM

How about using the BETWEEN operator ...

http://msdn.microsoft.com/en-us/library/ms187922.aspx

?

Maddus Mattus
Do, or do not. There is no try. - Yoda

In reply to Sabot

#Sep 22nd @ 10:31 AM

yeah, but you have to evaluate it on a row basis,..

so you cant use set operators to get the result you want,..

sysrpl

// changed to ints for brevity:

create table timer (
   timer_id int identity primary key,
   start int,
   duration int
)

insert into timer select 20, 15
insert into timer select 25, 5
insert into timer select 30, 5
insert into timer select 50, 10
insert into timer select 120, 40
insert into timer select 150, 25

declare
   @interval int

select @interval = 10

select
   distinct t1.timer_id
from
   timer t1
   join timer t2 on
       t2.timer_id <> t1.timer_id
       and (

(t2.start = t1.start)

or

(t2.start < t1.start

           and t2.start + t2.duration + @interval > t1.start)
           or
           (t1.start < t2.start
           and t1.start + t1.duration + @interval > t2.start)
       )
order by
   t1.timer_id