Calculating Valid_To date based on input date

[nagadastagirireddy]

Hi,
I have a requirement like from source I am getting valid from date for which I have to calculate valid to date.
The data is actually about currency exchange rate. I will data in a file and it is a monthly file, it will have four columns which are given below.

from_currency
to_currency
valid_from
exchange_rate

The problem I have is currency rate will change many times in a month and there will be multiple records for each currency with different valid from dates.
When there are multiple exchange rates in a month for same currency, then valid to date should be next validdate-1
Below is the sample data I am getting and expected result.

Source Data

CURR_FROM|CURR_TO|VALIDFROM|EXCHNG_RATE
CNH|USD|1-Jan-12|0.11
CNH|USD|5-Jan-12|0.8
CNH|USD|10-Jan-12|0.15
CNH|USD|15-Jan-12|0.14
CNH|USD|20-Jan-12|0.16


Expected Output

CURR_FROM|CURR_TO|VALIDFROM|VALIDTO|EXCHNG_RATE
CNH|USD|1-Jan-12|4-Jan-12|0.11
CNH|USD|5-Jan-12|9-Jan-12|0.8
CNH|USD|10-Jan-12|14-Jan-12|0.15
CNH|USD|15-Jan-12|19-Jan-12|0.14
CNH|USD|20-Jan-12|31-Dec-99|0.16


Please help me in solving this scenario.
Many thanks in advance

[ArndW]

Stage variables can be used to store values from the previous record, so in this case you will want to sort your data by currency and descending from_date.
The first record per currency will get a valid_to date of your high-date, then store that record's from_date in a stage variable and use that value, minus 1 day, for the next record's valid_to date.