-2

I have a question about rolling join in Oracle or Python pandas.

I have a table for list price , date of year, Material

I have another table for which is like the detail table and has Quote price, date of year , material, compnay and so on...

I want to get the list price in the second table for the material which is latest in the first table.

Basically i want to compare the quote price for every material and the given list price at that date or closest before.

Could someone help, i have heard about Rolling joins but not sure how to do that in oracle or in Pandas

Love Tyagi
  • 37
  • 5
  • 1
    Please read [how to make good reproducible pandas examples](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) and edit your post correspondingly. – MaxU - stand with Ukraine Jul 21 '17 at 17:15
  • 1
    You have asked four questions prior to this one and haven't accepted any of the useful answers. I don't believe you are understanding how this site works. Please read [**WhatToDoWhenSomeoneAnswers**](https://stackoverflow.com/help/someone-answers) – piRSquared Jul 21 '17 at 17:24

1 Answers1

0

The outcome you're asking for is ambiguous (which list(s) do you want the latest from?), but here's a couple of ideas:

The stupid way to do it in Oracle: Extract Latest dates from one or the other table, then do the join:

select material, company, ..., quote_price, quote_date,
       table1_latest.max_list_date, table1_latest.list_price 
from detail_table

left outer join

(select max(date) max_list_date, list_price, material from table1
 group by list_price, material) table1_latest

 ON detail_table.material = table1_latest.material

EDIT: Do a "full" join, then find the difference between the dates, use the smallest difference

select min(abs(days_diff)) min_days_diff, material, list_price,
       list_date, quote_date as closest_quote_date, quote_price as closest_quote_price
       from
       (select list_table.material, list_table.list_price,
               list_table.list_date, quote_table.quote_price,
               quote_table.quote_date, 
               (quote_table.quote_date-list_table.list_date) days_diff
        from list_table
        left outer join
        quote_table
        on list_table.material = quote_table.material) joined_table
group by material, list_price, list_date, quote_date, quote_price

The Left Outer Join fetches all of the available quotes for each list price. As long as the two dates are Oracle Date types, you can do the subtraction; it will yield Days difference.

cowbert
  • 3,212
  • 2
  • 25
  • 34
  • No i want the data which is closed to a particular date in detail table. – Love Tyagi Jul 21 '17 at 18:05
  • Its like my detail table has some price for a product on 21st july , so i want to see, what is the price other table on or closest before to that data and put it along side my table – Love Tyagi Jul 21 '17 at 18:06