I'm new to TERADATA and have got a requirement as follows. Eventhough I have got the 'Procedure' solution for this requirement,
I'm interested in understanding whether a SQL based solution is possible rather than a 'Procedure'approach. If yes, please show me how.
Say my table "testtable" has records as given below
Then,I want to fetch records starting from the minimum startdate(starting record used as reference) for each id and fetch subsequent records for the particular id having
a gap of more than 30 days with the reference record.Once we fetch this next record,then that record will become the reference and then we look for
subsequent records which are having a duration of more than 30 days with this new reference record and so on.
So my output will be as follows
Yes it is possible with a two step approach
1. calculate per row which would be the first qualifying row if this row is valid
create volatile table vt_test3 ( id integer, dt date, min_next_dt date) no primary index on commit preserve rows
2. use a recusive query to get the rows you are looking for
WITH RECURSIVE tmp (ID , dt, min_next_dt,level) AS ( select id, dt, min_next_dt, 1 from vt_test3 qualify row_number() over (partition by id order by dt) = 1 union all select i.id, i.dt, i.min_next_dt, tmp.level + 1 from vt_test3 i join tmp on i.id = tmp.id and i.dt = tmp.min_next_dt ) select id, dt from tmp order by 1,2