Grouping events that fall within x days of other events?
-
I'm having trouble coming up with a query in SQL Server 2000. Trying my best to sum it up in one sentence; I need a result set that for every EventID/StartDate that exists in tblEvents, I need all StartDates within x days to have a minimum sequential, unique number starting at 1. Now I'll go into some more detail and possibly confuse everyone. What I have is a table of events that to simplify this question include only an EventID and an StartDate field. What I need to do is for each event, find the other events that occur within x days after the event in question. I then need to number the events starting at 1 so that each eventB that occurs within x days of EventA has the minimum number different from any events from the past x days. I know I just sucked at putting it in words so I'll give an example. Here is what my idea of the end result table should like if x=2. The source table is basically the same thing minus the Num column. tblEvents ------------ EventID Date Num 1 10/1/06 1 3 10/2/06 2 7 10/1/06 3 4 10/4/06 1 6 10/10/06 1 2 10/20/06 1 5 10/21/06 2 Here's a quick diagram I made (I know it's ugly and not to scale) which hopefully helps you understand what I need better. Basically, I will be drawing a timeline using vml and each item is going to have text next to it so I need items to be staggered if they are on the same day or close to each other so that things don't overlap. I also need to minimize the vertical space it takes up which is why num needs to get back to 1 as soon as it can instead of just putting each event on its own line. The "num" column will tell me what row to put the item in. http://i10.tinypic.com/43pxycj.gif The following query gets me closer I think but it has a few problems and could be the wrong approach completely. I need this to work in SQL Server 2000 and I know that Row_Number and also Over() I think are both SQL Server 2005 only which is one problem with what I've come up with so far. Also, the partition I use has a separate value for the mirror of items (eg. EventID 7 should not be on row 1). Any tips or thoughts would be appreciated.
SELECT E1.EventID AS E1_EventID, E1.StartDate AS E1_StartDate, E2.EventID AS E2_EventID, E2.StartDate AS E2_StartDate, DATEDIFF(day, E2.StartDate, E1.StartDate) As Diff, ROW_NUMBER() OVER(PARTITION BY E1.EventID ORDER B
