Gaps in Events

[andyiain]

Hi

I have a list of people and events they engaged in together with the date and location of the event. These range from one off events to a series of related events (of between a handfull and several hunderd individual dates) but in all cases the reference numbers are unique and there is nothing that is common to the related events in the database.

Is there any way I can tell en masse for all of the people in the list if there are any that have attended more than X (say 50) events in one location and where there is no gap of longer than Y (say 7 days) between any two of the events.

I've searched for anything that may help but have come up with nothing so far so any steer anyone has would be very welcome.

Regards

 

[RiverGuy]

What part do you need help with? Calculating the gaps between is probably the most difficult part. The criteria of x events per location should be fairly easy. Here's an example of how to get the gaps between. You can use this as a starting point. If you use this logic, you probably want to apply it after you've already narrowed down your result set, for performance considerations.

DECLARE @T1 TABLE
(IDCol INT IDENTITY,
CustID INT,
EventID INT,
EventDate DATETIME)

INSERT INTO @T1 SELECT 1, 1, '20080101'
INSERT INTO @T1 SELECT 1, 2, '20080101'
INSERT INTO @T1 SELECT 1, 3, '20080125'
INSERT INTO @T1 SELECT 1, 4, '20080127'


INSERT INTO @T1 SELECT 2, 1, '20080101'
INSERT INTO @T1 SELECT 2, 2, '20080102'
INSERT INTO @T1 SELECT 2, 3, '20080107'
INSERT INTO @T1 SELECT 2, 4, '20080111'

SELECT c.*,
DATEDIFF(day, PreviousEventDate, EventDate) AS DaysInactive
FROM
	(SELECT a.*, 
	(SELECT TOP 1 EventDate FROM @T1 b WHERE a.CustID = b.CustID AND b.EventDate < a.EventDate ORDER BY EventDate DESC) AS PreviousEventDate
	FROM @T1 a) c