Interval analysis

Hi,

We have "party transaction table", for which lets say grain is one row per party/customer/trans id/trans amt/ trans time stamp.

My user req is to flag to all those parties on daily basis, where there 5 or more transaction at a party location with in 30 minutes of interval.

I am thinking to store all such parties in a separate fact table with a grain of party/customer/trans id/Day/trans amt/ trans time stamp..

But I am kind of stuck on how to derive a logic to get only such parties who fall in above 30 min interval criteria.

Did any of you folks face similar scenario? could some one throw some light on this?

Please suggest.

Thanks,
Winny

You are trying to count how many events occurred within 30 minutes of the one you are looking at.

Basically it is a correlated sub query. Given an event, join back to the events and count how many their are where the time is <= 30 minutes from the time of the current event. This will give you a row for each event and how many other events occurred in the half hour. You can then use this list to build the facts.