Solved

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

Posted on 2011-03-07
11
763 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:lkrishna
11 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35059057
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;

0
 

Author Comment

by:lkrishna
ID: 35059200
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
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
ID: 35059252
See if this helps.

It gets easier to read if from_date is actually a date and not a varchar2 column.
drop table tab1 purge;
create table tab1 (from_date varchar2(20), ROI number);

insert into tab1 values('1.4.2010',16.5);
insert into tab1 values('29.5.2010',15.75);
insert into tab1 values('10.9.2010',16.25);
commit;

select from_date, to_char(
	to_date(
		lead(from_date) over(order by to_date(from_date,'DD.MM.YYYY'))
	,'DD.MM.YYYY')-1,'DD.MM.YYYY'),
	roi
from tab1;

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 167 total points
ID: 35060353
Not sure what you want as output, but see attached as general guideline.
query.txt
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35060384
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.
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 166 total points
ID: 35067914
In CR, you can use Next ({table.From_date}) to get the value from the next record, but you may need to add some restrictions to that.  For example, if the report is reading sets of records that are grouped/sorted by account, on the last record for one account, Next will give you the first From_date for the next account, which is presumably not what you'd want.  Then the question would be, what should "to date" be when there is no next record for an account?

 James
0
 

Author Comment

by:lkrishna
ID: 35308160
All the comments are useful and I was able to get the results successfully
0
 

Author Comment

by:lkrishna
ID: 35308167
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Wrong number of values in the INTO list of a FETCH statement 16 67
MULTIPLE DATE QUERY 15 79
PL SQL Search Across Columns 4 38
SQL2016 to ORACLE11G linked-server 6 15
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question