Apologize for title. Having trouble explaining this.

I am running into an issue, and I am not sure what the best approach is here. Please see table picture. In this data set I need to find the date prd1 date in the prd2 column. If found, I need to then look at the dense_rnk column minus 1 and grab the value in the VAL column.

For example: 4/10 was found and the dense_rnk is 4 minus 1 is 3. The value needed in the output column is Z because it corresponds to dense_rnk 3. Should be driven by the dates on prd1.

Is there an excel offset comparable function that I can use in oracle? I added the window function thinking it would help, but it made everything more complicated.

Is this even possible? I appreciate any help here.

CodePudding user response：

This is a 2-step lookup, so

1. create 1st lookup (dense_rnk, val) in cte_rnk_val
2. create 2nd lookup (id, prd2, rnk) in cte_prd2_rank
3. join all three together to get output

with cte_rnk_val as (
select dense_rnk as rnk, val from tab_secret group by 1,2),
cte_prd2_rank as (
select id, prd2, dense_rnk-1 as rnk from tab_secret group by 1,2,3)
select s.id,
       s.prd1,
       s.prd2,
       s.dense_rnk,
       s.val,
       r.val as output
  from tab_secret s
  join cte_prd2_rank p
    on s.id = p.id and s.prd1 = p.prd2
  join cte_rnk_val r
    on p.rnk = r.rnk;

Output:

id  |prd1      |prd2      |dense_rnk|val|output|
---- ---------- ---------- --------- --- ------ 
7865|2015-04-10|2013-02-21|        1|S  |Z     |
7865|2013-07-09|2013-02-21|        1|S  |S     |
7865|2013-07-09|2013-02-21|        1|S  |S     |
7865|2015-04-10|2013-02-21|        1|S  |Z     |
7865|2015-04-10|2013-07-09|        2|Z  |Z     |
7865|2013-07-09|2013-07-09|        2|Z  |S     |
7865|2013-07-09|2014-10-10|        3|Z  |S     |
7865|2015-04-10|2014-10-10|        3|Z  |Z     |
7865|2015-04-10|2015-04-10|        4|Z  |Z     |
7865|2013-07-09|2015-04-10|        4|Z  |S     |
7865|2013-07-09|2016-10-15|        5|R  |S     |
7865|2015-04-10|2016-10-15|        5|R  |Z     |
7865|2015-04-10|2021-10-16|        6|R  |Z     |
7865|2013-07-09|2021-10-16|        6|R  |S     |

Query was tested in MySQL.

CodePudding user response：

Try this. You might have to translate to Oracle, since this is formatted for SSMS.

SELECT e.Val
FROM (
    SELECT c.dense_rnk-1
    FROM (
        SELECT b.dense_rnk
        FROM data a INNER JOIN data b on a.prd1 = b.prd2 
    ) c
) d LEFT JOIN data e ON d.dense_rnk = e.dense_rnk

Do note that this solution is pretty awful. You'd probably be able to do better with the original tables rather than the provided result set.