I have a table with several index-dates and I need to build groups of rows with maximum interval between first and last row not exceeding a specific value.
A reproducible example looks like this:
create myTable (id CHAR(1), rownr INTEGER, index DATE);
insert into myTable values ('A', 1, '2018-01-01');
insert into myTable values ('A', 2, '2018-08-01');
insert into myTable values ('A', 3, '2019-03-04');
insert into myTable values ('A', 4, '2019-09-09');
insert into myTable values ('A', 5, '2020-02-15');
insert into myTable values ('B', 1, '2020-12-31');
insert into myTable values ('B', 2, '2021-03-04');
insert into myTable values ('B', 3, '2022-01-01');
insert into myTable values ('B', 4, '2022-02-20');
I need a new Variable index_grp where the index date of all rows of the group is within 1 year. First row which index-date is farther then 1 year from current minimum index-date starts a new group (window). The desired result looks like this:
I tried windows moving functions:
select id, rownr, index
, min(index) over(partition by id order by index range between interval '1' year preceding and current row) as index_grp
from myTable;
but obviously index_grp of row A3 would be 2018-08-01 so the windows would overlap.
In Teradata-SQL there is a neat little addon to the analytical function, called "reset when", to start a new window when a given condition is met.
However, I need a ANSI-SQL solution.