Forum Stats

  • 3,757,252 Users
  • 2,251,213 Discussions
  • 7,869,779 Comments

Discussions

Rank function by Sorting function and create custom columns

User_0V525
User_0V525 Member Posts: 10 Green Ribbon

Hello All,

I have a requirement from the below Table

I need Count of Promoted and Not_promoted Managers with their last 3 phases for each of managers and sort them by ascending order of Manager ID. Result should look like the below one -

to explain the above one,

example - Manager_ID =10 he got promoted on ( 1st March, 15th March, 23rd March) , Not promoted on (None) , so Under promoted count would be 3 and Not Promoted count would be 0

Under First,Second and Third phase , these three are split accordingly. Similarly others too.

I tried to use Rank over function and tried but I'm unable to get desired output. Can someone please help me with the query to build result set please.


Appreciate your help in Advance


Best Answer

  • User_H3J7U
    User_H3J7U Member Posts: 460 Bronze Trophy
    edited Jul 27, 2021 6:09AM Accepted Answer
    with t (id, client_id, manager_id,status,prodate) as (
    select 1,1,10,'Promoted',date'2020-03-01' from dual union all
    select 2,2,11,'Not_Promoted',date'2020-03-01' from dual union all
    select 3,3,12,'Promoted',date'2020-03-01' from dual union all
    select 4,4,13,'Not_Promoted',date'2020-03-01' from dual union all
    select 5,1,10,'Promoted',date'2020-03-15' from dual union all
    select 6,2,11,'Promoted',date'2020-03-15' from dual union all
    select 7,3,12,'Promoted',date'2020-03-15' from dual union all
    select 8,2,12,'Promoted',date'2020-03-23' from dual union all
    select 9,3,10,'Promoted',date'2020-03-23' from dual union all
    select 10,4,13,'Not_Promoted',date'2020-03-23' from dual
    )
    select *
    from t match_recognize(
     partition by manager_id order by prodate
     measures
       count(pro.status) as cpro,
       count(notpro.status) as cnotpro,
        last(status,0) as first_s,
        last(status,1) as second_s,
        last(status,2) as third_s
     pattern ((pro|notpro)+)
     define
       pro as status = 'Promoted',
       notpro as status = 'Not_Promoted'
    );
    
    MANAGER_ID       CPRO    CNOTPRO FIRST_S      SECOND_S     THIRD_S    
    ---------- ---------- ---------- ------------ ------------ ------------
           10           3          0 Promoted     Promoted     Promoted   
           11           1          1 Promoted     Not_Promoted            
           12           3          0 Promoted     Promoted     Promoted   
           13           0          2 Not_Promoted Not_Promoted            
    


    User_0V525

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 460 Bronze Trophy
    edited Jul 27, 2021 6:09AM Accepted Answer
    with t (id, client_id, manager_id,status,prodate) as (
    select 1,1,10,'Promoted',date'2020-03-01' from dual union all
    select 2,2,11,'Not_Promoted',date'2020-03-01' from dual union all
    select 3,3,12,'Promoted',date'2020-03-01' from dual union all
    select 4,4,13,'Not_Promoted',date'2020-03-01' from dual union all
    select 5,1,10,'Promoted',date'2020-03-15' from dual union all
    select 6,2,11,'Promoted',date'2020-03-15' from dual union all
    select 7,3,12,'Promoted',date'2020-03-15' from dual union all
    select 8,2,12,'Promoted',date'2020-03-23' from dual union all
    select 9,3,10,'Promoted',date'2020-03-23' from dual union all
    select 10,4,13,'Not_Promoted',date'2020-03-23' from dual
    )
    select *
    from t match_recognize(
     partition by manager_id order by prodate
     measures
       count(pro.status) as cpro,
       count(notpro.status) as cnotpro,
        last(status,0) as first_s,
        last(status,1) as second_s,
        last(status,2) as third_s
     pattern ((pro|notpro)+)
     define
       pro as status = 'Promoted',
       notpro as status = 'Not_Promoted'
    );
    
    MANAGER_ID       CPRO    CNOTPRO FIRST_S      SECOND_S     THIRD_S    
    ---------- ---------- ---------- ------------ ------------ ------------
           10           3          0 Promoted     Promoted     Promoted   
           11           1          1 Promoted     Not_Promoted            
           12           3          0 Promoted     Promoted     Promoted   
           13           0          2 Not_Promoted Not_Promoted            
    


    User_0V525
  • User_0V525
    User_0V525 Member Posts: 10 Green Ribbon

    Thank you so much