SQL Query...​can't seem to wrap my head around this

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 Perhaps I've been staring at it too long. Any help would be appreciated

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

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

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

Maddus Mattus said:

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

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.

ploe said:

Maddus Mattus said:

*snip*

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.

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

Maddus Mattus said:

ploe said:

*snip*

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

How about using the BETWEEN operator ...

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

?

Sabot said:

Maddus Mattus said:

*snip*

How about using the BETWEEN operator ...

 

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

 

?

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

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

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