Sunday, December 21, 2008

Rate Mix Math Problem - An SQL Solution

by David L. Penton via The Penton-izer on 12/21/2008 10:42:00 PM

Periodically people send me emails or some other form of a message asking me for help with problems that are facing.  Since I have some proficiencies in mathematics, in some cases the problems tend to be very related to mathematics.  One such problem came to me from someone I have never met, but know on Twitter.  He called it his "Rate Mix Math Problem".  What I want to do with this post is to describe what my thought processes were to analyze this problem and help him solve it.  So...the problem:

I am currently working on an inventory distribution system in which my system will automatically suggest production amounts for certain products based on inventory levels.  With this being said, there are a few key players in the problem:

1. Begin Inventory - This is basically the amount of inventory that we currently have on hand.
2. Inventory Standard - This is the amount of inventory that we will need in order to meet production / demand standards.
3. Planned Production - This is the quantity that we will be planning to produce

Whenever we make a decision to produce, we basically allocate Planned Production to those items w/ the lowest percent to standard (Current Inventory / Inventory Standard ).  We call this process “Rate Mixing”.

For instance, consider this dataset:

Planned Production:  12,635

If I were to Rate Mix this, I would get a result set like this:

Basically, all of the planned production was mixed to the Lowest% standard until all the planned production ( 12,635 cases ) was allocated.  Consequently, the New % Std is match across all of the smaller items.

So, here is my question.  Currently, I am using several loops in code to make this allocation happen.  Even though this is accurate, the act of looping is more costly in performance than I care for it to be.  Is there a formula that I can apply to each line item to directly get the allocation of planned production and avoid having to loop, given that I know all the information from table 1 during the process?  I feel as if there should be a way to do this, if I were to consider the aggregations of inventory standards and total inventories, but I can't seem to make it come out appropriately (especially when I take the beginning inventories into account)

When I looked at this, I felt the data came out of some spreadsheet.  This typically means there are formulas that could possibly be used to calculate some of the columns of data.  There was certainly formatting of data happening.  Why did I think that?  First clue was the percentages.  What does this tell me?  There could possibly be a point where data is rounded, thus the "final" results might be off - or more accurately might not reflect the typical business normalization of values.  Does that mean the process is flawed, or that is just what is the expected business case?  The more I reviewed the data, the more I was focused on what some of the values meant.  I do believe it is a necessity to understand what your data means - the origins, what process might be defined on that case under what conditions, and what do the results mean.  In other words, after the application of process, do your results accurately portray the correct results.  So, I asked some simple questions:

If you can give me a little more on how you are attempting to calculate

• Mixed Planned Prod
• New % Std
• New Inv

For instance, you say:

"Basically, all of the planned production was mixed to the Lowest% standard until all the planned production ( 12,635 cases ) was allocated.  Consequently, the New % Std is match across all of the smaller items"

Is "planned production a separate value, is it derived from the data in table 1 somehow, it that value split between a percentage of the spread of Prod Code?  Not sure from the data you've sent me here.

You see, I was concerned about why the values for "Mixed Planned Prod" were zero.  What conditions provided that result?  I decided that if I could determine the formulae that gave me the other rows, then I could define the variables and determine the conditions that would achieve the secondary results for when "Mixed Planned Prod" was zero.  The other side of that was to determine how to come up with the "New % Std".

To begin my code, I wanted to come up with a table that had my introductory values.  I had to make some assumptions on data types and lengths.  I decided that "End % Std" was a calculation and that "cases" was a variable defined:

declare @t table (
    ProdCode int not null primary key
    , BeginInv int not null
    , InvStd int not null
    , EndPctStd as convert(decimal(10, 2), 100.0
        * convert(decimal(10, 2), BeginInv)
        / convert(decimal(10, 2), InvStd) ) )

declare @cases int

set @cases = 12635

insert into @t ( ProdCode, BeginInv, InvStd )
select 131, 58739, 3980 union all
select 144, 2989, 1451 union all
select 157, 1702, 616 union all
select 170, 20908, 6726 union all
select 178, 27595, 1306 union all
select 186, -84, 558

This gave me my beginning dataset - something I could work with to come up with my calculations.  I thought the best thing to do would be to determine what the "New % Std" was.  Immediately it seemed that some (in this case two) records were omitted from the calculations.  That turned out to be a bit of a manual process, so I just ended up excluding the TOP 2 "BeginInv" values from my calculations.  Also, those omitted records had no calculations on them - so that was easy enough to plan for. In looking at values in the resulting columns I saw that "InvStd" * "New % Std" - "BeginInv" was equal to "Mixed Planned Prod".  This was good, because it gave me basis for how I was to get to my solution of what "New % Std" equaled.  I solved for "New % Std" and that helped  ( "BeginInv" + ??? / "InvStd" = "New % Std")  but I needed some equalizers.  I needed to allocate the planned production to all of the records.  Since "New % Std" was the same for all of the records, I knew I needed a summation.  Based upon this knowledge I came up with the following SQL that worked nicely for this:

select
    sum(t.BeginInv) + @cases) / convert(decimal(10, 2), sum(t.InvStd))
from @t t
where
    t.BeginInv not in (
        /* got to figure out what to leave out */
        select top 2 tx.BeginInv
        from @t tx
        order by
            tx.BeginInv desc
    )

Remember I said that the process for determining the records to omit was a manual process, so I left it where it could be easy to replace that with whatever logic is used to determine which records to omit.  I am also allowing for two decimal points (408% is really 4.08).  With this piece of information, it was easy to calculate the rest of the columns.  With that, my final query comes to:

select
    t.ProdCode
    , t.BeginInv
    , t.InvStd
    , t.EndPctStd
    , cast(round(coalesce(t.InvStd * m.NewPctStd, t.BeginInv), 0) as int)
        - t.BeginInv as MixedPlannedProd
    , coalesce(m.NewPctStd * 100, t.EndPctStd) as NewPctStd
    , cast(round(coalesce(t.InvStd * m.NewPctStd, t.BeginInv), 0) as int)
        as NewInv
from @t t
left outer join (
    select
        t2.ProdCode
        , t2.BeginInv
        , t2.InvStd
        , t2.EndPctStd
        , cast((
            select coalesce( (
                select
                    (sum(t3.BeginInv) + @cases)
                    / convert(decimal(10, 2), sum(t3.InvStd))
                from @t t3
                where t3.BeginInv not in (
                    /* got to figure out what to leave out */
                    select top 2 tx.BeginInv from @t tx
                    order by tx.BeginInv desc
                )), 0)
            ) as decimal(10, 2)) as NewPctStd
    from @t t2
    where
        t2.BeginInv not in (
            /* got to figure out what to leave out */
            select top 2 tx.BeginInv from @t tx
            order by tx.BeginInv desc
        )
) as m on m.ProdCode = t.ProdCode

I am handling nulls with coalesce and keeping the scale at two decimal places.  I like where this solution went, but I obviously think there are opportunities to improve on it.  One version if this was to separate out the calculation for "New % Std", another would be to come up with a far better way to determine which rows are to be included in the calculation to determine the rows for the "New % Std" calculation.  The best thing - this isn't a solution that required any cursors or other procedural logic to solve.

The point that I was trying to get across in this blog post was how one might approach a problem of this type.  Asking the right questions, deconstructing the problem to simpler terms, and then combining to achieve your result.  Of course, in this case it sure helped to have a strong background in T-SQL. :)

Posted to SQL Server

Similar Posts

1. Introduction to SQL Profiler
2. Regex Precompilation in Practice
3. SQL Profiler Command Line Options