Getting the available ranges from two tables

I have two tables in oracle sql, A and B that manage a warehouse.

CREATE TABLE A (
    id NUMBER ,
    Item_ID NUMBER,
    Start_NUM NUMBER NOT NULL,
    End_NUM NUMBER NOT NULL
); 

CREATE TABLE B (
    id NUMBER ,
    Item_ID NUMBER,
    Start_NUM NUMBER NOT NULL,
    End_NUM NUMBER NOT NULL
); 

INSERT INTO A
(ID, ITEM_ID, START_NUM, END_NUM)
VALUES(1, 337, 101, 400);
INSERT INTO A
(ID, ITEM_ID, START_NUM, END_NUM)
VALUES(2, 337, 500, 800);
INSERT INTO A
(ID, ITEM_ID, START_NUM, END_NUM)
VALUES(3, 337, 801, 1200);
INSERT INTO A
(ID, ITEM_ID, START_NUM, END_NUM)
VALUES(4, 337, 1500, 1600);
INSERT INTO A
(ID, ITEM_ID, START_NUM, END_NUM)
VALUES(5, 337, 15000, 16000);

INSERT INTO B
(ID, ITEM_ID, START_NUM, END_NUM)
VALUES(1, 337, 240, 300);
INSERT INTO B
(ID, ITEM_ID, START_NUM, END_NUM)
VALUES(2, 337, 301, 400);
INSERT INTO B
(ID, ITEM_ID, START_NUM, END_NUM)
VALUES(3, 337, 850, 1100);

Both of them have the same columns Item_id, Start_num and End_num. Table A has the additions

of the items to the warehouse and table B the consumptions. Start_num and End_num represent the range of items received/consumed respectively.

For example start_num : 101 and end_num: 400 for item_id 337 means 300 items of id 337 numbering 101,102,103,...,400.

I want to create a view that returns the availability ranges of each item_id based on addition and consumption. For example i received items with numbering 101,102,103,104,...,400 and i consumed the 240,241,...,300 and 301,302,..,400 so the remaining numbering is 101,102,103,...,239.

The result i am looking for looks like that.

ID	ITEMID	AVAIL_START_NUM	AVAIL_END_NUM
1	337	101	239
2	337	500	800
3	337	801	849
4	337	1,101	1,200
5	337	1,500	1,600
6	337	15,000	16,000

Thank you in advance.

Solution

You have to merge the consumptions, then find the substracted intervals from intersections with the additions (you may have 2 new intervals) and then add the original additions not having any intersection with the consumptions.

WITH data_add(id, item_id, start_num, end_num) AS (
    SELECT 1, 337, 101, 400 FROM DUAL UNION ALL
    SELECT 2, 337, 500, 800 FROM DUAL UNION ALL
    SELECT 3, 337, 801, 1200 FROM DUAL UNION ALL
    SELECT 4, 337, 1500, 1600 FROM DUAL UNION ALL
    SELECT 5, 337, 15000, 16000 FROM DUAL -- UNION ALL
)
, data_cons(id, item_id, start_num, end_num) AS (
    SELECT 1, 337, 240, 300 FROM DUAL UNION ALL
    SELECT 2, 337, 301, 400 FROM DUAL UNION ALL
    SELECT 3, 337, 850, 1100 FROM DUAL -- UNION ALL
)
, merged_cons(item_id, start_num, end_num) AS (
    SELECT * FROM data_cons
    MATCH_RECOGNIZE (
        PARTITION BY item_id
        ORDER BY start_num, end_num
        MEASURES FIRST(start_num) AS start_num, LAST(end_num) AS end_num
        PATTERN( merged* strt )
        DEFINE
            merged AS MAX(end_num) + 1 >= NEXT(start_num)
    )
)
, intersections(id, item_id, start_before, end_before, start_after, end_after) AS (
    SELECT a.id, a.item_id, /*a.start_num AS add_start, a.end_num AS add_end, 
        b.start_num AS cons_start, b.end_num AS cons_end, */
        CASE WHEN a.start_num < b.start_num - 1 THEN a.start_num END AS start_before,
        CASE WHEN a.start_num < b.start_num - 1 THEN b.start_num - 1 END AS end_before,
        CASE WHEN b.end_num + 1 < a.end_num THEN b.end_num + 1 END AS start_after,
        CASE WHEN b.end_num + 1 < a.end_num THEN a.end_num END AS end_after
    FROM data_add a
    JOIN merged_cons b
        ON a.item_id = b.item_id AND LEAST(a.end_num, b.end_num) >= GREATEST(a.start_num, b.start_num)
)
SELECT item_id, start_before as start_num, end_before as end_num
FROM intersections WHERE start_before IS NOT NULL
UNION ALL
SELECT item_id, start_after as start_num, end_after as end_num
FROM intersections WHERE start_after IS NOT NULL
UNION ALL
SELECT item_id, start_num, end_num
FROM data_add d
WHERE NOT EXISTS(SELECT 1 FROM intersections i WHERE i.id = d.id)
;


337 101 239
337 801 849
337 1101    1200
337 500 800
337 1500    1600
337 15000   16000