Assign an instance to rolling but resetting 10 second time intervals

Question

Test Data Setup:

create table #Data(ID INT IDENTITY PRIMARY KEY CLUSTERED, EventDateTime datetime, Instance int)

insert into #Data(EventDateTime)
values
('2018-10-16T01:37:23.173'),
('2018-10-16T01:37:31.447'),
('2018-10-16T01:37:36.577'),
('2018-10-16T01:37:45.457'),
('2018-10-16T01:37:48.860'),
('2018-10-16T01:38:06.407'),
('2018-10-16T01:38:11.030'),
('2018-10-16T01:38:15.470'),
('2018-10-16T01:38:19.133'),
('2018-10-16T01:38:27.830')

Desired Output (which I can get):

Did this using quirky update:

declare @Instance int = 1
declare @StartDate datetime
select top 1 @StartDate = EventDateTime from #Data order by id asc

update t
set @Instance = Instance = case when ABS(datediff(MILLISECOND,t.EventDateTime, @StartDate)) < 10000 then @Instance else @Instance+1 end
, @StartDate = case when ABS(datediff(MILLISECOND,t.EventDateTime, @StartDate)) < 10000 then @StartDate else EventDateTime end
from #Data t

This is what it's doing in this specific example and it's the logic required:

1. Check row 1, if it’s within 10 seconds of @StartDate (Yes) then keep both the @InstanceID and @StartDate the same and set Instance to @InstanceID
2. Check row 2, if it’s within 10 seconds of @StartDate (still the original one, so Yes) then keep both the @InstanceID and @StartDate the same and set Instance to @InstanceID
3. Check row 3, if it’s within 10 seconds of @StartDate (still the original one, so No) then increment @instanceID by one and set Instance to that, and reset @StartDate to the Transactiondate of this record
4. Check row 4, if it’s within 10 seconds of @StartDate (now the one from row 3, so Yes) then keep both the @InstanceID and @StartDate the same and set Instance to @InstanceID
5. Check row 5, if it’s within 10 seconds of @StartDate (still the one from row 3, so No) then increment @instanceID by one and set Instance to that, and reset @StartDate to the Transactiondate of this record
6. etc
7. etc

The Question:

This can be done using a while loop, a cursor, or a quirky update. Is there a truly set based method for doing this (i.e none of the mentioned methods)?

Answer 1

Recursive CTE query can be a solution. No updates required at all.

DECLARE @OffsetMsec int = 10000;
WITH d1 (ID, EventDateTime, IsStarted, StartDate)
AS (
    SELECT TOP 1 ID, EventDateTime, 1, EventDateTime FROM #Data
    UNION ALL
    SELECT d2.ID, d2.EventDateTime,
        CASE WHEN ABS(DATEDIFF(MILLISECOND, d1.StartDate, d2.EventDateTime)) < @OffsetMsec
            THEN 0 ELSE 1 END AS IsStarted,
        CASE WHEN ABS(DATEDIFF(MILLISECOND, d1.StartDate, d2.EventDateTime)) < @OffsetMsec
            THEN d1.StartDate ELSE d2.EventDateTime END AS StartDate
    FROM #Data d2 INNER JOIN d1 ON d2.ID = d1.ID + 1
),
d2 (ID, EventDateTime, Instance)
AS (
    SELECT ID, EventDateTime, IsStarted, StartDate, 
        (SELECT SUM(IsStarted) FROM d1 d11 WHERE d11.ID <= d1.ID) AS Instance
    FROM d1
)
SELECT * FROM d2

Result

ID          EventDateTime           Instance
----------- ----------------------- -----------
1           2018-10-16 01:37:23.173 1
2           2018-10-16 01:37:31.447 1
3           2018-10-16 01:37:36.577 2
4           2018-10-16 01:37:45.457 2
5           2018-10-16 01:37:48.860 3
6           2018-10-16 01:38:06.407 4
7           2018-10-16 01:38:11.030 4
8           2018-10-16 01:38:15.470 4
9           2018-10-16 01:38:19.133 5
10          2018-10-16 01:38:27.830 5