SQL Window function, count the number of events in a 180 day window using each event date as an index

Question

I need to be able to find the minimum date out of a set of dates that fit the following criteria.

3 dates which are within 180 days of each other. The logic which i'm guessing would apply is that each date would be an index from which subsequent dates are compared to. I can do this using the LAG and Window functions if there are two dates. but the requirement is to find the minimum date where 3 events fit into a 180 day window and grouped by the GROUP_ID.

The test data is:

DROP TABLE #EVENT_COUNT
CREATE TABLE #EVENT_COUNT
(
    [DATE]    DATE 
    ,[GROUP_ID]    INT 

);

INSERT INTO #EVENT_COUNT
SELECT '2011-01-01',1
UNION ALL SELECT '2011-02-01',1 UNION ALL SELECT '2011-03-01',1
UNION ALL SELECT '2011-04-01',1 UNION ALL SELECT '2011-05-01',1
UNION ALL SELECT '2011-06-01',1 UNION ALL SELECT '2011-07-01',1
UNION ALL SELECT '2011-08-01',1 UNION ALL SELECT '2011-09-01',1
UNION ALL SELECT '2011-10-01',1 UNION ALL SELECT '2011-11-01',1
UNION ALL SELECT '2011-12-01',2 UNION ALL SELECT '2012-01-01',2
UNION ALL SELECT '2012-02-01',2 UNION ALL SELECT '2012-03-01',2
UNION ALL SELECT '2012-04-01',2 UNION ALL SELECT '2012-05-01',2
UNION ALL SELECT '2012-06-01',2 UNION ALL SELECT '2012-07-01',2
UNION ALL SELECT '2012-08-01',2 UNION ALL SELECT '2012-09-01',2
UNION ALL SELECT '2012-10-01',2 UNION ALL SELECT '2012-11-01',2
UNION ALL SELECT '2012-12-01',2;

SELECT * FROM #EVENT_COUNT;

This is the table that is created:

 DATE       |GROUP_ID
    ---------------------
    2011-01-01  |1<<This date
    2011-02-01  |1
    2011-03-01  |1
    2011-04-01  |1
    2011-05-01  |1
    2011-06-01  |1
    2011-07-01  |1
    2011-08-01  |1
    2011-09-01  |1
    2011-10-01  |1
    2011-11-01  |1
    2011-12-01  |2 << This date
    2012-01-01  |2
    2012-02-01  |2
    2012-03-01  |2
    2012-04-01  |2
    2012-05-01  |2
    2012-06-01  |2
    2012-07-01  |2
    2012-08-01  |2
    2012-09-01  |2
    2012-10-01  |2
    2012-11-01  |2
    2012-12-01  |2

The result that I need is the two dates in bold grouped by GROUP_ID:

DATE        |GROUP_ID
---------------------
2011-01-01  |1<<This date
2011-12-01  |2 << This date

Any help is much appreciated.

Answer 1

You can do this by using the LEAD function and looking at the second next date from the current date. If the difference is less than 180, it would satisfy your condition:

SELECT a.Group_ID,min(Date) as Date
FROM
(SELECT *,
       lead(date,2) over(partition by group_id order by date) as lag_date2 
FROM #EVENT_COUNT) a
WHERE DATEDIFF(day,date,ISNULL(lag_date2,'01Jan2100'))<=180
GROUP BY a.Group_ID

Hope this helps.