# Find events that happened within any seven days period

``````Hi,

I have a table with several encounters tied to people and I need to find the total of all encounters that happened within any 7 days period and group them under a unique ID.

**Data available**:

PersonID      EncounterID            EventDate
______________________________
1                1                   2021-08-02
1                2                   2021-08-05
1                3                   2021-08-09
2                1                   2021-09-10
2                2                   2021-10-01
3                1                   2021-11-06
3                2                   2021-11-07
3                3                   2021-11-09
4                1                   2021-06-01

**Results Needed**: I need to group all encounters that happened within a 7 days period under a new column called eventID to be able to track how many encouters happened within each 7 days period for each PersonID.

PersonID      EventID         EncounterID        EventDate
________________________________________
1                A                  1                  2021-08-02
1                A                  2                  2021-08-05
1                A                  3                  2021-08-09
2                A                  1                  2021-09-10
2                B                  2                  2021-10-01
3                A                  1                  2021-11-06
3                A                  2                  2021-11-07
3                A                  3                  2021-11-09
4                A                  1                  2021-06-01

Thank you.``````
DDL

declare @t table (PersonID int, EncounterID int, EventDate date)

insert into @t values
(1,1,'2021-08-02')
,(1,2,'2021-08-05')
,(1,3,'2021-08-09')
,(2,1,'2021-09-10')
,(2,2,'2021-10-01')
,(3,1,'2021-11-06')
,(3,2,'2021-11-07')
,(3,3,'2021-11-09')
,(4,1,'2021-06-01')

Solution

select PersonID, EncounterID,
case when DateDiff(day, EventDate, nextEventDate) > 7 then 'B' else 'A' end as EventID
, EventDate
from (
select PersonID, EncounterID, EventDate, lead(EventDate,1) over (partition by PersonID order by PersonID, EventDate) as nextEventDate
from @t) t
order by 1, 2, 3

This may be more generic:

``````-- *** Test Data ***
GO
CREATE TABLE #t
(
PersonID int NOT NULL
,EncounterID int NOT NULL
,EventDate date NOT NULL
,PRIMARY KEY (PersonID, EventDate)
);
INSERT INTO #t
VALUES (1, 1, '2021-08-02')
,(1, 2, '2021-08-05')
,(1, 3, '2021-08-09')
,(2, 1, '2021-09-10')
,(2, 2, '2021-10-01')
,(3, 1, '2021-11-06')
,(3, 2, '2021-11-07')
,(3, 3, '2021-11-09')
,(4, 1, '2021-06-01');
-- *** End Test Data ***

WITH EventIds
AS
(
SELECT PersonID, EventDate
,ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY EventDate) AS rn
,1 AS EventId
FROM #t

UNION ALL

SELECT T.PersonID, T.EventDate
,ROW_NUMBER() OVER (PARTITION BY T.PersonID ORDER BY T.EventDate) AS rn
,E.EventId + 1
FROM #t T
JOIN EventIds E
ON T.PersonID = E.PersonID
-- 6 agrees with text but use 7 to get given results
WHERE T.EventDate > DATEADD(day, 6, E.EventDate)
)
,Ranges
AS
(
SELECT PersonID, EventDate AS EventDateStart
,ISNULL(LEAD(EventDate) OVER (PARTITION BY PersonID ORDER BY EventDate), '9999') AS EventDateEnd
,EventId
FROM EventIds
WHERE rn = 1
)
SELECT T.PersonID
,R.EventId
--,CHAR(64 + R.EventId) AS EventIDAlpha
,T.EncounterID
,T.EventDate
FROM #t T
JOIN Ranges R
ON T.PersonID = R.PersonID
AND T.EventDate >= R.EventDateStart
AND T.EventDate < R.EventDateEnd
ORDER BY PersonID, EventDate;``````

Thanks much

Thanks much it worked