none
T-SQL "Running Subtracting" of One Column from Another

    Question

  • Hello:

    Below is a T-SQL query that I developed in SQL 2008 that contains inventory shipping data based on two views that I created--CTSSHIP and CORPAVAIL.  CTSSHIP contains the "Required Quantity" for inventoried items at their locations, while CORPAVAIL contains the "Quantity Available" at the corporate location.

    The Required Quantity field is CTSSHIP.RequiredQty, while the Quantity Available field is CORPAVAIL.[Qty Available].

    Also, as seen in my query, the QtyAvailToShip field is currently calculated as (CORPAVAIL.[Qty Available] - CTSSHIP.RequiredQty).

    I need help in understanding how to modify this query in two ways.  First, I need to have the QtyAvailToShip field actually  be calculated as Qty Available - RequiredQty--but as a "running subtraction".  Here's what I mean.  Let's say Inventory Item A is at three locations (1, 2, and 3).  Location 1 has 10 as the RequiredQty, Location 2 has 5 as the RequiredQty, and Location 3 has 7 as the RequiredQty.  And, let's say that Item A has 30 as Qty Available at the corporate location.  So, the QtyAvailToShip field needs to have for the first row be 20, the second row be 15, and the third row be 8 (30-10-5-7 = 8).

    Secondly, I need to have the "where" clause of my query below modified to return values from this query only where the newly modified QtyAvailToShip field > the RequiredQty.  Continuing with the example that I just gave in my first requirement, let's say that Location 4 has a RequiredQty of 10.  Well, 8 is < 10.  So, my query should not return a row for this item for Location 4 since the new QtyAvailToShip field < the RequiredQty.

    On this second modification I may be able to do that on my own as long as I can figure out the first modification.  With the first modification in place, I can probably simply create a view of this query and then do a "select * from" on that view where QtyAvailToShip field > the RequiredQty.

     I tried the following, for the first modification that I need:

    SUM(CORPAVAIL.[Qty Available] - CTSSHIP.RequiredQty ) OVER (PARTITION BY CTSSHIP.ItemNumber, CTSSHIP.LocationCode) as QtyAvailToShip

    That got me closer to what I need but not quite.  The first row's QtyAvailToShip totals correctly, but the subsequent rows do not.  You see, I need the subsequent rows' [RequiredQty] to subtract from the QtyAvailToShip.


    Any help would be much appreciated!  Thanks!

    Apitech

    select CTSSHIP.ItemNumber, CTSSHIP.ItemDescription, CTSSHIP.LocationCode, CTSSHIP.LocationDescription, CTSSHIP.Address1,
    CTSSHIP.Address2, CTSSHIP.City, CTSSHIP.State, CTSSHIP.ZipCode, CTSSHIP.RequiredQty,
    CORPAVAIL.ItemNumber, CORPAVAIL.LocationCode, CORPAVAIL.[Qty Available],
    (CORPAVAIL.[Qty Available] - CTSSHIP.RequiredQty) as QtyAvailToShip
     from CTSSHIP  
    INNER JOIN CORPAVAIL on CTSSHIP.ItemNumber = CORPAVAIL.ItemNumber
    where (CORPAVAIL.[Qty Available] - CTSSHIP.RequiredQty) > CTSSHIP.RequiredQty

    Friday, January 06, 2012 9:47 PM

Answers

  • Provided running substraction should be calculated wtih ordering by the location code just use the subquery

    (select c1.ItemNumber, c1.LocationCode, ...

    , sum(c2.RequiredQty ) as .RequiredQty
    from CTSSHIP c1

    inner join CTSSHIP c2 on c1.ItemNumber=c2.ItemNumber

    and c2.LocationCode <=c1.LocationCode

    group by c1.ItemNumber, c1.LocationCode, ...

    )

    instead of CTSSHIP  in your query

     


    Serg
    • Marked as answer by john.ellis Saturday, January 07, 2012 7:54 PM
    Saturday, January 07, 2012 5:01 PM

All replies

  • For the running subtraction, you need to run a subquery with SELECT SUM. It will not perform very well as the number of rows grow; execution times grows with the square of number of rows per ItemNumber and LocationCode. Unless, you by lucky chance are on SQL 2012 (not released yet) where you can use the new windwing functions. On SQL 2008, you may better performance with a cursor.

    Once you have this data, the WHERE clause should not be that difficult, although you may have to throuh a CTE (Common Table Expression) into the mix.

    For this type of problem it helps if you post

    o   CREATE TABLE statements for your tables (preferrably simplified to
        for the problem at hand.)
    o   INSERT statements with sample data.
    o   The desired result given the sample.
    o   Which version of SQL Server you are using.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 06, 2012 11:13 PM
  • Hi Erland:

    Below are the two views that my query is based upon.  I am using SQL 2008. 

    I do not see a way of attaching a spreadsheet or rows that will show you what I'm after.  But, here is a "summary" of what I need:

    Qty Available - RequiredQty = QtyAvailToShip for Row 1 and for Item A at Location 1,
    (QtyAvailToShip for Row 1 and for Item A at Location 1) - RequiredQty = QtyAvailToShip for Row 2 for Item A at Location 2,
    (QtyAvailToShip for Row 2 for Item A at Location 2) - RequiredQty = QtyAvailToShip for Row 3 for Item A at Location 3,
    (QtyAvailToShip for Row 3 for Item A at Location 3) - RequiredQty = QtyAvailToShip for Row 4 for Item A at Location 4,
    (QtyAvailToShip for Row 4 for Item A at Location 4) - RequiredQty = QtyAvailToShip for Row 5 for Item A at Location 5.

    CTSSHIP:

    select IV00102.ITEMNMBR as [ItemNumber], IV00101.ITEMDESC as [ItemDescription], IV00102.LOCNCODE as [LocationCode], IV40700.LOCNDSCR as [LocationDescription],
    IV40700.ADDRESS1 as [Address1], IV40700.ADDRESS2 as [Address2], IV40700.CITY as [City], IV40700.STATE as [State], IV40700.ZIPCODE as [ZipCode],
    IV00102.ORDRUPTOLVL-(IV00102.QTYONHND -IV00102.ATYALLOC) as [RequiredQty],
    IV00102.PRIMVNDR as [PrimaryVendor], IV00102.QTYONORD as [QtyOnOrder], IV00102.QTYONHND as [QtyOnHand], IV00102.ATYALLOC as [QtyAllocated],
    IV00102.ORDRPNTQTY as [OrderPointQty],
    IV00102.ORDRUPTOLVL as [OrderUpToLevel], IV00103.ECORDQTY as [EconcomicOrderQty],
    IV00102.QTYONHND-IV00102.ATYALLOC as [QtyAvailable], (IV00102.ORDRPNTQTY-(IV00102.QTYONHND-IV00102.ATYALLOC)) as [OrderQty]
     from TEST..IV00102
     INNER JOIN TEST..IV00101
     on
     IV00101.ITEMNMBR =  IV00102.ITEMNMBR
     INNER JOIN TEST..IV40700
     on
     IV40700.LOCNCODE =  IV00102.LOCNCODE
     INNER JOIN TEST..IV00103
     on
     IV00103.ITEMNMBR =  IV00102.ITEMNMBR and  IV00103.VENDORID =  IV00102.PRIMVNDR
    where
    IV00102.QTYONORD = '0'
     and IV00101.ITMCLSCD NOT IN ('INTERNAL', '')
     and IV00101.ITMGEDSC <> 'Literature'
     and IV00102.LOCNCODE NOT IN ('INTERNAL', 'DUMMY', 'VWCORP')
     and IV00102.ORDRPNTQTY > (IV00102.QTYONHND-IV00102.ATYALLOC)
     

    CORPAVAIL:

    select IV00102.ITEMNMBR as [ItemNumber], IV00101.ITEMDESC as [ItemDescription], IV00102.LOCNCODE as [LocationCode], '' as [LocationDescription],
    '' as [Address1], '' as [Address2], '' as [City], '' as [State], '' as [ZipCode],
    0 as [RequiredQty],
    '' as [PrimaryVendor], 0 as [QtyOnOrder], IV00102.QTYONHND as [QtyOnHand], IV00102.ATYALLOC as [QtyAllocated],
    0 as [OrderPointQty],
    0 as [OrderUpToLevel], 0 as [EconcomicOrderQty],
    IV00102.QTYONHND-IV00102.ATYALLOC as [QtyAvailable], 0 as [OrderQty]
     from TEST..IV00102
     INNER JOIN TEST..IV00101
     on
     IV00101.ITEMNMBR =  IV00102.ITEMNMBR
     --INNER JOIN TEST..IV40700
     --on
     --IV40700.LOCNCODE =  IV00102.LOCNCODE
     --INNER JOIN TEST..IV00103
     --on
     --IV00103.ITEMNMBR =  IV00102.ITEMNMBR and  IV00103.VENDORID =  IV00102.PRIMVNDR
    where
    --IV00102.QTYONORD = '0'
     --and
     IV00101.ITMCLSCD NOT IN ('INTERNAL', '')
     and IV00101.ITMGEDSC <> 'Literature'
     and IV00102.LOCNCODE = 'VWCORP'
     --and IV00102.ORDRPNTQTY > (IV00102.QTYONHND-IV00102.ATYALLOC)

    Thanks!  Any help is appreciated!

    John

    <input id="b1b6e9de-951c-41f6-b163-b33a962e8857_attachments" type="hidden" />

    Saturday, January 07, 2012 3:47 PM
  • Provided running substraction should be calculated wtih ordering by the location code just use the subquery

    (select c1.ItemNumber, c1.LocationCode, ...

    , sum(c2.RequiredQty ) as .RequiredQty
    from CTSSHIP c1

    inner join CTSSHIP c2 on c1.ItemNumber=c2.ItemNumber

    and c2.LocationCode <=c1.LocationCode

    group by c1.ItemNumber, c1.LocationCode, ...

    )

    instead of CTSSHIP  in your query

     


    Serg
    • Marked as answer by john.ellis Saturday, January 07, 2012 7:54 PM
    Saturday, January 07, 2012 5:01 PM
  • Thank you, Serg!

    John

    Saturday, January 07, 2012 7:54 PM
  • I do not see a way of attaching a spreadsheet or rows that will show you what I'm after.  But, here is a "summary" of what I need:

    I didn't ask for a spreadsheet. I asked for SQL statements that I can copy and paste into SSMS to develop a tested solution.

    Instead I will just give you the pattern, using a table from another current thread. This query just shows the running sum of supply (ignore that the date column is called ExpiryDate), and I only want to show rows where the running sum is above a certain limit.

    To compute the running sum I use a subquery. I have this in a CTE (Common Table Expression), and then query the CTE so I can filter the running sum.

    I need to add a word of caution: while the subquery is easy to write, it does not perform well. If you only have a couple of locations per item, it should work out. But if there are hundreds of them, it will not scale. In that case you need an iterative solution. Or upgrade to SQL 2012 which has better support for running sum.

    You may want to check out the thread
    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9223b7bb-c769-4cee-9162-df5a21157f37
    This problem is resemblent of yours, although if I understand you correctly, yours is a lot simpler.

    Here is a script. The first part, by the way, is the script I asked from you.

    SET NOCOUNT ON
    go
    IF OBJECT_ID('Supply', 'U') IS NOT NULL
      DROP TABLE Supply
    GO

    CREATE TABLE Supply
    (
        SupplyID INT
       ,MaterialNumber VARCHAR(5)
       ,ExpiryDate   DATE
       ,Quantity INT

    )
    GO
    INSERT INTO Supply VALUES (1, '11111','10/12/2011', 100)
    INSERT INTO Supply VALUES (2, '11111','10/18/2011', 700)
    INSERT INTO Supply VALUES (3, '11111','01/15/2012', 500)
    INSERT INTO Supply VALUES (4, '22222','11/18/2011', 250)
    INSERT INTO Supply VALUES (5, '22222','05/01/2012', 475)
    INSERT INTO Supply VALUES (6, '33333','12/17/2011', 200)
    INSERT INTO Supply VALUES (7, '33333','04/01/2012', 300)
    GO

    ;WITH Supply_accsum AS (
        SELECT a.MaterialNumber,
               (SELECT SUM(b.Quantity)
                FROM   Supply b
                WHERE  b.MaterialNumber = a.MaterialNumber
                  AND  b.ExpiryDate    <= a.ExpiryDate) AS RunningQty
        FROM   Supply a
    )
    SELECT *
    FROM   Supply_accsum
    WHERE  RunningQty > 300                                   


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, January 07, 2012 8:18 PM