CODEWITHSUNDEEP
sqlwindow-functionsnetezzadbvisualizerdate-manipulation
Ezra
Ezra

Reputation: 21

SQL query for date intervals comparing non-adjacent rows?

I want to flag the first date in every window of at least 31 days for each ID unit in my data.

ROW  ID    INDEX_DATE
1    ABC   1/1/2019
2    ABC   1/7/2019
3    ABC   1/21/2019
4    ABC   2/2/2019
5    ABC   2/9/2019
6    ABC   3/6/2019
7    DEF   1/5/2019
8    DEF   2/1/2019
9    DEF   2/8/2019

The desired rows are 1, 4, 6, 7 and 9; these are either the first INDEX_DATE for the given ID, or they occur at least 31 days after the previously flagged INDEX_DATE. Every suggestion I have found uses LAG() or LEAD with window functions, but I could only get these to compare adjacent rows. Row 4, for example, needs to be compared to Row 1 in order to be identified as the first after a 31-day window has completed.

I tried the following:
Data 

DROP TABLE tTest IF EXISTS; 
CREATE TEMP TABLE tTest
(
ROWN    INT,
ID VARCHAR(3),
INDEX_DATE DATE
) ;
GO

INSERT INTO tTEST VALUES (1, 'ABC', '1/1/2019');
INSERT INTO tTEST VALUES (2, 'ABC', '1/7/2019');
INSERT INTO tTEST VALUES (3, 'ABC', '1/21/2019');
INSERT INTO tTEST VALUES (4, 'ABC', '2/2/2019');
INSERT INTO tTEST VALUES (5, 'ABC', '2/9/2019');
INSERT INTO tTEST VALUES (6, 'ABC', '3/6/2019');
INSERT INTO tTEST VALUES (7, 'DEF', '1/5/2019');
INSERT INTO tTEST VALUES (8, 'DEF', '2/1/2019');
INSERT INTO tTEST VALUES (9, 'DEF', '2/8/2019');
GO

Query: 

DROP TABLE TTEST2 IF EXISTS;
CREATE TEMP TABLE TTEST2 AS (
WITH 
RN_CTE(ROWN, ID, INDEX_DATE, RN) AS (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY INDEX_DATE)
        FROM tTEST),
MIN_CTE(ROWN, ID, INDEX_DATE, RN) AS (SELECT * FROM RN_CTE WHERE RN=1),
DIFF_CTE(ROWN,ID, INDEX_DATE, RN, DAY_DIFF) AS (
        SELECT RN.*, DATE(RN.INDEX_DATE + INTERVAL '30 DAYS') 
        FROM RN_CTE AS RN
                JOIN MIN_CTE AS MC ON RN.ID=MC.ID
        WHERE RN.RN=1
           OR RN.INDEX_DATE > MC.INDEX_DATE + INTERVAL '30 DAYS' ),
MIN_DIFF_CTE AS (
        SELECT ID, DAY_DIFF, MIN(ROWN) AS MIN_ROW
        FROM DIFF_CTE
        GROUP BY ID, DAY_DIFF)
SELECT T.*
FROM MIN_DIFF_CTE AS MDC
        JOIN tTEST AS T ON MDC.MIN_ROW = T.ROWN
ORDER BY ID, INDEX_DATE
);

Result: 

SELECT * FROM TTEST2 ORDER BY ID, INDEX_DATE;    
ROWN ID INDEX_DATE
1   ABC 2019-01-01
4   ABC 2019-02-02
5   ABC 2019-02-09
6   ABC 2019-03-06
7   DEF 2019-01-05
9   DEF 2019-02-08

Row 5 with INDEX_DATE = 2019-02-09 should not be in the output because it is less than 31 days after Row 4's INDEX_DATE.

Upvotes: 0

Views: 166

Answers (1)

SteveC
SteveC

Reputation: 6015

Something like this. The CTE's locate the unique window of the minimum ROW value for each ID transition and 31 days rolling too.

Data

drop table if exists #tTEST;
go
select * INTO #tTEST from (values 
(1, 'abc', '1/1/2019'),
(2, 'abc', '1/7/2019'),
(3, 'abc', '1/21/2019'),
(4, 'abc', '2/2/2019'),
(5, 'abc', '2/9/2019'),
(6, 'abc', '3/6/2019'),
(7, 'def', '1/5/2019'),
(8, 'def', '2/1/2019'),
(9, 'def', '2/8/2019')) V([ROW], ID, INDEX_DATE);

Query

;with
rn_cte([ROW], ID, INDEX_DATE, rn) as (
    select *, row_number() over (partition by ID order by INDEX_DATE)
    from #tTEST),
min_cte([ROW], ID, INDEX_DATE, rn) as (select * from rn_cte where rn=1),
diff_cte([ROW], ID, INDEX_DATE, rn, day_diff) as (
    select rn.*, datediff(d, mc.INDEX_DATE, rn.INDEX_DATE)/31
    from rn_cte rn
         join min_cte mc on rn.ID=mc.ID
    where rn.rn=1 
          or datediff(d, mc.INDEX_DATE, rn.INDEX_DATE)/31>0),
min_diff_cte as (
    select ID, day_diff, min([ROW]) min_row
    from diff_cte 
    group by ID, day_diff)
select t.*
from min_diff_cte mdc
     join #tTEST t on mdc.min_row=t.ROW
order by 1;

Output

ROW ID  INDEX_DATE
1   abc 1/1/2019
4   abc 2/2/2019
6   abc 3/6/2019
7   def 1/5/2019
9   def 2/8/2019

Upvotes: 1

Related Questions