CODEWITHSUNDEEP
oracle-databasehierarchical-data
Shantanu
Shantanu

Reputation: 867

How to use recursive function for product transition?

In Oracle SQL, I have a product transition table named kit_transition_mapping that is a slowly changing dimension and an orders table. For an order_no based on the order_date, I need to utilize the kit_transition_mapping table to get to the right kit.

For e.g.,

Given the above scenario and the kit_component table that breaks down a kit to the component level. How to write a query to get the expected result?

kit_transition_mapping

old_kit new_kit valid_from valid_to
ABC ABD 01-Sep-24 30-Sep-24
ABD ABE 01-Oct-24 31-Oct-24
ABE ABF 01-Nov-24 30-Nov-24

orders

order_no kit order_date qty
100 ABC 15-Aug-24 1
101 ABC 15-Sep-24 1
102 ABC 15-Oct-24 1
103 ABC 15-Nov-24 1
104 ABD 15-Oct-24 1
105 ABE 15-Nov-24 1
106 ABE 15-Oct-24 1
107 DEX 01-Dec-24 1

kit_component

kit comp qty
ABC A 1
ABC B 2
ABC C 3
ABD A 1
ABD B 2
ABD D 4
ABE A 1
ABE B 2
ABE E 5
ABF A 1
ABF B 2
ABF F 6
DEX D 2
DEX E 3
DEX X 4

expected_result

order_no order_date original_kit new_kit order_qty comp comp_qty
100 15-Aug-24 ABC ABC 1 A 1
100 15-Aug-24 ABC ABC 1 B 2
100 15-Aug-24 ABC ABC 1 C 3
101 15-Sep-24 ABC ABD 1 A 1
101 15-Sep-24 ABC ABD 1 B 2
101 15-Sep-24 ABC ABD 1 D 4
102 15-Oct-24 ABC ABE 1 A 1
102 15-Oct-24 ABC ABE 1 B 2
102 15-Oct-24 ABC ABE 1 E 5
103 15-Nov-24 ABC ABF 1 A 1
103 15-Nov-24 ABC ABF 1 B 2
103 15-Nov-24 ABC ABF 1 F 6
104 15-Oct-24 ABD ABE 1 A 1
104 15-Oct-24 ABD ABE 1 B 2
104 15-Oct-24 ABD ABE 1 E 5
105 15-Nov-24 ABE ABF 1 A 1
105 15-Nov-24 ABE ABF 1 B 2
105 15-Nov-24 ABE ABF 1 F 6
106 15-Oct-24 ABE ABE 1 A 1
106 15-Oct-24 ABE ABE 1 B 2
106 15-Oct-24 ABE ABE 1 E 5
107 01-Dec-24 DEX DEX 1 D 2
107 01-Dec-24 DEX DEX 1 E 3
107 01-Dec-24 DEX DEX 1 X 4

Here is the CTEs for the above data:

WITH orders (order_no, kit, order_date, qty) AS (
    SELECT 100, 'ABC', DATE '2024-08-15', 1 FROM DUAL UNION ALL
    SELECT 101, 'ABC', DATE '2024-09-15', 1 FROM DUAL UNION ALL
    SELECT 102, 'ABC', DATE '2024-10-15', 1 FROM DUAL UNION ALL
    SELECT 103, 'ABC', DATE '2024-11-15', 1 FROM DUAL UNION ALL
    SELECT 104, 'ABD', DATE '2024-10-15', 1 FROM DUAL UNION ALL
    SELECT 105, 'ABE', DATE '2024-11-15', 1 FROM DUAL UNION ALL
    SELECT 106, 'ABE', DATE '2024-10-15', 1 FROM DUAL UNION ALL
    SELECT 107, 'DEX', DATE '2024-12-01', 1 FROM DUAL
),
kit_transition_mapping (old_kit, new_kit, valid_from, valid_to) AS (
    SELECT 'ABC', 'ABD', DATE '2024-09-01', DATE '2024-09-30' FROM DUAL UNION ALL
    SELECT 'ABD', 'ABE', DATE '2024-10-01', DATE '2024-10-31' FROM DUAL UNION ALL
    SELECT 'ABE', 'ABF', DATE '2024-11-01', DATE '2024-11-30' FROM DUAL
),
kit_component (kit, comp, qty) AS (
    SELECT 'ABC', 'A', 1 FROM DUAL UNION ALL
    SELECT 'ABC', 'B', 2 FROM DUAL UNION ALL
    SELECT 'ABC', 'C', 3 FROM DUAL UNION ALL
    SELECT 'ABD', 'A', 1 FROM DUAL UNION ALL
    SELECT 'ABD', 'B', 2 FROM DUAL UNION ALL
    SELECT 'ABD', 'D', 4 FROM DUAL UNION ALL
    SELECT 'ABE', 'A', 1 FROM DUAL UNION ALL
    SELECT 'ABE', 'B', 2 FROM DUAL UNION ALL
    SELECT 'ABE', 'E', 5 FROM DUAL UNION ALL
    SELECT 'ABF', 'A', 1 FROM DUAL UNION ALL
    SELECT 'ABF', 'B', 2 FROM DUAL UNION ALL
    SELECT 'ABF', 'F', 6 FROM DUAL UNION ALL
    SELECT 'DEX', 'D', 2 FROM DUAL UNION ALL
    SELECT 'DEX', 'E', 3 FROM DUAL UNION ALL
    SELECT 'DEX', 'X', 4 FROM DUAL
),
kit_resolution (order_no, original_kit, kit, order_date, order_qty) AS (
    SELECT ho.order_no, ho.kit AS original_kit, ho.kit, ho.order_date, ho.qty AS order_qty
    FROM orders ho
    UNION ALL
    SELECT kr.order_no, kr.original_kit, km.new_kit, kr.order_date, kr.order_qty
    FROM kit_resolution kr
    JOIN kit_transition_mapping km ON kr.kit = km.old_kit AND kr.order_date BETWEEN km.valid_from AND km.valid_to
)
SELECT 
    kr.order_no, 
    TO_CHAR(kr.order_date, 'DD-Mon-YY') AS order_date,
    kr.original_kit, 
    kr.kit AS new_kit, 
    kr.order_qty, 
    kc.comp, 
    kc.qty * kr.order_qty AS comp_qty
FROM kit_resolution kr
JOIN kit_component kc ON kr.kit = kc.kit
WHERE NOT EXISTS (
    SELECT 1 FROM kit_transition_mapping km
    WHERE kr.kit = km.old_kit AND kr.order_date BETWEEN km.valid_from AND km.valid_to
)
ORDER BY kr.order_no, kc.comp;

Upvotes: 1

Views: 43

Answers (1)

p3consulting
p3consulting

Reputation: 4640

Try with:

...
,
kit_resolution(old_kit, new_kit, valid_from, valid_to) AS (
    SELECT old_kit, new_kit, valid_from, valid_to 
    FROM kit_transition_mapping km

    UNION ALL
    
    SELECT km.old_kit, kr.new_kit, kr.valid_from, kr.valid_to
    FROM kit_resolution kr
    JOIN kit_transition_mapping km ON km.valid_to < kr.valid_from
        AND km.new_kit = kr.old_kit
)
SELECT ord.order_no, ord.order_date, ord.kit AS original_kit, 
    NVL(kr.new_kit, ord.kit) AS new_kit, ord.qty, kc.comp, kc.qty AS comp_qty
FROM orders ord
LEFT JOIN kit_resolution kr ON ord.order_date BETWEEN kr.valid_from AND kr.valid_to
    AND ord.kit = kr.old_kit
JOIN kit_component kc ON kc.kit = NVL(kr.new_kit, ord.kit)
;

The kit_transition contains all the pairs(old, new) for each possible date interval:

ABE ABF 01/11/24    30/11/24
ABD ABE 01/10/24    31/10/24
ABC ABD 01/09/24    30/09/24
ABD ABF 01/11/24    30/11/24
ABC ABE 01/10/24    31/10/24
ABC ABF 01/11/24    30/11/24

Final result:

100 15/08/24    ABC ABC 1   B   2
100 15/08/24    ABC ABC 1   A   1
100 15/08/24    ABC ABC 1   C   3
101 15/09/24    ABC ABD 1   A   1
101 15/09/24    ABC ABD 1   B   2
101 15/09/24    ABC ABD 1   D   4
102 15/10/24    ABC ABE 1   B   2
102 15/10/24    ABC ABE 1   A   1
102 15/10/24    ABC ABE 1   E   5
103 15/11/24    ABC ABF 1   B   2
103 15/11/24    ABC ABF 1   A   1
103 15/11/24    ABC ABF 1   F   6
104 15/10/24    ABD ABE 1   E   5
104 15/10/24    ABD ABE 1   A   1
104 15/10/24    ABD ABE 1   B   2
105 15/11/24    ABE ABF 1   B   2
105 15/11/24    ABE ABF 1   F   6
105 15/11/24    ABE ABF 1   A   1
106 15/10/24    ABE ABE 1   E   5
106 15/10/24    ABE ABE 1   A   1
106 15/10/24    ABE ABE 1   B   2
107 01/12/24    DEX DEX 1   D   2
107 01/12/24    DEX DEX 1   E   3
107 01/12/24    DEX DEX 1   X   4

Upvotes: 1

Related Questions