Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Amit.

Asked: February 16, 2021 - 11:06 pm UTC

Last updated: February 19, 2021 - 1:37 pm UTC

Version: 12.0.1

Viewed 1000+ times

You Asked

Hi Chris and Connor,

I have two tables t1 - Stores warehouse, item, and percentage, t2 - Stores warehouse, item, order, line, quantity, and request date.

The hard code value 9 (will be as input to query) is the quantity that I want to assign to table t2 rows based on the percentage given in table t1.

I also need a new column called status which will show Y if an assignment is done and N if an assignment is not done, and new it should generate rows for the quantity which is leftover with status P i.e. pending unassigned quantity.

The assigned quantity should be in integer, in case of a tie on request date - we must give priority based on order and line. For the cases where full input quantity can’t be assigned the last row of table t2 can have less/more quantity.

I have attempted a solution but it doesn’t show the desired result. I have provided sample data as below.

DROP TABLE t1; 
CREATE TABLE t1 (
organization_code VARCHAR2(3),
item_number VARCHAR2(50),
share_percentage NUMBER);
INSERT INTO t1 (
SELECT 'W1', 'A', 40 FROM DUAL UNION ALL
SELECT 'W2', 'A', 10 FROM DUAL UNION ALL
SELECT 'W3', 'A', 10 FROM DUAL UNION ALL
SELECT 'W4', 'A', 10 FROM DUAL UNION ALL
SELECT 'W5', 'A', 10 FROM DUAL UNION ALL
SELECT 'W6', 'A', 10 FROM DUAL UNION ALL
SELECT 'W7', 'A', 10 FROM DUAL 
);

DROP TABLE t2; 
CREATE TABLE t2 (
organization_code VARCHAR2(3),
order_number NUMBER,
line_number NUMBER,
item_number VARCHAR2(50),
ordered_quantity NUMBER,
request_date DATE);

INSERT INTO t2 (
SELECT 'W1', 1, 1, 'A', 10, TIMESTAMP '2021-02-01 10:10:10' FROM DUAL UNION ALL
SELECT 'W2', 2, 1, 'A', 10, TIMESTAMP '2021-02-01 11:10:10' FROM DUAL UNION ALL
SELECT 'W3', 3, 1, 'A', 10, TIMESTAMP '2021-02-01 12:10:10' FROM DUAL UNION ALL
SELECT 'W4', 4, 1, 'A', 10, TIMESTAMP '2021-02-01 13:10:10' FROM DUAL UNION ALL
SELECT 'W5', 5, 1, 'A', 10, TIMESTAMP '2021-02-01 14:10:10' FROM DUAL UNION ALL
SELECT 'W6', 6, 1, 'A', 10, TIMESTAMP '2021-02-01 15:10:10' FROM DUAL UNION ALL
SELECT 'W7', 7, 1, 'A', 10, TIMESTAMP '2021-02-01 16:10:10' FROM DUAL
);

SELECT organization_code                                                                            AS organization_code
     , order_number                                                                                 AS order_number
     , line_number                                                                                  AS line_number
     , item_number                                                                                  AS item_number
     , to_share_percentage - LAG(to_share_percentage, 1, 0) OVER( PARTITION BY organization_code
                                                                             , item_number 
                                                                  ORDER BY request_date)            AS shared_percentage
     , 1 + LAG(to_share_percentage, 1, 0) OVER (PARTITION BY organization_code
                                                           , item_number 
                                                ORDER BY request_date)                              AS from_share_percent
     , to_share_percentage                                                                          AS to_share_percentage
  FROM ( SELECT t2.organization_code
              , t2.order_number
              , t2.line_number
              , t2.item_number
              , t2.ordered_quantity
              , t2.request_date
              , ROUND (9 * SUM(share_percentage) OVER( PARTITION BY t2.organization_code
                                                                    , t2.item_number 
                                                         ORDER BY t2.request_date) / 100)       AS to_share_percentage
         FROM t1  t1
            , t2  t2
        WHERE t1.organization_code = t2.organization_code
          AND t1.item_number = t2.item_number
       )
 ORDER BY 1; 


Desired result -

ORGANIZATION ORDER_NUMBER LINE_NUMBER ITEM_NUMBER SHARED_PERCENTAGE STATUS
W1                      1           1           A                 4 Y -- 40% of 9 is 3.6 so 4 is mapped to this row.
W1                      1           1           A                 6 N -- Order quantity was 10 but assigned is 40% of 9 so left over is this row.
W2                      2           1           A                 1 Y -- 10% of 9 is 0.9 so 1 is mapped to this row.
W2                      2           1           A                 9 N -- Order quantity was 10 but assigned is 10% of 9 so left over is this row.
W3                      3           1           A                 1 Y -- 10% of 9 is 0.9 so 1 is mapped to this row.
W3                      3           1           A                 9 N -- Order quantity was 10 but assigned is 10% of 9 so left over is this row.
W4                      4           1           A                 1 Y -- 10% of 9 is 0.9 so 1 is mapped to this row.
W4                      4           1           A                 9 N -- Order quantity was 10 but assigned is 10% of 9 so left over is this row.
W5                      5           1           A                 1 Y -- 10% of 9 is 0.9 so 1 is mapped to this row.
W5                      5           1           A                 9 N -- Order quantity was 10 but assigned is 10% of 9 so left over is this row.
W6                      6           1           A                 1 Y -- 10% of 9 is 0.9 so 1 is mapped to this row.
W6                      6           1           A                 1 N -- Order quantity was 10 but assigned is 10% of 9 so left over is this row.
W7                      7           1           A                 1 P -- Last row with status P becuase out of nothing left to assign here. 

and Chris said...

So, here's what I think you're looking for:

- 9 represents some form of "required" quantity
- You need to split this between the items according to the listed percentage (rounding fractional digits up)
- Calculate the running total of item percentages used, compare this to the required quantity
- If the running total <= required quantity (9), generated two rows:
* The first is status = Y that has the calculated pct
* The other is status = N that has (item) ordered quantity - calculated pct
- Once the running total > required quantity, only have one row/item with status = P

If so, you can do this as follows:

- Assign row numbers to the data in the order you want to allocate the quantities
- Use recursive with to calculate the running total of the quantity used so far (I doubt you can do this with analytic functions due to the rounding issues)
- Union all the results of this together:
* The first branch checks if the allocated quantity < required quantity, setting Y if it is and P if it isn't
* The second only returns rows where the outstanding quantity >= 0; this has status N and subtracts the allocated pct for this row from the ordered quantity for this row

Which looks something like the query below.

I'm not convinced this is exactly what you're looking for and there are various edge cases to consider; hopefully this is enough to get you started though:

with rws as (
  select organization_code, item_number, share_percentage, ordered_quantity,
         row_number () over ( 
           order by request_date
         ) rn,
         9 req_quant
  from   t1
  join   t2
  using ( organization_code, item_number )
), tree ( 
  organization_code, item_number, share_percentage, 
  ordered_quantity, rn, req_quant, quantity_consumed, tot_consumed
) as (
  select r.*,
         ceil ( req_quant * share_percentage / 100 ) quantity_consumed,
         ceil ( req_quant * share_percentage / 100 ) tot_consumed
  from   rws r
  where  rn = 1
  union  all
  select r.organization_code, r.item_number, r.share_percentage, 
         r.ordered_quantity, r.rn, r.req_quant,
         ceil ( r.req_quant * r.share_percentage / 100 ) quantity_consumed,
         t.tot_consumed + ceil ( r.req_quant * r.share_percentage / 100 )
  from   tree t
  join   rws r
  on     t.rn + 1 = r.rn
), used as (
  select t.*, 
         req_quant - tot_consumed remaining
  from   tree t
)
  select organization_code, item_number, share_percentage, 
         ordered_quantity, req_quant, quantity_consumed, tot_consumed,
         case 
           when req_quant >= tot_consumed then 'Y' 
           else 'P'
         end status, 
         1 sort_pos
  from   used
  union all
  select organization_code, item_number, share_percentage, 
         ordered_quantity, req_quant, ordered_quantity - quantity_consumed, tot_consumed,
         'N' status, 
         2 sort_pos
  from   used
  where  remaining >= 0
  order  by 1, 2, sort_pos;
  
ORGANIZATION_CODE ITEM_NUMBER  SHARE_PERCENTAGE  ORDERED_QUANTITY  REQ_QUANT  QUANTITY_CONSUMED  TOT_CONSUMED STATUS  SORT_POS   
W1                A                          40                10          9                  4             4 Y              1 
W1                A                          40                10          9                  6             4 N              2 
W2                A                          10                10          9                  1             5 Y              1 
W2                A                          10                10          9                  9             5 N              2 
W3                A                          10                10          9                  1             6 Y              1 
W3                A                          10                10          9                  9             6 N              2 
W4                A                          10                10          9                  1             7 Y              1 
W4                A                          10                10          9                  9             7 N              2 
W5                A                          10                10          9                  1             8 Y              1 
W5                A                          10                10          9                  9             8 N              2 
W6                A                          10                10          9                  1             9 Y              1 
W6                A                          10                10          9                  9             9 N              2 
W7                A                          10                10          9                  1            10 P              1

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Model clause

Rajeshwaran, Jeyabal, February 19, 2021 - 1:10 pm UTC

Team:

How about a (simple) model clause like this avoiding many layers of CTE's.

demo@XEPDB1> select t1.*,
  2      case when r2 = 1 and req_quant >= tot_consumed then 'Y'
  3           when r2 = 1 and req_quant < tot_consumed then 'P'
  4      else 'N' end as status
  5  from (
  6  select organization_code, item_number, share_percentage,ordered_quantity,req_quant, qty_consumed,tot_consumed,remaining
  7  from (
  8  select organization_code, item_number, share_percentage, ordered_quantity,
  9         row_number() over( order by t2.request_date ) r,
 10           9 req_quant
 11    from   t1
 12    join   t2
 13    using ( organization_code, item_number )
 14      )
 15  model
 16      dimension by ( r )
 17      measures( organization_code,item_number,share_percentage,ordered_quantity,
 18                  req_quant, 0 as qty_consumed,0 as tot_consumed, 0 as remaining)
 19      ( qty_consumed[any]  = ceil( (req_quant[cv()] * share_percentage[cv()])/100 ) ,
 20        tot_consumed[any] = sum( qty_consumed ) [ r<=cv() ],
 21        remaining[any] = req_quant[cv()] - tot_consumed[cv()]
 22      )
 23      ) t1, lateral( select level r2
 24          from dual
 25          connect by level <= case when remaining >= 0 then 2 else 1 end )
 26  order by 1,r2
 27  /

ORG ITEM_NUMBE SHARE_PERCENTAGE ORDERED_QUANTITY  REQ_QUANT QTY_CONSUMED TOT_CONSUMED  REMAINING S
--- ---------- ---------------- ---------------- ---------- ------------ ------------ ---------- -
W1  A                        40               10          9            4            4          5 Y
W1  A                        40               10          9            4            4          5 N
W2  A                        10               10          9            1            5          4 Y
W2  A                        10               10          9            1            5          4 N
W3  A                        10               10          9            1            6          3 Y
W3  A                        10               10          9            1            6          3 N
W4  A                        10               10          9            1            7          2 Y
W4  A                        10               10          9            1            7          2 N
W5  A                        10               10          9            1            8          1 Y
W5  A                        10               10          9            1            8          1 N
W6  A                        10               10          9            1            9          0 Y
W6  A                        10               10          9            1            9          0 N
W7  A                        10               10          9            1           10         -1 P

13 rows selected.

demo@XEPDB1>

Chris Saxon
February 19, 2021 - 1:37 pm UTC

I'm not sure simple and MODEL go together ;)

That seems to work - though my understanding is for N rows, QTY_CONSUMED should be ORDERED_QUANTITY - QTY_CONSUMED for the corresponding Y row

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.