Help required on ranking logic [message #670400] |
Mon, 02 July 2018 23:13 |
ramya29p
Messages: 146 Registered: November 2007 Location: Chennai
|
Senior Member |
|
|
Hi ,
Need help on ranking logic.
Below is my table structure
Effective_date Buy_sell
01-Dec-15 BUY
30-Nov-15 SELL
27-Nov-15 SELL
26-Nov-15 BUY
25-Nov-15 BUY
24-Nov-15 BUY
I want to do the ranking & find latest_date based on the Latest Buy or sell action.
First Buy was on 24th Nov 15 and no change in buy_sell action upto 26th Nov 15. So the Latest_date as on 26th Nov is 24-Nov-15.
if there is change in Buy_sell action then the ranking also to be changed accordingly as shown in below output
I need the output as
Effective_date Buy_sell Rank Latest_Date
01-Dec-15 BUY 1 01-Dec-15
30-Nov-15 SELL 2 27-Nov-15
27-Nov-15 SELL 1 27-Nov-15
26-Nov-15 BUY 3 24-Nov-15
25-Nov-15 BUY 2 24-Nov-15
24-Nov-15 BUY 1 24-Nov-15
|
|
|
Re: Help required on ranking logic [message #670402 is a reply to message #670400] |
Tue, 03 July 2018 00:14 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
[Updated on: Tue, 03 July 2018 00:15] Report message to a moderator
|
|
|
|
Re: Help required on ranking logic [message #670405 is a reply to message #670403] |
Tue, 03 July 2018 00:59 |
ramya29p
Messages: 146 Registered: November 2007 Location: Chennai
|
Senior Member |
|
|
Table Script
create table test_rank (effective_date date, buy_sell varchar2(5));
Insert Script
insert into test_rank values(to_date('24-nov-2015','dd-mon-yyyy'),'BUY');
insert into test_rank values(to_date('25-nov-2015','dd-mon-yyyy'),'BUY');
insert into test_rank values(to_date('26-nov-2015','dd-mon-yyyy'),'BUY');
insert into test_rank values(to_date('27-nov-2015','dd-mon-yyyy'),'SELL');
insert into test_rank values(to_date('30-nov-2015','dd-mon-yyyy'),'SELL');
insert into test_rank values(to_date('01-dec-2015','dd-mon-yyyy'),'BUY');
|
|
|
Re: Help required on ranking logic [message #670407 is a reply to message #670400] |
Tue, 03 July 2018 05:05 |
|
shawaj
Messages: 89 Registered: January 2016
|
Member |
|
|
SELECT a.effective_date,
a.buy_sell,
a.rk rank,
(SELECT MIN(effective_date)
FROM test_rank
WHERE buy_sell =a.buy_sell
AND TRUNC(effective_date,'month')=TRUNC(a.effective_date,'month')
) Latest_date
FROM
(SELECT EFFECTIVE_DATE,
BUY_SELL,
dense_rank() over (partition BY TRUNC(effective_date,'month'),buy_sell order by effective_date,buy_sell ) rk
FROM test_rank
) a;
[Updated on: Tue, 03 July 2018 07:18] Report message to a moderator
|
|
|
Re: Help required on ranking logic [message #670410 is a reply to message #670405] |
Tue, 03 July 2018 07:26 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
12C match recognize solution:
select effective_date,
buy_sell,
rank,
latest_date
from test_rank
match_recognize(
partition by buy_sell
order by effective_date
measures count(*) as rank,
first(effective_date) as latest_date
all rows per match
pattern(up*)
define up as effective_date = first(effective_date)
or
effective_date - prev(
-- skip weekend
case effective_date - trunc(effective_date,'iw')
when 4 then effective_date + 2
else effective_date
end
) = 1
)
order by effective_date desc
/
EFFECTIVE BUY_S RANK LATEST_DA
--------- ----- ---------- ---------
01-DEC-15 BUY 1 01-DEC-15
30-NOV-15 SELL 2 27-NOV-15
27-NOV-15 SELL 1 27-NOV-15
26-NOV-15 BUY 3 24-NOV-15
25-NOV-15 BUY 2 24-NOV-15
24-NOV-15 BUY 1 24-NOV-15
6 rows selected.
SQL>
SY.
|
|
|
|