1

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
biguzs
  • 21
  • 4
  • The 60 days start at the first date after the threshold? This can't be done using OLAP, it requires either WITH RECURSIVE (ok, if the number of rows per id is small) or a loop in a Stored Procedure. – dnoeth Jun 11 '23 at 13:29
  • @dnoeth That was my fear. Thanks anyway! – biguzs Jun 11 '23 at 19:08

0 Answers0