Need SQL help: date ladder

Database
Tourist

Need SQL help: date ladder


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

ids startdate
--------------------
100 02-JAN-2014
100 27-JAN-2014
100 05-FEB-2014
100 15-FEB-2014
100 08-MAR-2014
200 09-APR-2014
200 10-APR-2014
200 12-MAY-2014

 

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

ids startdate
-------------------
100 02-JAN-2014
100 05-FEB-2014
100 08-MAR-2014
200 09-APR-2014
200 12-MAY-2014

1 REPLY
Supporter

Re: Need SQL help: date ladder

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