SQL query to fill multiple needs with partial quantities by using Oracle SQL window functions

Question

I would like to solve an abstract problem which raised multiple times in my SQL history. This abstract problem can better be realized if we imagine we're a sales company and talk about parts (products) we want to sell, which are therefor needed at different dates in different quantities. On the other hand we have our "fill possibilities", like e.g. quantities which come from stock, production, purchasing at different dates and quantities.

I would like to fill those needs ordered by the date needed and date available using only one query (no procedure, no temp tables).

As technical base you can assume to have two tables:

• NEED_TABLE, which lists multiple needs, date needed and quantities.
• FILL_TABLE, which lists multiple "fills", date available and quantities.

In my example there are two needs:

• Need A: we need partno 123 in qty 4 at date 01/02/2019
• Need B: we need partno 123 in qty 2 at date 06/02/2019

And we have two "fills" in different quantities:

• Fill X: we have partno 123 in qty 2 in a purchase order, available 01/01/2019
• Fill Y: we have partno 123 in qty 4 in a purchase order, available 06/01/2019

Result should be:

• I need partno 123 with qty 4 at 1/2/2019 ("Need A"), which is filled by a purchase order in qty 2 ("Fill X") and by another purchase order in qty 2 ("Fill Y" - partial).
• I need partno 123 with qty 2 at 6/2/2019 ("Need B"), which is filled by a purchase order in qty 2 ("Fill Y" - partial).

SQL query:

with
    NEED_TABLE
    as
        (select 'A' NEED_ID, 123 partno, to_date('01/02/2019', 'MM/DD/YYYY') DATE_NEEDED, 4 NEED_QTY from dual
         union all
         select 'B' NEED_ID, 123 partno, to_date('06/02/2019', 'MM/DD/YYYY') DATE_NEEDED, 2 NEED_QTY from dual),
    FILL_TABLE
    as
        (select 'X' FILL_ID, 123 partno, to_date('01/01/2019', 'MM/DD/YYYY') DATE_AVAILABLE, 2 FILL_QTY from dual
         union all
         select 'Y' FILL_ID, 123 partno, to_date('06/01/2019', 'MM/DD/YYYY') DATE_AVAILABLE, 4 FILL_QTY from dual)
select   NEED_TABLE.NEED_ID
       , NEED_TABLE.PARTNO
       , NEED_TABLE.DATE_NEEDED
       , NEED_TABLE.NEED_QTY
       , FILL_TABLE.FILL_ID
       , FILL_TABLE.DATE_AVAILABLE
       , FILL_TABLE.FILL_QTY
       /* all following is wrong/incomplete */
       , lag(need_QTY - fill_QTY, 1, need_QTY)
             over(
                 partition by NEED_ID
                 order by DATE_NEEDED, DATE_AVAILABLE) REAL_NEED_QTY
       , greatest(
               lag(need_QTY - fill_QTY, 1, need_QTY)
                   over(
                       partition by NEED_ID
                       order by DATE_NEEDED, DATE_AVAILABLE)
             - FILL_QTY
           , 0) LEFT_NEED_QTY
       , abs(
             least(
                   lag(need_QTY - fill_QTY, 1, need_QTY)
                       over(
                           partition by NEED_ID
                           order by DATE_NEEDED, DATE_AVAILABLE)
                 - FILL_QTY
               , 0)) LEFT_FILL_QTY
    from NEED_TABLE, FILL_TABLE
order by DATE_NEEDED, DATE_AVAILABLE;

If you check the result of that query, all seems to be fine for the first NEED_ID "A". But as it continues with NEED_ID "B", it doesn't remember that the FILL_IDs X and Y already have been decreased while filling need "A".

I expect a result like:

NEED_ID A is filled by FILL_ID X qty 2

NEED_ID A is filled by FILL_ID Y qty 2

(NEED_ID A is filled by FILL_ID X qty 0)

NEED_ID B is filled by FILL_ID Y qty 2

NEED_TABLE:

| NEED_ID | PARTNO | DATE_NEEDED | NEED_QTY |
|---------|--------|-------------|----------|
| A       | 123    | 01/02/2019  | 4        |
| B       | 123    | 06/02/2019  | 2        |

FILL_TABLE:

| FILL_ID | PARTNO | DATE_AVAILABLE | FILL_QTY |
|---------|--------|----------------|----------|
| X       | 123    | 01/01/2019     | 2        |
| Y       | 123    | 06/01/2019     | 4        |

Expected query result:

| NEED_ID | PARTNO | DATE_NEEDED | NEED_QTY | FILL_ID | DATE_AVAILABLE | FILL_QTY | ***REAL_FILL*** | "WHY?"                                                              |
|---------|--------|-------------|----------|---------|----------------|----------|-----------------|---------------------------------------------------------------------|
| A       | 123    | 01/02/2019  | 4        | X       | 01/01/2019     | 2        | 2               | A needs 4, gets partially filled by X by 2                          |
| A       | 123    | 01/02/2019  | 4        | Y       | 06/01/2019     | 4        | 2               | A still needs 2, gets completely filled by Y by 2                   |
| B       | 123    | 06/02/2019  | 2        | X       | 01/01/2019     | 2        | 0               | B needs 2, can't get filled by X, because A already used that qty   |
| B       | 123    | 06/02/2019  | 2        | Y       | 06/01/2019     | 4        | 2               | B still needs 2, gets completely filled by remaining qty of Y, by 2 |

Any help is very appreciated - thx!

Answer 1

Here is my try:

with 
  need(rn, nid, nq) as (
    select 1, 'A', 4 from dual union all 
    select 2, 'B', 2 from dual ),
  fill(rf, fid, fq) as (
    select 1, 'X', 2 from dual union all 
    select 2, 'Y', 4 from dual ),
  u as (
    select rn, nid, -nq nq, null rf, null fid, null fq from need
    union all select null, null, null, rf, fid, fq from fill),
  c (crn, cnid, cnq, crf, cfid, cfq, rest, amt) as (
    select rn, nid, nq, 0, fid, fq, nq, 0 from u where rn = 1
    union all 
    select nvl(rn, crn), nvl(nid, cnid), nvl(nq, cnq), 
           nvl(rf, crf), nvl(fid, cfid), nvl(fq, cfq), rest + nvl(nq, fq), 
           least(abs(rest), abs(nvl(nq, fq)))
      from c 
      join u on rest >= 0 and rn = crn + 1 
             or rest <  0 and rf = crf + 1 )
select cnid, cfid, amt from c where amt <> 0

I simplified data, but partno can be easily added in joins and partition by of proper row_numbers and dates are important only for ordering rows. If they have more meaning they can be added now, but let's start with something clearer.

How it works. need and fill are our data source. u is union of these tables with need and fill data in separate columns. This union is needed to make next query working.

c is recursive CTE which starts with first fill. It is our anchor. In the next step I add (in join) fill or need row depending on what we got in previous rest. If rest is lower than zero it means then we have to look for next fill row. If it is greater it means then we got surplus from fills and we can look for next need. At each step the amount of the transaction is counted which equals to lower value from previous rest and currently joined fill/need.

Finally I take amounts and both sides of transaction. Tested on some examples.

^demo

Answer 2

Maybe the following ideas will help: {1} write 2 views, which break down the NEED_QTY and FILL_QTY values into "atoms" ie the smallest available units. (If you just want one massive query eventually, you can incorporate the view code into the final query later). {2} JOIN the 2 views {3} use GROUP BY ROLLUP to get some usable output.

Views

-- NEED: required items, broken down into "atoms" ie smallest available quantities
create or replace view nvw
as
select
  need_id as nid
, partno
, need_qty as nqty
, date_needed as ndate
, single_item
, row_number() over ( order by need_id, partno, date_needed ) row_
from need_table N cross apply (
    select 1 as single_item from dual connect by level <= N.need_qty
) SN
;

-- FILL: available items, broken down into "atoms" ie smallest available quantities
create or replace view fvw
as
select
  fill_id as fid
, partno
, fill_qty as fqty
, date_available as fdate
, single_item
, row_number() over ( order by fill_id, partno, date_available ) row_
from fill_table F cross apply (
    select 1 as single_item from dual connect by level <= F.fill_qty
) SF
;

JOIN the views

select *
from (
  select fid, partno, fqty, single_item, row_ from fvw
) FV join (
  select nid, partno, nqty, single_item, row_ from nvw
) NV on FV.row_ = NV.row_ ;

-- result
FID  PARTNO  FQTY  SINGLE_ITEM  ROW_  NID  PARTNO  NQTY  SINGLE_ITEM  ROW_  
X    123     2     1            1     A    123     4     1            1     
X    123     2     1            2     A    123     4     1            2     
Y    123     4     1            3     A    123     4     1            3     
Y    123     4     1            4     A    123     4     1            4     
Y    123     4     1            5     B    123     2     1            5     
Y    123     4     1            6     B    123     2     1            6     

6 rows selected. 

GROUP BY ROLLUP

-- dates omitted for clarity
select  NV.nid, FV.fid, sum( FV.single_item ) real_fill
from (
  select fid, partno, fqty, single_item, row_ from fvw
) FV join (
  select nid, partno, nqty, single_item, row_ from nvw
) NV on FV.row_ = NV.row_
group by rollup ( NV.nid, FV.fid );

-- result
NID   FID   REAL_FILL  
A     X     2          
A     Y     2          
A     NULL  4          
B     Y     2          
B     NULL  2          
NULL  NULL  6

The views/queries have been written with the central problem ie "fill multiple needs with partial quantities" in mind. You can add more column names to the GROUP BY clause (eg the dates). Tested with Oracle 12c and 18c. DBfiddle here.

This concept can also be used for the scenario described in your comment (2 packs of 10 must be filled by items that are stored in 4 bins, containing 5 items each ) - see DBfiddle.