The Basics of Consumption Queries in SQL

I was inspired to post this blog by a post on the OTN Forums where a user was asking about calculating the consumption of hours against a target – that is, given a target of X hours with N hours per day, how many days would it be until they reached the target? I knew I had done similar things several times in the past, and each time I always seem to forget the actual logic and end up having to look it up again. So hopefully this post will also be also be a little reminder for me if nothing else.
Queries such as this can actually take many guises, but they all share the same underlying principle. A common use is in a warehouse environment where we have stock located in many different locations (because we can of course only fit a finite quantity of an item in a single location/box/storage unit etc) and we get a big order for an item placed. We want to iteratively pick all the items from each locator until need less than is in a locator, then we pick just the amount that is remaining.

Warehouse Diagram

Using the above example, if we had a request for item ABC of quantity 60, we could easily walk from left to right and pick 20 from Locator A, 30 from Locator B, and then the remaining 10 from the 20 in Locator D.
Often though there are requirements for stock usage in warehouses such as FIFO (First In, First Out), which basically translates to Pick the oldest stock first. This is because we don’t want stock with a limited shelf life perishing whilst pickers only ever take the stock which is nearest the door!

I’ve seen a number of different implementations of this within an Oracle environment, some good, some not so good. The latter tend to be approaches which loop around in PL/SQL and count up the quantity from each location. What I want to do here though is demonstrate a pure SQL way of doing this using Analytic Functions. It’s not particularly complicated, but it’s one of those things that when you come to do it you end up scratching your head for ages trying to remember how to do it!
I’m also a big fan of do it in a single statement where possible/sensible – there’s something quite satisfying with solving (seemingly) complicated problems within a single query/command 🙂 .


 

As always, let’s set up some demo data. I’ll create a few items, some locators and put some stock in them. We’ll assume that once stock is in a locator we can’t add more to it at a later date (to preserve our FIFO rules). We also have an order lines table, which is just a request to pick a quantity of an item.

ERD

Create Table xxItems As   Select 1 item_id, 'ABC' item_name From dual Union All   Select 2 item_id, 'DEF' item_name From dual Union All   Select 3 item_id, 'GHI' item_name From dual;    Create Table xxLocators As   Select 1 locator_id, 'A' locator_name From dual Union All   Select 2 locator_id, 'B' locator_name From dual Union All   Select 3 locator_id, 'C' locator_name From dual Union All   Select 4 locator_id, 'D' locator_name From dual;    Create Table xxOnhandStock As   Select 1 item_id, 1 locator_id, 20 qty, Date '2017-01-01' stock_age From dual Union All   Select 1 item_id, 2 locator_id, 30 qty, Date '2016-01-01' stock_age From dual Union All   Select 1 item_id, 4 locator_id, 20 qty, Date '2017-02-10' stock_age From dual Union All   Select 2 item_id, 1 locator_id, 100 qty, Date '2017-03-23' stock_age From dual Union All   Select 2 item_id, 2 locator_id, 10 qty, Date '2017-01-12' stock_age From dual Union All   Select 2 item_id, 3 locator_id, 45 qty, Date '2016-08-09' stock_age From dual Union All   Select 2 item_id, 4 locator_id, 20 qty, Date '2016-12-30' stock_age From dual Union All   Select 3 item_id, 1 locator_id, 10 qty, Date '2017-03-23' stock_age From dual Union All   Select 3 item_id, 2 locator_id, 0 qty, Date '2017-01-26' stock_age From dual Union All   Select 3 item_id, 3 locator_id, 10 qty, Date '2016-11-12' stock_age From dual Union All   Select 3 item_id, 4 locator_id, 10 qty, Date '2016-09-17' stock_age From dual;   Create Table xxOrderLines As   Select 1 order_line_id, 1 item_id, 60 qty From dual Union All   Select 2 order_line_id, 2 item_id, 37 qty From dual Union All   Select 3 order_line_id, 3 item_id, 54 qty From dual; 

I’m going to give the full query first so you can see it working – then I’ll break it down a bit and explain what is what.

Select v.* From (   Select xol.order_line_id order_line_id,           xol.qty order_qty,           xi.item_name item_name,           xl.locator_name locator_name,           xo.qty onhand_qty,          Greatest(Least(xol.qty - Nvl(Sum(xo.qty) Over             (Partition By xol.order_line_id Order By xo.stock_age             Rows Between Unbounded Preceding And 1 Preceding),0),xo.qty),0) pick_qty     From xxonhandstock xo,          xxorderlines xol,          xxitems xi,          xxlocators xl    Where xol.item_id = xo.item_id      And xl.locator_id = xo.locator_id      And xi.item_id = xo.item_id) v  Where v.pick_qty > 0; 

Running that we get the following results

ORDER_LINE_ID  ORDER_QTY ITEM_NAME  LOCATOR_NAME ONHAND_QTY   PICK_QTY ------------- ---------- ---------- ------------ ---------- ----------             1         60 ABC        B                    30         30             1         60 ABC        A                    20         20             1         60 ABC        D                    20         10             2         37 DEF        C                    45         37             3         54 GHI        D                    10         10             3         54 GHI        C                    10         10             3         54 GHI        A                    10         10 

We have a qty of 60 for item ABC, 37 for DEF and we have only been able to partially satisfy the request of 54 for item GHI with the 30 we have available (more on that later).
Now let’s break the above query down – clearly the work is being done within the pick_qty column, so what is our logic? First, for each line of stock, we want to see whether we have fully satisfied the request yet – so we say “how much have we consumed up to but not including the current row?”

Sum(xo.qty) Over (Partition By xol.order_line_id Order By xo.stock_age Rows Between Unbounded Preceding And 1 Preceding) 

Our Order By clause implements the FIFO logic and the Rows Between... gets us the Sum() up to but not including the current row. Then we subtract that amount from the request quantity, however if the result of that is greater than the quantity available, we use the latter (this is our Least function. Finally we consider the cases where we have consumed all the stock and the result is now being driven negative – so we use the Greatest function with a value of zero to set all negative amounts to zero, thus leaving us with positive values only where we have consumed stock.

Originally I was going to leave this demo there, however when writing this I thought “Wouldn’t it be nice if for order line 3, where we have an unallocated amount, we could actually see that?” – that gives us the perfect opportunity to use the “introduced in Oracle 10g but hardly used by anyone” Model clause.

Select * From (   Select order_line_id, order_qty, item_name, locator_name, onhand_qty, pick_qty, out_qty From (     Select xol.order_line_id order_line_id,             xol.qty order_qty,             xi.item_name item_name,             xl.locator_name locator_name,             xo.qty onhand_qty,            Greatest(Least(xol.qty - Nvl(Sum(xo.qty) Over (Partition By xol.order_line_id Order By xo.stock_age Rows Between Unbounded Preceding And 1 Preceding),0),xo.qty),0) pick_qty,            0 out_qty       From xxonhandstock xo,            xxorderlines xol,            xxitems xi,            xxlocators xl      Where xol.item_id = xo.item_id        And xl.locator_id = xo.locator_id        And xi.item_id = xo.item_id) v   Model      Partition By (order_line_id, item_name)     Dimension By (locator_name)      Measures (pick_qty, order_qty, onhand_qty, out_qty)     Rules (order_qty[Null] = Max(order_qty)[Any],             out_qty[Null] = Max(order_qty)[Any] - Sum(pick_qty)[Any])            order by order_line_id, locator_name)   Where 0 < Any(pick_qty, out_qty); 

We have had to modify our original query slightly to include some supplmentary information, however we are then able to create a dummy row which is calculated as the target quantity minus the sum of all allocated lines.

ORDER_LINE_ID  ORDER_QTY ITEM_NAME  LOCATOR_NAME ONHAND_QTY   PICK_QTY    OUT_QTY ------------- ---------- ---------- ------------ ---------- ---------- ----------             1         60 ABC        A                    20         20          0             1         60 ABC        B                    30         30          0             1         60 ABC        D                    20         10          0             2         37 DEF        C                    45         37          0             3         54 GHI        A                    10         10          0             3         54 GHI        C                    10         10          0             3         54 GHI        D                    10         10          0             3         54 GHI                                                   24  8 rows selected. 

See we now have the additional line 8, which shows that we have 24 of item GHI outstanding. I separated the out_qty and pick_qty columns for clarity purposes however we could easily have used the existing one.

Pretty neat huh?

Leave a Reply

Your email address will not be published. Required fields are marked *

eighteen + 13 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.