CODEWITHSUNDEEP
sqloracle-databasegroup-bywindow-functions
user1751356
user1751356

Reputation: 615

oracle select the rows using analytical conditions

I have a table with the data as below 

id  start_dt   cance1_dt     record_eff_dt  latest_row_flag 

1   null        null           01/01/2018       N
1   01/02/2018  01/02/2018     01/02/2018       N
1   01/03/2018  null           01/03/2018       Y     
2   null        01/04/2018     01/04/2018       Y     
3   01/05/2018  null           01/05/2018       N
3   null        01/06/2018     01/06/2018       Y

I have to rank the rows by grouping the rows with same id (partition by id) using the below conditions

condition 1 case when start_dt is not null AND cancel_dt is null and latest_row_flag = 'Y' then rank is 1.

condition 2 case when cancel_dt is null and latest_row_flag = 'Y' then scan all the rows for that same id and see if there is ever a row with start_dt is not null and cancel_dt is null then rank it as 2 else 3.

condition 3 else rank all other cases with 3

I'm struggling to come up with the code for condition 2, where i have to look through all the previous rows to see if there is ever such a case. Please help.

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Hmmm . . . the condition just depends on window functions:

select t.*,
       (case when start_dt is not null and cancel_dt is null and latest_row_flag = 'Y'
             then 1
             when cancel_dt is null and latest_row_flag = 'Y' and
                  sum(case when start_dt is not null and cancel_dt is null then 1 else 0 end) over (partition by id) > 0
             then 2
             else 3
        end) as ranking
from t;

Upvotes: 1

Related Questions