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：

Two things to point out:

• Line 6 : te.Order_YYYYMM as Order_Date change to t1.Order_YYYYMM as Order_Date
• Line 13,14,15 : where clause is incomplete and unnecessary, remove it

Your code should work fine with the changes above.

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：

If you want to find a single row in table2 that can supply the complete quantity corresponding to a table1 row at the minimum price then, from Oracle 12, you can use a LATERAL join:

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN LATERAL (
         SELECT price,
                price_active_date_from,
                price_active_date_to
         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)

Which, for the sample data:

CREATE TABLE table1 (product_nr, customer, quantity, order_yyyymm) AS
SELECT 10023, 'X', 20, 202104 FROM DUAL UNION ALL
SELECT 10023, 'Y', 10, 202203 FROM DUAL UNION ALL
SELECT 10334, 'X',  1, 202204 FROM DUAL;

CREATE TABLE table2 (product_nr, quantity, price, price_active_date_from, price_active_date_to) AS
SELECT 10023,   1, 100, 202101, 999912 FROM DUAL UNION ALL
SELECT 10023,  10,  80, 201605, 202012 FROM DUAL UNION ALL
SELECT 10023,  10, 120, 202101, 202205 FROM DUAL UNION ALL
SELECT 10023,  20, 250, 202101, 999912 FROM DUAL UNION ALL
SELECT 10023, 100, 400, 202101, 202111 FROM DUAL UNION ALL
SELECT 10334,   1,  25, 202101, 202111 FROM DUAL UNION ALL
SELECT 10334,   1,  30, 202112, 202205 FROM DUAL;

Outputs:

PRODUCT_NR	CUSTOMER	QUANTITY	ORDER_YYYYMM	PRICE	PRICE_ACTIVE_DATE_FROM	PRICE_ACTIVE_DATE_TO
10023	X	20	202104	250	202101	999912
10023	Y	10	202203	120	202101	202205
10334	X	1	202204	30	202112	202205

If you want to find the row in table2 that can supply the quantity from the cumulative quantities between that row and the preceding rows within the date range ordered by ascending price then you can use:

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN LATERAL (
         SELECT price,
                price_active_date_from,
                price_active_date_to
         FROM   (
           SELECT price,
                  price_active_date_from,
                  price_active_date_to,
                  SUM(quantity) OVER (ORDER BY price ASC) AS quantity
           FROM   table2 t2
           WHERE  t1.product_nr = t2.product_nr
           AND    t1.order_yyyymm BETWEEN t2.price_active_date_from
                                      AND t2.price_active_date_to
         ) t2
         WHERE  t1.quantity   <= t2.quantity
         ORDER BY t2.price ASC
         FETCH FIRST ROW ONLY
       ) t2
       ON (1 = 1)

Which, for the sample data, has the same output.

db<>fiddle here