Need Help Update the interval using recursive or by any to make start date and end date in squence

Database

Need Help Update the interval using recursive or by any to make start date and end date in squence

Hello,  Can someone help me with a Recursive SQL problem.  I am new to teradata . 

 have been struggling for about 6 months trying to find a teradata query that will combine consecutive date spans

Scenario

From date To date
5/5/2015 5/8/2015
5/9/2015 5/12/2015
5/15/2015 5/20/2015
5/21/2015 5/25/2015
5/28/2015 1/1/9999

The above record doesn’t have the proper sequence between the start date and end it has to be updated

as below record  plz let me know if any query is used to update the sequece between start date and end date

Output Required
5/5/2015 5/8/2015
5/9/2015 5/14/2015
5/15/2015 5/20/2015
5/21/2015 5/27/2015
5/28/2015 1/1/9999

Vinoth

1 REPLY
Junior Contributor

Re: Need Help Update the interval using recursive or by any to make start date and end date in squence

Hi Vinoth,

you simply need to find then next row's from_date and subtract one day from it:

select from_date, to_date, 
max(from_date)
over (order by from_date, to_date
rows between 1 following and 1 following) - 1 as new_to_date
from tab
qualify to_date <> new_to_date

If you really need to update the table (of course test it before you run it):

update tgt 
from tab as tgt,
(
previous query
) as src
set to_date = src.new_to_date
where tgt.from_date = src.from_date
and tgt.to_date = src.to_date