Speed Phreak #2: Matching Inflows and Outflows (IT Toolbox Blogs)



A good night’s sleep is increasingly common for me — finally, now 18 months after leaving a job for which I was “on call” with increasing frequency (due to staff reassignments to The Next Big Thing). I call my former work-life years of cumulative trauma. The stress is slowly ebbing out.

 

With decent sleep, I was able to more carefully consider the  “Phil Factor SQL Speed Phreak Competition: No 2: The ‘FIFO Stock Inventory’ SQL Problem“.

 


 

Last time (my initial foray), I wrote in part:

 

For an “out” transaction, the trick is to match find the lowest point in the ever-rising inventory graph that just “covers” the required amount. The lowest point — and the one just below it. At the next-lower inventory point, all the inventory can be “lumped together”: the prices can be averaged because you’re going to use them all. The inventory that arrived next may need to be split.

 

That’s still true. However, I realized I’d written an almost shamefully-clumsy query to do so; a klutzy query that only looked at one “out” transaction.

 

How would I match all out-transactions?

 

That’s when I knew I’d built a Rube Goldberg query. Further analytic functions, just basic ones like LAG, would provide clean-up capability.

 

I just needed to match up the cumulative out-quantities with the cumulative in-quantities. If I need 610 units for my first out-transaction, I just had find the inventory count 610 was between two inventory-inputs, cumulatively over time. 

 

If my second inventory-request was for 165 units, I needed to add 165 to the prior 610: that’s 775 units. Then once again, find the increasing inventory-interval that included 775.

 


 

 

I futher simplied the input columns. Who cares about StockID as a surrogate for TransDate? 

 

 

WITH
invaction
(ArticleID, TranDate,  TranCode,Items,Price ) AS (           SELECT
 10000, '10:45:07', 'IN',     38, 245.94 FROM DUAL UNION ALL SELECT
 10000, '10:45:08', 'IN',    100, 245.94 FROM DUAL UNION ALL SELECT
 10000, '10:45:09', 'IN',    150, 245.94 FROM DUAL UNION ALL SELECT
 10000, '10:45:20', 'IN',    450, 245.94 FROM DUAL UNION ALL SELECT
 10000, '12:05:25', 'OUT',   610, NULL   FROM DUAL UNION ALL SELECT
 10000, '14:39:27', 'IN',      5, 199.95 FROM DUAL UNION ALL SELECT
 10000, '14:39:28', 'RET',    10,   NULL FROM DUAL UNION ALL SELECT
 10000, '14:39:29', 'IN',     15, 199.95 FROM DUAL UNION ALL SELECT
 10000, '14:39:40', 'IN',     32, 199.95 FROM DUAL UNION ALL SELECT
 10000, '16:14:13', 'OUT',   165, NULL   FROM DUAL UNION ALL SELECT
 10000, '18:18:58', 'RET',     5, NULL   FROM DUAL UNION ALL SELECT
 10000, '20:18:54', 'IN',    500, 135.91 FROM DUAL ),
-- -----------------------------------------------
-- transaction#s are easy as 1, 2, 3...
-- -----------------------------------------------
assign_trx# AS (
select ArticleID, TranDate, TranCode
     , Items, Price
     , ROW_NUMBER()OVER(ORDER BY tranDate) trx#
  from invaction
),
-- -----------------------------------------------
-- cumulative qty of IN/RET inventory
--   and latest IN for each RET
-- -----------------------------------------------
   -- first, accumulative over time (transaction#)
   cuminflow AS (
   SELECT trx#
        , tranCode
        , Items
        , Price
        , MAX(CASE WHEN Price IS NOT NULL THEN trx# END)
           OVER(ORDER BY trx# ASC) as priced_trx#
        , SUM(Items)OVER(ORDER BY trx# ASC) AS qty
     FROM assign_trx#
    WHERE TranCode <> 'OUT'
   ),
   -- finally, get prior cumulative qty and $ of IN/RET
   --           inventory over time (transaction#)
   return_pricing AS (
   SELECT trx#
        , tranCode
        , Items
        , MAX(Price)OVER(PARTITION BY priced_trx#) as Price$
        , qty
     FROM cuminflow
   ),
   inventory$ AS (
   SELECT trx#
        , tranCode
        , Items
        , price$
        , qty
        , SUM(price$*items)OVER(ORDER BY trx#) as qty$
     FROM return_pricing
   ),
   inflowing AS (
   SELECT COALESCE(LAG(trx#)OVER(ORDER BY trx#),0) as b4_trx#
        , trx#
        , tranCode
        , Items
        , price$
        , COALESCE(LAG(qty)OVER(ORDER BY trx#),0)  as b4_qty
        , qty
        , COALESCE(LAG(qty$)OVER(ORDER BY trx#),0) as B4_qty$
        , qty$
     FROM inventory$
   ),
-- -----------------------------------------------
-- identify outflow requests
-- -----------------------------------------------
   outflowing AS (
   SELECT trx#
        , tranCode
        , Items -- qty
        , SUM(items)OVER(ORDER BY trx#) as cumOutQty
     FROM assign_trx#
    WHERE TranCode = 'OUT'
   )
-- -----------------------------------------------
-- match inflows and outflows
-- -----------------------------------------------
SELECT inflowing.trx#
     , inflowing.tranCode
     , inflowing.Items
     , inflowing.price$
     , inflowing.b4_qty
     , outflowing.cumOutQty
     , inflowing.qty
     , outflowing.Items    AS out_qty
     , outflowing.trx# AS out_trx#
  FROM inflowing
  LEFT OUTER
  JOIN outflowing
    ON outflowing.cumOutQty
       BETWEEN inflowing.b4_qty
           AND inflowing.qty
   AND inflowing.trx# < outflowing.trx#
 ORDER BY inflowing.trx#;

TRX# TRA  ITEMS  PRICE$  B4_QTY  CUMOUTQTY  QTY OUT_QTY OUT_TRX#
---- --- ------ ------- ------- ---------- ---- ------- --------
   1 IN      38  245.94       0              38
   2 IN     100  245.94      38             138
   3 IN     150  245.94     138             288
   4 IN     450  245.94     288        610  738     610        5
   6 IN       5  199.95     738             743
   7 RET     10  199.95     743             753
   8 IN      15  199.95     753             768
   9 IN      32  199.95     768        775  800     165       10
  11 RET      5  199.95     800             805
  12 IN     500  135.91     805            1,305

 

Now this version is priceless. At least so far, I’ve not attempted to calculate the unit-price for each of the outbound quantities.

 

I feel better though.

 

<END>

 

 



 

 

The wikipedia illustration is from Rube Goldberg (1883-1970) depicting Professor Lucifer Butts using a napkin.When lifting the teaspoon (A), a rope (B) is pulled, which pulls behind a bucket cap (C), which throws a cracker (D) into the air, which on the fly catches a parrot (E), which tilts the tee (F) The seeds (G) spill into the bucket (H), which, under additional load, pulls down the lane (I), opening and igniting the lighter (J) that causes the rocket to fire (K); This, with the sickle (L), crosses the string (M), which releases the clock swing, which starts to sway inward and outward, thus wiping the beard with the professor’s napkin.

 

 
SQL
 
Analytic functions
 
inventory control
 
Phil Factor

Source: SANS ISC SecNewsFeed @ May 1, 2017 at 06:09AM

0
Share