Tech Off Thread

7 posts

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

Back to Forum: Tech Off
  • User profile image
    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

  • User profile image
    Maddus Mattus

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

  • User profile image
    ploe

    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.

  • User profile image
    Maddus Mattus

    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.

  • User profile image
    Sabot

    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

     

    ?

  • User profile image
    Maddus Mattus

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

  • User profile image
    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      

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.