asked on

sql query to fetch previous rows value - 1 in the next column

I have to create a loan account statement in the main report.
In the footnote I need to show the rate of interest changes during the period of account statement as below.

From_date To_date Rate of Interest
1.4.2010 28.5.2010 16.5%
29.5..2010 9.9.2010 15.75
10.9.2010 16.,25

In the above report 'From_date' and 'ROI' are fetched from the table.
Please help to fetch to_date with value= next(from_date) -1

slightwv (䄆 Netminder)

Are those dates stored as 'dates' or strings?

I'm also not sure what you mean by 'next(from_date)-1'

To add or subtract days from Oracle dates, just do it:

select to_date('1.4.2010','MM.DD.YYYY')-1 from dual;

lkrishna

ASKER

The table has only from_date and ROI fields, I need to fetch the to_date

For e.g. in the below report, to_date in the first row is equal to from_date of second row - 1

29.5.2010 -1,

I tried with self join, connect by prior...but yet to get a solution

From_date To_date Rate of Interest
1.4.2010 28.5.2010 16.5%
29.5..2010 9.9.2010 15.75
10.9.2010 16.,25

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

Isn't that pretty much what I posted?

>>The table has only from_date and ROI fields, I need to fetch the to_date

to_date isn't a real column. It is derived as 1 - the 'next' from date.

SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lkrishna

ASKER

All the comments are useful and I was able to get the results successfully

lkrishna

ASKER

all the expert comments are useful and I was able toget the required results. Regret for not having responded , due to work pressure in the office ,immediately could not respond.