Resetting minimum value after certain condition is met

23 Views Asked by At

In Teradata, I want to add a date column that gives me the minimum date which would reset after a certain condition is met. In this case the min date is reset when the difference >= 60.

Code initial table

CREATE VOLATILE TABLE tbl_testing (id INTEGER,event_date DATE) ON COMMIT PRESERVE ROWS;
INSERT INTO tbl_testing VALUES (1,'2022-07-02');
INSERT INTO tbl_testing VALUES (1,'2022-07-10');
INSERT INTO tbl_testing VALUES (1,'2022-07-29');
INSERT INTO tbl_testing VALUES (1,'2022-11-12');
INSERT INTO tbl_testing VALUES (1,'2022-11-17');
INSERT INTO tbl_testing VALUES (1,'2022-12-03');
INSERT INTO tbl_testing VALUES (1,'2022-12-07');
INSERT INTO tbl_testing VALUES (1,'2022-06-09');

The final table would look like this. Either min_date1 or min_date2 work for my purposes.

ID event_date Min_Date1 Min_Date2
1 7/2/2022 7/2/2022 7/2/2022
1 7/10/2022 7/2/2022 7/2/2022
1 7/29/2022 7/2/2022 7/2/2022
1 11/12/2022 7/2/2022 11/12/2022
1 11/17/2022 11/12/2022 11/12/2022
1 12/3/2022 11/12/2022 11/12/2022
1 6/9/2023 11/12/2022 6/9/2023

I tried using the following code but didn't get what I wanted.

SELECT
a.id,
a.event_date,
FIRST_VALUE(a.event_date)
 OVER
 (
  PARTITION BY a.id
  ORDER BY a.event_date
  RESET WHEN a.event_date - FIRST_VALUE(b.first_date) OVER(ORDER BY a.event_date) >= 60
 ) actual_result_date
FROM tbl_testing AS a
    LEFT JOIN
    (
        SELECT
        id,
        MIN(event_date) first_date
        FROM tbl_testing
        GROUP BY id
    ) AS b
        ON a.id = b.id AND a.event_date = b.first_date;
ID event_date expected_date actual_result_date
1 7/2/2022 7/2/2022 7/2/2022
1 7/10/2022 7/2/2022 7/2/2022
1 7/29/2022 7/2/2022 7/2/2022
1 11/12/2022 11/12/2022 11/12/2022
1 11/17/2022 11/12/2022 11/17/2022
1 12/3/2022 11/12/2022 12/3/2022
1 6/9/2023 6/9/2023 6/9/2023
0

There are 0 best solutions below