-1

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.

SQL_Novice
  • 61
  • 1
  • 2
  • 11
  • 1
    From the data you posted, `Select MIN(Date) Date, Group_ID From #Event_Count Group By Group_ID` would fit this. But I'm assuming that you need more to this, but this might get you started. – SS_DBA Feb 19 '20 at 19:38

1 Answers1

2

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.

CR7SMS
  • 2,520
  • 1
  • 5
  • 13
  • Thank you so much, exactly what I was looking for. I was trying to figure out the cumulative date difference using a window offset by 2. But this is exactly what i needed. – SQL_Novice Feb 19 '20 at 20:38