Selecting DateTime Values with intervals greater than 45 days-CodePudding

I have a list of ID's and Dates ordered from oldest to newest. I'd like to select all ID's and Dates where the difference to the next previous date (with matching ID) is 45 days or greater.

202185, 2021-10-01 09:35:000
202185, 2021-10-02 09:36:000
202185, 2021-10-03 09:14:000
202185, 2022-02-01 09:22:000
202185, 2022-02-02 09:23:000
301133, 2021-11-01 09:35:000
301133, 2021-11-02 09:36:000
301133, 2021-11-03 09:14:000
301133, 2021-12-06 09:22:000
301133, 2022-01-25 09:23:000

SELECTION RETURNS:

202185, 2022-02-01 09:22:000
301133, 2022-01-25 09:23:000

Is there an efficient way to handle this using SQL Server?

Thanks!

CodePudding user response：

select id, date 
from (
    select id, [date], 
        datediff(day, lag([date]) over (partition by id order by [date]), [date]) as daydiff
    from [MyTable]
) t
where t.dayDiff >= 45
order by id, [date];

See it here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=69b5b976154d52a04333fbb1b771b3e1

CodePudding user response：

SELECT A.* FROM MyTable A 
WHERE A.date >  
( SELECT DATEADD(day, 45, MAX(b.date))
  FROM MyTable B
  WHERE B.ID = A.ID 
  AND   B.date < A.date
)

See it here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=038a3d02461f0ea54a14140e6be22370

CodePudding user response：

RHere is a solution using LAG() in a CTE.

CREATE TABLE idz(
        ID INT,
        DAT DATETIME);
INSERT INTO idz VALUES
(202185, '2021-10-01 09:35:000'),
(202185, '2021-10-02 09:36:000'),
(202185, '2021-10-03 09:14:000'),
(202185, '2022-02-01 09:22:000'),
(202185, '2022-02-02 09:23:000'),
(301133, '2021-11-01 09:35:000'),
(301133, '2021-11-02 09:36:000'),
(301133, '2021-11-03 09:14:000'),
(301133, '2021-12-06 09:22:000'),
(301133, '2022-01-25 09:23:000');

WITH i AS
( SELECT
  ID,
  DAT,
  LAG(DAT) OVER ( PARTITION BY ID
          ORDER BY DAT) DAT_1
FROM idz)
SELECT
  ID,
  DAT,
  DAT_1,
  DATEDIFF(DAY,DAT_1,DAT) DD
FROM i
WHERE DATEDIFF(DAY,DAT_1,DAT)>= 45

    ID | DAT                     | DAT_1                   |  DD
-----: | :---------------------- | :---------------------- | --:
202185 | 2022-02-01 09:22:00.000 | 2021-10-03 09:14:00.000 | 121
301133 | 2022-01-25 09:23:00.000 | 2021-12-06 09:22:00.000 |  50

db<>fiddle here

Credit due to shawnt00