Getting the Available Ranges from Two Tables

In this article, we will explore how to create a view that returns the availability ranges of each item_id based on additions and consumptions in two tables. We will use Oracle SQL to achieve this.

Introduction

We have two tables, A and B, in an Oracle database that manage a warehouse. Both tables have the same columns: Item_id, Start_num, and End_num. Table A contains the items added to the warehouse, while table B contains the consumptions of these items. The Start_num and End_num columns represent the range of items received or consumed, respectively.

For example, if we receive 300 items with numbering 101, 102, …, 400 for item_id 337, and we consume 240, 241, …, 300 and 301, 302, …, 400 of these items, the remaining numbering is 101, 102, …, 239. We want to create a view that returns this availability range for each item_id.

The Problem

To solve this problem, we need to merge the consumptions into a single table, find the subtracted intervals from intersections with the additions, and then add the original additions not having any intersection with the consumptions.

Solution

We will use Oracle SQL’s Common Table Expressions (CTEs) to achieve this. We will first create two CTEs: data_add for the additions and data_cons for 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
)

Next, we will create a third CTE, merged_cons, which merges the consumptions into a single table.

, 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)
    )
)

This CTE uses Oracle SQL’s MATCH_RECOGNIZE function to merge the consumptions. The PARTITION BY item_id clause groups the rows by the item_id column, and the ORDER BY start_num, end_num clause orders the rows by the start_num and end_num columns. The MEASURES FIRST(start_num) AS start_num, LAST(end_num) AS end_num clause calculates the first start_num and last end_num for each group.

The PATTERN(merged* strt) clause defines a pattern to match: merged. This pattern matches any row that comes after a previous row with the same item_id, where the next start_num is greater than or equal to the current end_num.

Finally, we will create a fourth CTE, intersections, which finds the subtracted intervals from intersections with the additions.

, intersections(id, item_id, start_before, end_before, start_after, end_after) AS (
    SELECT a.id, a.item_id, 
        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)
)

This CTE joins the data_add and merged_cons CTEs on the item_id column. It then calculates the start_before, end_before, start_after, and end_after values for each intersection.

The Solution

Finally, we will create a query that returns the availability ranges for each item_id.

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);

This query uses the UNION ALL operator to combine three parts:

• The first part selects rows from the intersections CTE where start_before IS NOT NULL.
• The second part selects rows from the intersections CTE where start_after IS NOT NULL.
• The third part selects rows from the data_add table that do not exist in the intersections CTE.

The result is a view that returns the availability ranges for each item_id.

Example Use Case

Suppose we have the following data:

Table A (additions):

Table B (consumptions):

The query would return:

This result shows the availability ranges for each item_id.

Last modified on 2025-01-15