SQL Server get count row interval next 5 seconds for every row with same criteria

Question

i have an example data :

I need to get the count of SNs for the next 5 seconds where the SNs are of the same type and the same selection

I tried using join but didn't quite get to the point. Is there a way to get this.

Answer 1

select *
from TestData t1
cross apply
(
  select
    count(*) [Count],
    isnull(string_agg(t2.SN, ','), '-') [Related SN]
  from TestData t2
  where
    t2.SN <> t1.SN
    and t2.Type = t1.Type
    and t2.Selection = t1.Selection
    and t2.DT >= t1.DT
    and datediff(second, t1.DT, t2.DT) < 5
) RelatedSN;