Home » SQL & PL/SQL » SQL & PL/SQL » Help required on ranking logic
Help required on ranking logic [message #670400] Mon, 02 July 2018 23:13 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 #670403 is a reply to message #670400] Tue, 03 July 2018 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Thu, 17 May 2018 09:11
Michel Cadot wrote on Mon, 14 May 2018 12:57

No feedback, no thank, no help above all when rules are ignored.

Re: Help required on ranking logic [message #670405 is a reply to message #670403] Tue, 03 July 2018 00:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: Help required on ranking logic [message #670419 is a reply to message #670410] Tue, 03 July 2018 23:11 Go to previous message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Thank you very much.
Previous Topic: How to get decimal value from string and convert
Next Topic: Stored Procedure / Deletion Conflict
Goto Forum:
  


Current Time: Fri Mar 29 08:56:59 CDT 2024