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 |