Running Decrement 2

[thermidor]

All:

I have two tables, one contains Inventory, the other Orders. The Orders are associated with a specific time period but Inventory is not. I want to join the tables and decrement Inventory from the Orders across the periods I have orders for.
Any suggestions on SQL to accomplish this would be most appreciated.

TIA,
Sven

Orders Table

SKU     Period  Order_Qty
------  ------  ---------
A123    200803         10
A123    200804         20
A123    200807         30
B456    200804         35
B456    200807         15
B456    200808         50
C789    200803        120

Inventory Table

SKU     Inv_Qty
------  ---------
A123           15
B456           75

Needed result from join of Orders and Inventory

SKU     Period  Order_Qty
------  ------  ---------
A123    200803          0
A123    200804         15
A123    200807         30
B456    200804          0
B456    200807          0
B456    200808         25
C789    200803        120

 

[SantaMufasa]

Sven,

Maybe I'm dense and it's too early in the morning (locally) for my brain to be functioning yet, but can you please post wording (business rules) that explain the data transformations and results that you want? Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[thermidor]

Basically the business question is: How many of each sku need to be made and when?

For example, (using the table above) the Orders for SKU A123 are:
10 in 200803
20 in 200804
30 in 200807

We start with 15 of A123 in Inventory.

So we don't need to make any additional A123 to cover the 200803 Order of 10.

For the next period, 200804 our Order is for 20. The remainder in Inventory is 5 (Original Inventory Qty of 15 less the 10 used to fill the 200803 Order). So to fill the 200804 Order we need to make 15 (Order Qty minus remaining Inventory Qty).

For the next period, 200807 our Order is for 30. We have no remaining Inventory. To fill the 200807 Order we need to make 30.(Still Order minus remaining Inventory, though there is no Inventory.)

I appreciate your help. I hope this makes it clear.

Thanks,
Sven

 

[thermidor]

and I should have mentioned, I need to do it for every SKU...

 

[SantaMufasa]

Sven,

I believe that your solution lies in Oracle's Analytic functions. Within the last hour, I posted some excellent links for that topic in thread1177-1454857. I'd produce a sample solution using Analytic functions, but I just had a production database run out of disk space and I must solve that.

Please post if you find (and create) a solution to your problem with Analytics. (If I don't see a "problem solved" verdict, then I'll try to create one for you later.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[thermidor]

Thanks for the tip on Analytic Functions! Here's my solution...

SELECT 
  ord.sku,
  ord.period,
  ord.order_qty,
  ord.order_qty_rolling,
  nvl(inv.inv_qty, 0) inv_qty,

/* Explanation of following CASE STATEMENT
        Inv Qty
          -> nvl(inv.inv_qty, 0)
        
        Qty required to fill prev period order 
          -> ord.order_qty_rolling - ord.order_qty 
          
        Remaining Inv - If Inv less Qty required to 
        fill prev period order is less than zero, 
        Inventory is zero
          -> CASE 
              WHEN nvl(inv.inv_qty, 0) -                     (ord.order_qty_rolling - ord.order_qty)
                         < 0 
                THEN 0 
              ELSE nvl(inv.inv_qty, 0) -                     (ord.order_qty_rolling -                        ord.order_qty) 
              END  
        
        If Order minus Remaining Inv < 0 then 0 
          else Order minus Remaining Inv
          -> CASE 
                WHEN (ord.order_qty - 
                    (CASE 
                      WHEN nvl(inv.inv_qty, 0) -                        (ord.order_qty_rolling -                            ord.order_qty) < 0 
                        THEN 0 
                      ELSE nvl(inv.inv_qty, 0) -                         (ord.order_qty_rolling -                             ord.order_qty) 
                    END)) < 0
                  THEN 0
                ELSE (ord.order_qty - 
                    (CASE 
                      WHEN nvl(inv.inv_qty, 0) -                         (ord.order_qty_rolling -                              ord.order_qty) < 0 
                        THEN 0 
                      ELSE nvl(inv.inv_qty, 0) -                          (ord.order_qty_rolling -                              ord.order_qty) 
                    END))
              END  
      */    
  CASE 
                WHEN (ord.order_qty - 
                    (CASE 
                      WHEN nvl(inv.inv_qty, 0) -                        (ord.order_qty_rolling -                            ord.order_qty) < 0 
                        THEN 0 
                      ELSE nvl(inv.inv_qty, 0) -                         (ord.order_qty_rolling -                             ord.order_qty) 
                    END)) < 0
                  THEN 0
                ELSE (ord.order_qty - 
                    (CASE 
                      WHEN nvl(inv.inv_qty, 0) -                         (ord.order_qty_rolling -                              ord.order_qty) < 0 
                        THEN 0 
                      ELSE nvl(inv.inv_qty, 0) -                          (ord.order_qty_rolling -                              ord.order_qty) 
                    END))
              END  
       
      FROM 
      (SELECT  
        sku, 
        period, 
        order_qty,
        SUM(order_qty) OVER (PARTITION BY sku ORDER BY period) order_qty_rolling
          FROM 
            order
        ORDER BY  sku, period   
      ) ord
        left join 
      (SELECT sku, inv_qty
        FROM inv) inv
        on ord.sku = inv.sku
        order by ord.sku, ord.period

 

[SantaMufasa]

Sven,

You, too, deserve a

for taking the intitiative to solve your own proble, devise such a well-thought-out application of Oracle's Analytic functions, the share the fruits of your labor with us...Good onya!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]