Join on PY date range in Oracle SQL-CodePudding

I have below two tables:

~ What I am looking to do: I want to append the Price from Table 2 (t2) to Table 1 (t1), by joining on Quantity and YYYY_MM. Each t2.price was active in a certain time range (t2.Price_Active_Date_From and t2.Price_Active_Date_To), and the t1.Order_YYYYMM should fall within this range. Year 9999 indicates that the price has no end date yet, and is therefore still active. I also want to find which price was active 1 year prior to the order date

So the result should look like:

What I have tried below so far, which works to get the Price_Active_At_Order, but I am not sure how to get Price_Active_PY when my date values are strings:

select distinct
t1.Product_NR,
t1.Customer,
t1.Quantity,
t2.Price as Price_Active_At_Order,
t1.Order_YYYYMM as Order_Date

from Table_1 t1
join Table_2 t2 on t1.Product_NR = t2.Product_NR
                and t1.Quantity = t2.Quantity
                and t1.Order_YYYYMM between t2.Price_Active_Date_From and t2.Price_Active_Date_To

CodePudding user response：

You can join twice on Table2 and use a LATERAL join with FETCH FIRST ROW ONLY (which would prevent getting multiple rows and needing to use DISTINCT):

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN LATERAL (
         SELECT price
         FROM   table2 t2
         WHERE  t1.product_nr = t2.product_nr
         AND    t1.quantity   = t2.quantity
         AND    t1.order_yyyymm BETWEEN t2.price_active_date_from
                                    AND t2.price_active_date_to
         ORDER BY t2.price ASC
         FETCH FIRST ROW ONLY
       ) t2
       ON (1 = 1)
       LEFT OUTER JOIN LATERAL (
         SELECT price AS price_ly
         FROM   table2 t2_ly
         WHERE  t1.product_nr = t2_ly.product_nr
         AND    t1.quantity   = t2_ly.quantity
         AND    TO_CHAR(ADD_MONTHS(TO_DATE(t1.order_yyyymm, 'YYYYMM'), -12), 'YYYYMM')
                                BETWEEN t2_ly.price_active_date_from
                                    AND t2_ly.price_active_date_to
         ORDER BY t2_ly.price ASC
         FETCH FIRST ROW ONLY
       ) t2_ly
       ON (1 = 1)

Which, for the sample data, outputs:

PRODUCT_NR CUSTOMER QUANTITY ORDER_YYYYMM PRICE PRICE_LY

10023 X 20 202104 250 300

10023 Y 10 202203 120 120

10334 X 1 202204 30 25

PRODUCT_NR	CUSTOMER	QUANTITY	ORDER_YYYYMM	PRICE	PRICE_LY
10023	X	20	202104	250	300
10023	Y	10	202203	120	120
10334	X	1	202204	30	25

db<>fiddle here

CodePudding user response：

You need to join the second table again with 1 year prior date to fetch the prior year's data -

select distinct
t1.Product_NR,
t1.Customer,
t1.Quantity,
t2.Price as Price_Active_At_Order,
t3.price as Price_Active_1Year_PriorOrder,
t1.Order_YYYYMM as Order_Date

from Table_1 t1
join Table_2 t2 on t1.Product_NR = t2.Product_NR
                and t1.Quantity = t2.Quantity
                and t1.Order_YYYYMM between t2.Price_Active_Date_From and t2.Price_Active_Date_To
join Table_2 t3 on t1.Product_NR = t3.Product_NR
                and t1.Quantity = t3.Quantity
                and TO_CHAR(ADD_MONTHS(TO_DATE(t1.Order_YYYYMM, 'YYYYMM'), -12), 'YYYYMM') between t3.Price_Active_Date_From and t3.Price_Active_Date_To