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:
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.
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?
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.
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
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?