Speed Phreak #2: Pricing, Partitioning and Polishing (IT Toolbox Blogs)



Getting lost in the details, wanting to cover every contingency — that’s easy for those of us who like to toss ideas up in the air, spin them around, and inspect every angle.

 

Yet sometimes pursuing those details are useless. If you’re just building the facade for a Broadway show; the important aspects are how it looks to the audience, and how easy it is to wield around the stage. No one (I’m aware of) stains and laquers the wood on the backside of the facade. Having looked behind the dry wall of existsing homes a few times, I’m unsurprised to find oversize distances between studs (not up to building code), and other unpleasant occurrences. It’s a bit like looking at someone else’s SQL at times…

 

But I digress.

 

As Stephen R. Covey emphasized the habit: “Begin with the End in Mind.” In like manner, a traceability matrix confirms you accomplished your goal, and didn’t get distracted by sea shells by the sea shore.

 

Not that that happens to me. 

 


 

There are numerous distractions for the “Phil Factor SQL Speed Phreak Competition: No 2: The ‘FIFO Stock Inventory’ SQL Problem” Yet the goal is simply to “calculate current items in stock and current stock value in FIFO order.”

 

For example, we don’t care the exact count of outbound inventory actions for a particular product (“ArticleID”). One transaction for a million items, or one-hundred-thousand orders for 10 items each– the net result is the same: one-million items gone.

 

Therefore, only the cumulative outbound quantity over all actions for an ArticleID is interesting. I don’t have to wonder about multiple outbound requests just milliseconds apart, database record locking, backorders, and similar complexities. Just figure out the cumulative effect of the outbound transactions.

 


 

My query here is almost nothing like my original. The query is fairly compact (dense), completely analytic-function driven, and uses zero joins (kind of like I did with ALL_CONSTRAINTS previously.).

 

WITH
-- ----------------------------------------------------------------------------
-- STOCK INVENTORY REPORT with current number of items in stock and value 
--   for each articleID according to FIFO rules.
-- ----------------------------------------------------------------------------
sample_data
(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
20000, '10:45:20', 'IN',    850, 245.94 FROM DUAL UNION ALL SELECT
20000, '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
     , CASE WHEN TranCode = 'OUT' THEN 'OUT' ELSE 'IN' END as TranCode
     , Items, Price
     , ROW_NUMBER()OVER
         (PARTITION BY ArticleID
              ORDER BY tranDate) trx#
  from sample_data
),
-- -----------------------------------------------
-- For returned products, we need the transaction# of the prior delivery
-- For inbound products, we need the cumulating inventory
-- For outbound, we need the total out quantity
-- -----------------------------------------------
setup AS (
SELECT trx#
     , ArticleID
     , tranCode
     , Items
     , Price
     , CASE WHEN tranCode = 'IN'
            THEN MAX(CASE WHEN Price IS NOT NULL THEN trx# END)OVER
            (PARTITION BY ArticleID
                 ORDER BY trx# ASC)
        END as priced_trx# -- prior priced transaction if it's a return
     , CASE WHEN tranCode = 'IN'
            THEN SUM(CASE WHEN TranCode = 'IN' THEN Items END)OVER
            (PARTITION BY ArticleID
                 ORDER BY trx# ASC)
         END as inventory
     , CASE WHEN tranCode = 'OUT'
            THEN SUM(Items) OVER
           (PARTITION BY ArticleID, TranCode)
        END as total_out
  FROM assign_trx#
),
-- -----------------------------------------------
-- For returned products, we need the PRICE of the prior delivery transaction.
-- For inbound products, we need the PRIOR cumulating inventory.
-- For outbound, we need the total out quantity on the INBOUND records too.
-- -----------------------------------------------
machinations AS (
SELECT trx#
     , ArticleID
     , tranCode
     , Items
     , MAX(Price)OVER(PARTITION BY priced_trx#) as price -- price the returns
     , CASE WHEN tranCode = 'IN'
            THEN COALESCE(LAG(Inventory)OVER
            (PARTITION BY ArticleID, tranCode
                 ORDER BY trx# ASC),0)
         END as b4_qty
     , MAX(total_out)OVER(PARTITION BY ArticleID) as total_out
     , inventory
 FROM setup t
 --ORDER BY ArticleID, trx#
)
SELECT ArticleID 
     , sum(CASE WHEN total_out BETWEEN b4_qty AND inventory
                THEN inventory-total_out
                ELSE items
            END 
          * price )as "$"
     , MAX(inventory-total_out) as rem_qty
     , MAX(inventory) as max_qty
  FROM machinations m
 WHERE inventory >= total_out
 GROUP BY ArticleID
 ORDER BY 1;

ARTICLEID          $    REM_QTY    MAX_QTY
--------- ---------- ---------- ----------
    10000    73953.5        530       1305
    20000    59025.6        240        850

 

In order to make the intermediate results easier to read for me, the code is a bit more complex; for example there are “CASE WHEN tranCode = ” scattered about. The query might work fine without those, but I’d have a more difficult time reading the results because the columns are only meaningful for either the IN or OUT transaction.

 

 


 

You may be wondering why I used Sullenberger-Skiles smoothing instead of FIRST_VALUE IGNORE NULLS at one point: simply because I can just drop this query into SQL Server 2012 (after getting rid of “FROM DUAL”), and it runs!

 

Last time I split the IN and OUT transactions in separate CTEs, then joined them thusly:

 

...
SELECT inflowing.trx#
     , inflowing.tranCode
     , inflowing.Items
     , inflowing.price$
     , inflowing.qty
  FROM inflowing
UNION ALL
SELECT trx#
     , tranCode
     , Items -- qty
     , NULL
     , cumoutqty
  FROM outflowing
 ORDER BY 5,2

 

This time I just used analytic functions to calculate the total outbound quantities for each ArticleID then filtered for the remaining inventory (shown below as it cascades through the CTEs:

 

     , CASE WHEN tranCode = 'OUT'
            THEN SUM(Items) OVER
           (PARTITION BY ArticleID, TranCode)
        END as total_out
...
     , MAX(total_out)OVER(PARTITION BY ArticleID) as total_out
...
 WHERE inventory >= total_out

 

It’s an implicit self-join (as I did with ALL_CONSTRAINTS previously).

 

Although I used aggregates in the final SELECT of the cascade of CTEs, I toyed with analytics; in an earlier version the SUM was an analytic, then I had another SELECT to get the net for each ArticleID. However, that seemed pretty excessive for applying analytics.

 

 


 

 

Maybe I’ll run this query through the complete set of test data.

 

Or maybe I’m sufficiently thilled just to meet my original goal: an analytic-function based query.

 

<END>

 

 



 

The wikipedia image is Guinness World Record-holder Owen Morse joggling during a training session at University of California, Irvine, in 1988. Original uploader was RadioKirk at en.wikipedia – Transferred from en.wikipedia, Public Domain.

 

 

 
Speed Phreak
 
Sullenberger-Skiles Smoothing. SQL Server 2012
 
Oracle 12c
 
SQL

Source: SANS ISC SecNewsFeed @ May 3, 2017 at 08:12AM

0
Share