Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2007
    Posts
    27

    Question Unanswered: At least one match...

    I've got a table that is essentially just an ID and a timestamp. I'm trying to grab a block of data such that:

    1. Each timestamp is within 4 hours of at least one other entry.
    2. At least one timestamp is within 4 hours of a given time.

    I don't really know if that's a good way to describe it, to be honest, so let me demonstrate what I mean. I've got the following table and data:

    Code:
    Create Table Events ( UserID int, MyTimestamp datetime)
    
    INSERT INTO Events VALUES ( 345, '2007-11-23 23:32:14.000' )
    INSERT INTO Events VALUES ( 345, '2007-11-24 00:17:13.000' )
    INSERT INTO Events VALUES ( 345, '2007-11-24 03:07:48.000' )
    INSERT INTO Events VALUES ( 345, '2007-11-24 03:35:56.000' )
    INSERT INTO Events VALUES ( 345, '2007-11-24 05:51:47.000' )
    INSERT INTO Events VALUES ( 345, '2007-11-24 06:01:08.000' )
    INSERT INTO Events VALUES ( 345, '2007-11-28 05:53:11.000' )
    INSERT INTO Events VALUES ( 345, '2007-11-28 06:52:25.000' )
    INSERT INTO Events VALUES ( 345, '2007-11-28 07:51:23.000' )
    INSERT INTO Events VALUES ( 345, '2007-11-28 09:06:54.000' )
    Now, given as input @UserID = 345 and @MyTimestamp = 2007-11-24 05:00:00.000 I want a query that would return the following data:

    345 2007-11-23 23:32:14.000
    345 2007-11-24 00:17:13.000
    345 2007-11-24 03:07:48.000
    345 2007-11-24 03:35:56.000
    345 2007-11-24 05:51:47.000
    345 2007-11-24 06:01:08.000

    You'll notice that any one time is within 4 hours of any other time, and at least one of the times is within 4 hours of the given time. I think the following will work for grouping them into those that are within certain times of each other:

    Code:
    SELECT DISTINCT Curr.UserID, Curr.MyTimestamp
    FROM Events as Curr INNER JOIN Events as Previous
    ON Curr.UserID = Previous.UserID AND DateDiff(hh,Curr.MyTimestamp, Previous.MyTimestamp) < 5 AND DateDiff(hh,Curr.MyTimestamp,Previous.MyTimestamp) > -5
    WHERE Curr.UserID = @UserID
    AND Curr.MyTimestamp <> Previous.MyTimestamp
    ORDER BY Curr.UserID DESC;
    The problem is that this pretty much list everything anyway, since every entry is almost certainly within 4 hours of at least one other entry somewhere. So how do I limit it to a group where they are all within 4 hours of at least one other entry, AND at least one of that "group" is within 4 hours of the given time?

    I'm sorry if I'm not making sense. I am honestly having difficulty figuring out how to describe what I want.

  2. #2
    Join Date
    Dec 2007
    Posts
    27
    Maybe I'm thinking about it wrong. Given a datetime, I need all records that are within 4 hours of that time, plus all records that are within 4 hours of those records, plus all records that are within 4 hours, etc... I would need a way to limit it to a 16 hours difference between the given time and any one record. Is that possible?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a complex union will do what you want

    which database are you using? the date functions vary so much from one to the next...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, thinking about this some more, why not just pull all the records within 16 hours?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Posts
    27
    That will pull too many. What if there is an event that happened 12 hours ago, but more than 5 hours before the next event occurred? I don't want that record in that case. However, I think your suggestion might be where I want to start. I haven't worked out the details yet (and it's time to go so I can't work on it again until tomorrow), but basically I think I want to subquery all records within 16 hours, find the first and last where there is a difference of more than 4 hours, and select all the records in between the two times. That's a close enough approximation, I think. The exact details will be worked out tomorrow. I'll let you know what I come up with.

    Oh yeah, and this one in particular is being done in Access, which I'm not used to. I'm used to SQL Server, which is what I usually work with.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sounds like you could simply pull all rows within 16 hours, use ORDER BY, and do the grunt work in your front end app

    thread moved to access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as we are in the Access forum, lets guess we are using JET..... risky I know

    have a look at the dateadd function to create your time window
    select blah from mytable where mydate >= dateadd("h",-4,targetdate)

    failing that you could take advantage for the way JET handles dates....subtract 4/24ths from the value of your specified date.
    select blah from mytable where mydate >= targetdate-(4/24) and mydate<= targetdate

    you will need to specify what target date actually is
    mydate is the date column in your table

    HTH

  8. #8
    Join Date
    Dec 2007
    Posts
    27
    So I've defined what I want to do. Now I have to figure out how to do it. Given an arbitrary block of datetimes, I need to find either the earliest date that is at least 4 hours after any earlier dates, or the earliest date itself if no others are more than 4 hours earlier. I'm not sure where to start on this. Any suggestions?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •