Find events that happened within any seven days period

Transact-SQL
1 
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.
2
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

3

This may be more generic:

-- *** Test Data ***
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
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;
4

Thanks much

5

Thanks much it worked :slight_smile: