none
Time Interval RRS feed

  • Question

  • 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 with 15 min gap - see expected results as the code)

    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);

    Thanks, Oded Dror

    Monday, February 5, 2018 10:59 PM

Answers

  • 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
    where (exists (SELECT 1 FROM Step1 As FilterA WHERE FilterA.NextVal = A.DTTM)
    OR NOT EXISTS (SELECT 1 FROM Step1  As FilterB WHERE FilterB.DTTM < A.DTTM))
    group by A.ID, b.NextVal

    ORDER BY min(a.dttm);
    • Edited by NereNull Monday, February 5, 2018 11:14 PM
    • Marked as answer by odeddror Tuesday, February 6, 2018 1:09 AM
    Monday, February 5, 2018 11:14 PM

All replies

  • 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
    where (exists (SELECT 1 FROM Step1 As FilterA WHERE FilterA.NextVal = A.DTTM)
    OR NOT EXISTS (SELECT 1 FROM Step1  As FilterB WHERE FilterB.DTTM < A.DTTM))
    group by A.ID, b.NextVal

    ORDER BY min(a.dttm);
    • Edited by NereNull Monday, February 5, 2018 11:14 PM
    • Marked as answer by odeddror Tuesday, February 6, 2018 1:09 AM
    Monday, February 5, 2018 11:14 PM
  • Thanks it works

    Thanks, Oded Dror

    Tuesday, February 6, 2018 1:09 AM