Query to obtain cccurrences within a certain period of time

Question

I've been looking for and thinking of a solution to the following but I cant come up with it, and hope someone can help me.

I have the following table:

ClientId    RecordDateTime
--------    -------------------    
      1     2019-02-14 14:05:49
      1     2019-02-14 14:06:34
      1     2019-02-14 14:07:19
      1     2019-02-14 14:08:49
      1     2019-02-14 14:09:34
      1     2019-02-14 14:10:32
      1     2019-02-14 14:12:32
      1     2019-02-14 14:14:18
      1     2019-02-14 14:15:10

I need to count the amount of occurrences using a determined amount of minutes to split the occurrences. For example:

• I set the limit to 1 minutes
• First datetime is 14:05:49
• Second datetime is 14:06:34, which has less than 1 minute difference with the previous one (14:05:49), so I consider it belongs to the same occurrence.

• Third datetime is 14:07:19, which also has less than 1 minute difference with the previous one (14:06:34), so is still same occurrence

• Now it comes 14:08:49, which has a difference with the previous one of MORE than 1 minute, so I consider its a new occurence.

• Next 14:09:34, which has a difference of less than 1 minute with 14:08:49.

And so it goes.

At the end, the result I want is:

ClientId   Occurrence Start      Occurrence End
--------   -------------------   -------------------
       1   2019-02-14 14:05:49   2019-02-14 14:07:19
       1   2019-02-14 14:08:49   2019-02-14 14:10:32
       1   2019-02-14 14:12:32   2019-02-14 14:12:32
       1   2019-02-14 14:14:18   2019-02-14 14:15:10

This is just an example, but I have a lot of data, from multiple ClientIds. Is there way of doing this without using stored procedures and looping through each row?

Thanks in advance for any help

Answer 1

You need to do some grouping for it

CREATE TABLE Table1
    ([ClientId] int, [RecordDateTime] datetime)
;

INSERT INTO Table1
    ([ClientId], [RecordDateTime])
VALUES
    (1, '2019-02-14 14:05:49'),
    (1, '2019-02-14 14:06:34'),
    (1, '2019-02-14 14:07:19'),
    (1, '2019-02-14 14:08:49'),
    (1, '2019-02-14 14:09:34'),
    (1, '2019-02-14 14:10:32'),
    (1, '2019-02-14 14:12:32'),
    (1, '2019-02-14 14:14:18'),
    (1, '2019-02-14 14:15:10')
;




select
  ClientID
  ,min(RecordDateTime) as [Occurrence Start]
  ,max(RecordDateTime) as [Occurrence End]
from
(
select
  ClientID
  ,RecordDateTime
  ,sum(grp) over(PARTITION BY ClientID ORDER BY RecordDateTime) as sgrp
from
  (
   select
       ClientID
       ,RecordDateTime
       ,isnull(prevRecordDT,RecordDateTime) as prevRecordDT
      ,case when datediff(second,prevRecordDT,RecordDateTime) <=60 then 0 else 1 end as grp
  from
    (
    select
          ClientID, RecordDateTime,
          LAG(RecordDateTime,1,NULL)OVER(PARTITION BY ClientID ORDER BY RecordDateTime) as prevRecordDT
    from Table1  
    ) as s
  ) as g
) as a
group by ClientID,sgrp

output of it:

ClientID    Occurrence Start    Occurrence End
1   14/02/2019 14:05:49 14/02/2019 14:07:19
1   14/02/2019 14:08:49 14/02/2019 14:10:32
1   14/02/2019 14:12:32 14/02/2019 14:12:32
1   14/02/2019 14:14:18 14/02/2019 14:15:10

dbfiddle
source of inspiration

A short description:

select
      ClientID, RecordDateTime,
      LAG(RecordDateTime,1,NULL)OVER(PARTITION BY ClientID ORDER BY RecordDateTime) as prevRecordDT
from Table1 

key point here is the LAG function , to get the previous value

ClientID    RecordDateTime  prevRecordDT
1   14/02/2019 14:05:49 
1   14/02/2019 14:06:34 14/02/2019 14:05:49
1   14/02/2019 14:07:19 14/02/2019 14:06:34

Then, do a difference, in seconds , to test if it's in interval:
,case when datediff(second,prevRecordDT,RecordDateTime) <= 60 then 0 else 1 end as grp

select
       ClientID
       ,RecordDateTime
       ,isnull(prevRecordDT,RecordDateTime) as prevRecordDT
      ,case when datediff(second,prevRecordDT,RecordDateTime) <= 60 then 0 else 1 end as grp
  from
       (
          select
               ClientID, RecordDateTime,
               LAG(RecordDateTime,1,NULL)OVER(PARTITION BY ClientID ORDER BY RecordDateTime) as prevRecordDT
          from Table1  
        ) as s

ClientID    RecordDateTime  prevRecordDT    grp
1   14/02/2019 14:05:49 14/02/2019 14:05:49 1
1   14/02/2019 14:06:34 14/02/2019 14:05:49 0
1   14/02/2019 14:07:19 14/02/2019 14:06:34 0
1   14/02/2019 14:08:49 14/02/2019 14:07:19 1

After this, just do a sum base on grp column
,sum(grp) over(PARTITION BY ClientID ORDER BY RecordDateTime) as sgrp

select
  ClientID
  ,RecordDateTime
  ,sum(grp) over(PARTITION BY ClientID ORDER BY RecordDateTime) as sgrp
from
  (
   select
       ClientID
       ,RecordDateTime
       ,isnull(prevRecordDT,RecordDateTime) as prevRecordDT
      ,case when datediff(second,prevRecordDT,RecordDateTime) <= 60 then 0 else 1 end as grp
  from
    (
    select
          ClientID, RecordDateTime,
          LAG(RecordDateTime,1,NULL)OVER(PARTITION BY ClientID ORDER BY RecordDateTime) as prevRecordDT
    from Table1  
    ) as s
  ) as g

ClientID    RecordDateTime      sgrp
1         14/02/2019 14:05:49   1
1         14/02/2019 14:06:34   1
1         14/02/2019 14:07:19   1
1         14/02/2019 14:08:49   2
1         14/02/2019 14:09:34   2

And finally, do min and max grouping by ClientID and sgrp

Answer 2

For small and medium resultset , Recursive CTE is not bad and it is very simple.

In case my script is not working for any other sample data that means that Test case is not covered and please provide that sample data with output.

create table #temp(ClientId int,RecordDateTime Datetime)
insert into #temp values
 (1,'2019-02-14 14:05:49')
,(1,'2019-02-14 14:06:34')
,(1,'2019-02-14 14:07:19')
,(1,'2019-02-14 14:08:49')
,(1,'2019-02-14 14:09:34')
,(1,'2019-02-14 14:10:32')
,(1,'2019-02-14 14:12:32')
,(1,'2019-02-14 14:14:18')
,(1,'2019-02-14 14:15:10')



    DECLARE @LimitSec int=60

 ;

WITH CTE1
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY ClientId ORDER BY RecordDateTime
            ) rn
    FROM #temp
    )
    ,Cte2
AS (
    SELECT clientid
        ,RecordDateTime
        ,rn AS grp
        ,rn
    FROM cte1
    WHERE rn = 1

    UNION ALL

    SELECT c.clientid
        ,c.RecordDateTime
        ,CASE 
            WHEN datediff(SECOND, c1.RecordDateTime, c.RecordDateTime) <= @LimitSec
                THEN c1.grp
            ELSE c1.grp + 1
            END
        ,c.rn
    FROM cte1 c
    INNER JOIN cte2 c1 ON c.clientid = c1.clientid
        AND c.rn = c1.rn + 1
    )
SELECT clientid
    ,min(RecordDateTime) AS [Occurence Start]
    ,max(RecordDateTime) AS [Occurence End]
FROM cte2
GROUP BY clientid
    ,grp

DROP TABLE #temp