| Query reporting [message #651511] |
Thu, 19 May 2016 06:08  |
srinivas.k2005
Messages: 404
Registered: August 2006
|
Senior Member |
|
|
Create table x_inv
(
Inv_num Number,
Inv_date Date,
CURRENT_STATE Varchar2(20)
);
Insert into x_inv values (500,sysdate,'Review');
Insert into x_inv values (400,sysdate-1,'Paid');
Insert into x_inv values (300,sysdate-2,'Paid');
Insert into x_inv values (200,sysdate-3,'Review');
Insert into x_inv values (100,sysdate-4,'Paid');
Insert into x_inv values (50,sysdate-5,'Paid');
Insert into x_inv values (25,sysdate-6,'Review');
SQL> select * from x_inv order by Inv_date;
INV_NUM INV_DATE CURRENT_STATE
---------- ----------- --------------------
25 5/13/2016 4 Review
50 5/14/2016 4 Paid
100 5/15/2016 4 Paid
200 5/16/2016 4 Review
300 5/17/2016 4 Paid
400 5/18/2016 4 Paid
500 5/19/2016 4 Review
Requirement:
If I pass inv_num (ex: 25) as input to the result of above query, I should get next Inv_num(Ex : 200) as result( Result is based on Inv_date and current_state as "Review").
For example in above result,
If input for query (INV_NUM) is 25, Inv_date for Inv_num 25 is 5/13/2016.
The Next higher date with current_state of "Review" is 5/16/2016 and corresponding inv_num is 200.
So, the result should be 200
In the similar way, if we pass input(inv_num )to query as 300, result should be 500.
If we pass input(inv_num )to query as 200, result should be 500.
If we pass input(inv_num )to query as 50, result should be 200.
Please advise.
Regards,
SRK
|
|
|
|
|
|
| Re: Query reporting [message #651514 is a reply to message #651511] |
Thu, 19 May 2016 06:27  |
srinivas.k2005
Messages: 404
Registered: August 2006
|
Senior Member |
|
|
Oh!! sorry.
The result from query should be,
SQL> select * from x_inv order by Inv_date;
INV_NUM INV_DATE CURRENT_STATE
---------- ----------- --------------------
25 5/13/2016 Review
50 5/14/2016 Paid
100 5/15/2016 Paid
200 5/16/2016 Review
300 5/17/2016 Paid
400 5/18/2016 Paid
500 5/19/2016 Review
[mod-edit: code tags added by bb]
[Updated on: Thu, 19 May 2016 13:57] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Query reporting [message #651535 is a reply to message #651514] |
Thu, 19 May 2016 14:18 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Just add a partition clause to the method that Michel previously provided on your other thread, as demonstrated below.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM x_inv ORDER BY inv_date
2 /
INV_NUM INV_DATE CURRENT_STATE
---------- --------------- --------------------
25 Fri 13-May-2016 Review
50 Sat 14-May-2016 Paid
100 Sun 15-May-2016 Paid
200 Mon 16-May-2016 Review
300 Tue 17-May-2016 Paid
400 Wed 18-May-2016 Paid
500 Thu 19-May-2016 Review
7 rows selected.
SCOTT@orcl_12.1.0.2.0> with
2 data as (
3 select inv_num, inv_date, current_state,
4 lag(inv_num) over (partition by current_state order by inv_date) prev_num
5 from x_inv
6 )
7 select inv_num, inv_date, current_state
8 from data
9 where prev_num = &prev_num
10 /
Enter value for prev_num: 25
old 9: where prev_num = &prev_num
new 9: where prev_num = 25
INV_NUM INV_DATE CURRENT_STATE
---------- --------------- --------------------
200 Mon 16-May-2016 Review
1 row selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
