getting data from table with nearest date

I want to create a query where any null values IN val COLUMN in table1
are replaced with value from table2 (VAL column) where the rules are:
join tabkle1 to table2 re: ID1 & ID2 and the value retuned from table2 (column VAL)
must be the row where the date in table2 is either equal or next date back
from that in table1 - see output table for desired result



Table1
Company       ID1      ID2     DATE          VAL
abc            123      1       01/03/2008     34
def            124      11      01/03/2009     NULL

Table2
ID1      ID2     DATE          VAL
123      1       01/03/2008     34
124      11      01/02/2004     50
124      11      01/02/2008     67
124      11      01/02/2011     12


SQL query required to produce ..............
output
cpy       ID1      ID2     DATE          VAL
abc      123      1       01/03/2008     34
def      124      11      01/03/2009     67 -- date returned from table1 and val 67
                                 -- is nearest date in table2 <= table1 date
philsivyerAsked:
Who is Participating?
 
sdstuberCommented:
SELECT company,
       id1,
       id2,
       d,
       NVL(val, val2)
  FROM (SELECT t1.*,
               t2.val val2,
               ROW_NUMBER() OVER (PARTITION BY t1.id1, t1.id2 ORDER BY t1.d - t2.d) rn
          FROM table1 t1, table2 t2
         WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2 AND t2.d <= t1.d)
 WHERE rn = 1
0
 
philsivyerAuthor Commented:
Thanks for this
0
 
philsivyerAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.