MS-SQL Filter based on group of events in a time frame

Spur
Spur used Ask the Experts™
on
I have a table with two columns, location and timestamp in mssql 2000

I am looking to have a query that returns any location that has 3 or more timestamps within a 3 hour period. It would be 1 column of results just listing locations that fit that criteria

For example location a has events today  at 1 am 2 am 2:30 am and 5 am so location a would show up in the result set. 3 events in a 3 hour time period.

Location b would have 1 am 6 am  6:30 6:45 am  and 12 am. Since it had 3 timestamps within a 3 hr period, it counts
location c has events at 1 am 5  am 10 am and 6 pm, but we do not have 3 events within a 3 hour time period, so it does not return in the results.

I am looking for a query to do this filter.

Select location
from table_events
where (3 events occur within a 3 hour time frame)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Bumping value because this may be tough
You'll need to group by location something like


~Regards

SELECT DISTINCT(A.location)
   , B.total
FROM mytable [A]
INNER JOIN (
   SELECT location, count(location) as total
   FROM mytable
   GROUP BY location) [B]
ON A.location = B.location
WHERE b.total >= 3
AND A.timestamp BETWEEN '2009-07-17 12:00:00' AND '2009-07-17 15:00:00'
 
 
RESULTS
TX    3
CO    44
AZ    5

Open in new window

Author

Commented:
I can't restrict myself to a 3 hour period specifically in the query. I have several days worth of events and currently I manually search for anytime we have 3 events within any 3 hour time period.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

What about this?
SELECT location
from yourtable
group by location, datepart(h, timecolumn)
having count(datepart(h, timecolumn)) >= 3

Open in new window

Author

Commented:
ralmade-

The results I get have it showing only a one hour period and if it is 1 pm on one day and 1 pm on another day, it still counts that as the same time frame even though it is a different day.
Spur,

My post earlier was an example that from your question solves your post.  you will need to put your own code for time constraints. You can do this either by passing them as variables to your query, or you can use a dynamic GETDATE() in SQL and manipulate that, however you wish by adding hours, or whatever you wish.

WHERE timestamp BETWEEN GETDATE() AND DATEADD(hour, 3, GETDATE())

This will give you a time frame of the current date/time + 3 hours.


Sorry about that. Here's my attempt. Since you are using SQL 2000 then you need to create an unique key for each record. So you can create a temporary table. Here is the full example I've created.
create table periods (
location int,
date datetime
)
 
insert periods values (1, '2009-07-15 13:00')
insert periods values (1, '2009-07-15 13:30')
insert periods values (1, '2009-07-15 14:00')
insert periods values (2, '2009-07-15 13:00')
insert periods values (2, '2009-07-15 14:00')
insert periods values (2, '2009-07-15 19:00')
 
 
select * from periods
 
create table #temp_periods (
id int identity(1,1),
location int,
date datetime
)
 
insert #temp_periods
select * from periods
 
select distinct a.location from #temp_periods a
inner join #temp_periods b on a.location = b.location and a.id <> b.id and a.date >= b.date and a.date < dateadd(hh, 3, b.date)
inner join #temp_periods c on a.location = c.location and a.id <> c.id and a.date >= c.date and a.date < dateadd(hh, 3, c.date)
where b.location = c.location and b.id <> c.id
 
 
 
 
drop table #temp_periods

Open in new window

Author

Commented:
meagain35, putting in a fixed timestamp is easy, and not something I need help with. Creating a query that scans a large volume of date times for different locations and finding if there is a clump of events that occur at a location at any 3 hour time frame, whether it be three days ago or today, is my challenge. It ia dynamic table, not a fixed one, and it updates every hour. I am looking for a query to avoid manually updating it to reflect time periods.

So no, your example does not solve my post, primarily because the part I am looking to solve is to address the 3 hour time frame which can be any 3 hour time frame over the course of several days, and is always changing. Sorry I was not clearer in my question. I should have emphasized the dynamic nature of the 3 hour time frame.

Ralmada, I will play with that query later this morning and see what I can do with it.

Author

Commented:
OK ralmada, I tried several variants on that and it didn't work for me. What I finally ended up doing is creating a temporary table that took each event and added 2 hours to the time as one field and subtracted 2 hours from the other field, compared it against the original and did a count where the start time in the original fell between the plus and minus times of the temporary table.

Not exactly what I am looking for, and I will leave this question open hoping someone can point me to something that fits into the parameters I am looking for, because this method results in a few records that are not actually in my 3 hour criteria..
>>OK ralmada, I tried several variants on that and it didn't work for me.<<
Can you elaborate on what didn't work? With the example provided, it is working perfectly. If you post an example of what is not working then I can take a look at it.

Author

Commented:
I have to manually update the periods, and there are several days worth of events to create periods for.
>>I have to manually update the periods<< I'm not following you.
The query below does not require you to update anything. I've just created a temporary table because you don't have a primary key in your original table, therefore it was impossible to make comparisons.
If you post some sample data of what didn't work I'm sure we can find a solution.

select distinct a.location from #temp_periods a
inner join #temp_periods b on a.location = b.location and a.id <> b.id and a.date >= b.date and a.date < dateadd(hh, 3, b.date)
inner join #temp_periods c on a.location = c.location and a.id <> c.id and a.date >= c.date and a.date < dateadd(hh, 3, c.date)
where b.location = c.location and b.id <> c.id

Open in new window

Author

Commented:
Accepting answer, because ralmada put a lot of effort in, and I don't want to tie the kind person up any more working on this. Thanks for your efforts Ralmada :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial