CODEWITHSUNDEEP
sqlsql-serverfunction
Oded Dror
Oded Dror

Reputation: 39

Dynamic Time Interval Gap

Please take a look at this code and tell me why it doesn't produce the expected result the result should be finding 15 min gap (dynamic) and starting from the point where the gap was found

Here is the code (it should return 5 records and 15 min gap)

drop table if exists example

CREATE TABLE example(
       [ID] [varchar](9) NULL,
       [DTTM] [datetime] NULL,
       [RN] Int null,
       [COUNTFLAG] int
) ON [PRIMARY] 

Insert into [example] (ID,DTTM) values 
('123456789','2017-10-05 08:00:00.000'), -- Expected Result
('123456789','2017-10-05 08:05:00.000'), 
('123456789','2017-10-05 08:07:00.000'),
('123456789','2017-10-05 08:15:00.000'), -- Expected Result
('123456789','2017-10-05 08:25:00.000'),
('123456789','2017-10-05 10:12:00.000') , -- Expected Result
('123456789','2017-10-05 10:26:00.000'),
('123456789','2017-10-05 10:32:00.000'),  -- Expected Result
('123456789','2017-10-05 10:33:00.000'),
('123456789','2017-10-05 10:34:00.000'),
('123456789','2017-10-05 10:35:00.000'),
('123456789','2017-10-05 10:36:00.000'),
('123456789','2017-10-05 10:37:00.000'),
('123456789','2017-10-05 10:38:00.000'),
('123456789','2017-10-05 10:39:00.000'),
('123456789','2017-10-05 10:40:00.000'),
('123456789','2017-10-05 10:41:00.000'),
('123456789','2017-10-05 10:42:00.000'),
('123456789','2017-10-05 10:43:00.000'),
('123456789','2017-10-05 10:44:00.000'),
('123456789','2017-10-05 10:45:00.000'),
('123456789','2017-10-05 10:46:00.000'),
('123456789','2017-10-05 10:47:00.000'),  -- Expected Result
('123456789','2017-10-05 10:48:00.000')



WITH Step1 AS (
 select ID, DTTM ,  NextVal
 FROM example A 
 OUTER APPLY (
  SELECT TOP(1) DTTM AS NextVal 
  FROM example AS B 
  WHERE dateadd(minute,15,(A.DTTM)) <= B.DTTM
   AND A.ID = B.ID
  ORDER BY DTTM) AS B
)
SELECT  A.ID, min(a.dttm) As MinDate, min(B.DTTM) AS MaxDate
FROM Step1 AS A
JOIN Step1 AS B ON A.NextVal = B.DTTM AND A.ID = B.ID
group by A.ID, b.NextVal
ORDER BY min(a.dttm);

Upvotes: 1

Views: 81

Answers (1)

kirchner
kirchner

Reputation: 974

It's not clear from your explanation if you can have multiple IDs, but since you GROUP BY on ID I believe you can have. Then some recursive CTE like this one will do the job:

WITH times AS (
    SELECT e.ID, MIN(e.DTTM) AS DTTM
    FROM example e
    GROUP BY e.ID

    UNION ALL

    SELECT r.ID, r.DTTM
    FROM (
        SELECT e.ID, e.DTTM,
            ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY e.DTTM) AS rn
        FROM example e
        INNER JOIN times t
            ON e.ID = t.ID AND e.DTTM >= DATEADD(mi, 15, t.DTTM)
    ) r
    WHERE r.rn = 1
)

SELECT *
FROM times
ORDER BY ID, DTTM
;

The query grabs the first time for each ID (the first SELECT, before the UNION ALL) and then recurses, finding the next row for each ID which is at least 15 minutes appart.

It is desirable to have a index on (ID, DTTM). Do you have a large table of data to try it and check if it produces the correct result and also performs well? The SELECT with GROUP BY at the top will cause a scan, but maybe it will be acceptable. If not let us know.

Upvotes: 2

Related Questions