The ‘FIFO Stock Inventory’ SQL Problem

Question

Phil Factor SQL Speed Phreak Competition: No 2

alt text

This competition is now over, but the winner, Dave, got an Amazon Voucher for $60, and the privilege of being able to display the 'Phil Factor SQL Speed Phreak' award on their own site

It was quite a struggle with some close competition from many of those who participated in this competition. However, Dave came up with a clever solution that produced an FIFO calculation from a million rows in a just a few seconds. The third Phil Factor Speed Phreak competition will soon be held on here. Watch out!

(here is the original preamble.)

This competition is to calculate current items in stock and current stock value in FIFO order.
I'll tell you the business rules for the algorithm and provide a sample cursor-based routine that generates the correct result, but slowly (about 40 minutes).

I have seen many different algorithms to do this and most of them involve a cursor. Can it be done more quickly without, or even with, a cursor? In other words, what is the fastest way in SQL Server (any version) to provide this stock inventory report?
It is a reasonable request. We have a stock transaction list (1,000,001 records, 17.6MB zipped) with 15,002 unique articles and we need to do a stock inventory report that gives the current breakdown of the ArticleID, the number of items in stock and the current stock value according to FIFO rules. The list should be in ArticleID order. The sample stock transaction list will include two running totals so you can check the results of your routine. They are NOT part of the competition, and you are not allowed to use them in your calculation.

Beware that the sample file includes the two extra columns; CurrentItems (INT) and CurrentValue (MONEY) !
If you want a smaller subset to test on, import all records and remove all articles but two or three. These are the samples for which all suggestions will be measured with.

The table is in this form (simplified from the way we'd do it in a real system of course).
In this made-up but realistic problem, you have an existing table and database design which you can’t do anything about. (we can all relate to that!) You have an existing cursor-based solution that is taking several minutes to run. (Yes, we all relate to that). The database is performing badly, and you need to take effective steps to remedy this. The only thing you can do is to come up with a better-performing routine. Redesigning the database isn’t an option very often, in the real world, because this requires team sign-in. This is a competition based on the real, sometimes imperfect, world: not an exposition of database design. The point is that we are faced with designs like this and we have to heal them. The competition is all about making stuff go faster.

CREATE TABLE    dbo.Stock
                (
                    StockID INT IDENTITY(1, 1) NOT NULL,
                    ArticleID SMALLINT NOT NULL,
                    TranDate DATETIME NOT NULL,
                    TranCode VARCHAR(3) NOT NULL,
                    Items INT NOT NULL,
                    Price MONEY NULL,
                    CONSTRAINT [PK_Stock] PRIMARY KEY CLUSTERED 
                    (
                        StockID ASC
                    )
                )

CREATE NONCLUSTERED INDEX IX_Input ON dbo.Stock (TranCode, ArticleID)
--INCLUDE (TranDate, Items, Price) -- Remove comment for SQL Server 2005 and later
--WHERE TranCode IN ('IN', 'RET')   -- Remove comment for SQL Server 2008

CREATE NONCLUSTERED INDEX IX_Output ON dbo.Stock (TranCode, ArticleID)
-- INCLUDE (TranDate, Items) -- Remove comment for SQL Server 2005 and later
--WHERE TranCode = 'OUT'  -- Remove comment for SQL Server 2008

You are welcome to change the two nonclustered indexes to suit your solution. You can download the complete sample data here. I have an idea of my own of the way to do this but I don’t know if it is the fastest.

Explanation of FIFO rules (example, abbreviated)

StockID ArticleID TranDate TranCode Items  Price CurrentItems CurrentValue
   4567     10000 10:45:07 IN         738 245.94          738   181,503.72
  21628     10000 12:05:25 OUT        600                 138    33,939.72
  22571     10000 14:39:27 IN          62 199.95          200    46,336.62
  30263     10000 16:14:13 OUT        165                  35     6,998.25
  42090     10000 18:18:58 RET          5                  40     7,998.00
  58143     10000 20:18:54 IN         500 135.91          540    75,953.00

a) First IN add 738 items (each $245.94) to the stock, for a total of $181,503.72
b) Then we take out 600 items (each 245.94) from the stock, leaving a total of $33,939.72
c) Then we insert 62 items (each 199.95) to the stock, for a total of $46,336.62
d) Then we take out 165 items (138 each 245.94 and 27 each 199.95), leaving a total of $6,998.25
e) Then we return 5 items. We can’t track at which price we took them out; so all returns are priced at the price of the latest ones inserted before the return. Even if there should be items left for the price of 245.94, the returned items are valued for 199.95. After the return, the current stock value is $7,998.00
f) The final insert adds $67,995.00 to the stock value, for a total of $75,953.00

As mentioned before, the CurrentItems and CurrentValue columns in the sample data are only included for you to validate your routines.

Here are some guidelines for your entries:

1) Include a header in your suggestion. Make sure your name and date is present.
2) Include an edition number. First edition is 1. If you later improve your current suggestion post it again as version 2. Example: “Peso 1” and if improved, “Peso 1b”, “Peso 1c” etc.
3) If you are trying a new algorithm, change the edition to “Peso 2”. If you improve this algorithm, change the version to “Peso 2b”, “Peso 2c” etc. This will save Phil hours of work in the test harness!
4) If you create a temp table, make sure you delete it in the script.
5) Keep the order of columns in output as ArticleID, CurrentItems, CurrentValue

I will allow you to use an existing tally number table (make sure it starts with 0). You can use any kind of object for this competition, except SQLCLR. If you are using a fixed tally number table, it has to be named dbo.TallyNumbers and the column named Number.
The time taken for their creation will not be included in the timings. The time measured is the “Main” script/procedure. If you want to call sub-procedures, go ahead.

The winner will be amongst the tied fastest entrants (generally there is a group of these) and it will be the one with the highest number of votes. We'll announce the winner in three week's time on 16th November.

For a starter, here is a common cursor based solution that you will find in production in many places.

CREATE TABLE    #Work
                (
                    RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
                    Price MONEY
                )

DECLARE @ArticleID INT = 1,
        @PrevID INT = 0,
        @TranCode VARCHAR(3),
        @Items INT,
        @Price MONEY,
        @Loop INT = 0,
        @StockID INT,
        @LatestPrice MONEY,
        @Total INT = (SELECT COUNT(*) FROM dbo.Stock)

DECLARE curYak CURSOR FORWARD_ONLY FOR
                SELECT      ArticleID,
                            TranCode,
                            Items,
                            Price,
                            StockID
                FROM        dbo.Stock
                ORDER BY    ArticleID,
                            TranDate

OPEN    curYak

FETCH   NEXT
FROM    curYak
INTO    @ArticleID,
        @TranCode,
        @Items,
        @Price,
        @StockID

WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @ArticleID > @PrevID
            BEGIN
                TRUNCATE TABLE  #Work

SET @LatestPrice = NULL
            END

IF @TranCode = 'IN'
            BEGIN
                INSERT  #Work
                        (
                            Price
                        )
                SELECT  @Price
                FROM    dbo.TallyNumbers
                WHERE   Number < @Items

SET     @LatestPrice = @Price
            END

IF @TranCode = 'RET'
            INSERT  #Work
                    (
                        Price
                    )
            SELECT  @LatestPrice
            FROM    dbo.TallyNumbers
            WHERE   Number < @Items

IF @TranCode = 'OUT'
            DELETE  w
            FROM    (
                        SELECT      TOP(@Items)
                                    RowID
                        FROM        #Work
                        ORDER BY    RowID
                    ) AS w

UPDATE      s
        SET         s.CurrentItems = w.CurrentItems,
                    s.CurrentValue = COALESCE(w.CurrentValue, 0)
        FROM        dbo.Stock AS s
        INNER JOIN  (
                        SELECT  COUNT(*) AS CurrentItems,
                                SUM(Price) AS CurrentValue
                        FROM    #Work
                    ) AS w ON s.StockID = @StockID

SELECT  @PrevID = @ArticleID,
                @Loop += 1

IF @Loop % 1000 = 0
            RAISERROR('Now updating record %d of %d.', 10, 1, @Loop, @Total) WITH NOWAIT

FETCH   NEXT
        FROM    curYak
        INTO    @ArticleID,
                @TranCode,
                @Items,
                @Price,
                @StockID
    END

DROP TABLE  #Work

CLOSE       curYak
DEALLOCATE  curYak

The above is already done. Here is the code to produce the final resultset.

SELECT      ArticleID,
            CurrentItems,
            CurrentValue
FROM        (
                SELECT  ArticleID,
                        CurrentItems,
                        CurrentValue,
                        ROW_NUMBER() OVER (PARTITION BY ArticleID ORDER BY TranDate DESC) AS recID
                FROM    dbo.Stock
            ) AS d
WHERE       recID = 1
ORDER BY    ArticleID

Good luck to you all!

Peter Larsson

Accepted Answer

Dave Ballantyne - Phil Factor Challenge Entry 3.d

Peso had adding some index hints and shuffled a calculation to my initial 3.a. So this is 3.a(+) with comments

with 3.d doing the TotalStock sum in one operation shaves some more time off

“A problem well stated is a problem half solved” - Charles F. Kettering

Let us think about what has been asked within the challenge.

We have a warehouse which starts empty , the data contains the stock movements in (including returns) and out. The stock movements ALWAYS happen in a first in first out basis. With this we know that if the present stock level (sum(in's) - sum(out's) is 50 that will be the last 50 that have entered the warehouse. The key to this query is to efficiently find the cost of those 50 items.

    CREATE NONCLUSTERED INDEX [IX_Dave_General] ON [dbo].[Stock]
    (
            [ArticleID] ASC,
              [TranDate] DESC,
            [TranCode] ASC
    )
    INCLUDE ( [Items],
    [Price]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
    CREATE NONCLUSTERED INDEX [IX_Dave_Items] ON [dbo].[Stock]
    (
            [ArticleID] ASC,
            [TranDate] ASC
    )
    INCLUDE ( [Items])
    WHERE ([TranCode] IN ('IN', 'RET'))
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_Dave_Price] ON [dbo].[Stock]
    (
            [ArticleID] ASC,
            [TranDate] ASC
    )
    INCLUDE ( [Price])
    WHERE ([TranCode]='IN')
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]

GO

/* Sum up the ins and outs to calculate the remaining stock level */
;WITH cteStockSum 
AS      (

SELECT  ArticleID,
            SUM(CASE  WHEN TranCode = 'OUT' THEN 0-Items
                      ELSE Items END) AS TotalStock
            FROM  dbo.Stock
      GROUP BY  ArticleID
), 
/* Perform a rolling balance ( in reverse order ) through the stock movements in */
cteReverseInSum
AS      (
   SELECT  s.ArticleID,
           s.TranDate,
           (
               SELECT SUM(i.Items)
                 FROM dbo.Stock AS i WITH (INDEX (IX_Dave_Items))
                WHERE i.ArticleID = s.ArticleID
                  AND i.TranCode IN ('IN', 'RET')
                  AND i.TranDate >= s.TranDate
           /*   SELECT SUM(i.Items)
                 FROM dbo.vwStockIn as i with (index (idxStockInArtTrans))
                WHERE i.ArticleID = s.ArticleID
                  AND i.TranDate >= s.TranDate*/

) AS RollingStock,
           s.Items AS ThisStock
     FROM  dbo.Stock AS s
    WHERE  s.TranCode IN ('IN', 'RET')

), 
/* Using the rolling balance above find the first stock movement in that meets (or exceeds) our required stock level */
/* and calculate how much stock is required from the earliest stock in */
cteWithLastTranDate
AS      (
  SELECT   w.ArticleID,
           w.TotalStock,
           LastPartialStock.TranDate,
           LastPartialStock.StockToUse,
           LastPartialStock.RunningTotal,
           w.TotalStock - LastPartialStock.RunningTotal + LastPartialStock.StockToUse AS UseThisStock

FROM cteStockSum AS w
           CROSS APPLY  ( SELECT TOP(1)  
                                 z.TranDate,
                                 z.ThisStock AS StockToUse,
                                 z.RollingStock AS RunningTotal
                            FROM cteReverseInSum AS z
                           WHERE z.ArticleID = w.ArticleID
                             AND z.RollingStock >= w.TotalStock
                        ORDER BY z.TranDate DESC ) AS LastPartialStock

)
/*  Sum up the cost of 100% of the stock movements in after the returned stockid and
    for that stockid we need 'UseThisStock' items' */
SELECT y.ArticleID,
       y.TotalStock as CurrentItems ,
       SUM(CASE WHEN e.TranDate = y.TranDate THEN y.UseThisStock
                ELSE e.Items
                END * Price.Price) AS CurrentValue
  FROM cteWithLastTranDate AS y
 INNER JOIN  dbo.Stock AS e  WITH (INDEX (IX_Dave_Items)) 
    ON e.ArticleID = y.ArticleID
   AND e.TranDate >= y.TranDate
   AND e.TranCode IN ('IN', 'RET')
 CROSS APPLY  (
       /* Find the Price of the item in */
       SELECT TOP(1)        
              p.Price
         FROM dbo.Stock AS p WITH (INDEX (IX_Dave_Price))
        WHERE p.ArticleID = e.ArticleID
          AND p.TranDate <= e.TranDate
          AND p.TranCode = 'IN'
        ORDER BY p.TranDate DESC
      ) AS Price
GROUP BY  y.ArticleID,
          y.TotalStock
ORDER BY   y.ArticleID
go

Answer 2

RBarryYoung_1b: Naive, Pure Set-Based Solution

OK, just to get things rolling, here is a straight-forward Set-based solution, with no T-SQL tricks (though there is a very important algorithmic trick in cteOverlapValue).

CREATE PROC SscFIFO_RBarryYoung_1b AS
/*
    Naive pure Set-based solution to SSC FIFO inventory challenge.

- RBarryYoung, Oct-24, 2009
*/
;WITH
-- the base CTE, (just an easy way to Alias to other version of the test table)
 cteStock AS ( SELECT * FROM Stock )
-- Just the 'IN' records:
, cteStockIN AS ( 
    SELECT * 
    FROM cteStock
    WHERE TranCode = 'IN' 
    )
-- Fill in the Last IN records date for lookups later:
, cteLastINDate AS (
    SELECT * 
    , CASE TranCode WHEN 'IN' THEN TranDate
        ELSE (
            SELECT MAX(s3.TranDate)
            FROM cteStockIN s3
            WHERE s.ArticleID = s3.ArticleID
            AND  s.TranDate > s3.TranDate
            ) END AS LastINDate
    FROM cteStock s
    )
-- Carry-forward the last price for RET records:
, cteLastPriceDate AS (
    SELECT * 
    , COALESCE(Price, (
        SELECT Price 
        FROM cteStockIN s2
        WHERE s.ArticleID = s2.ArticleID
        AND  s.LastINDate =  s2.TranDate
        )) AS LastPrice
    FROM cteLastINDate s
    )
-- Build running sums of InItems and OutItems (same column, but separate counts)
, cteRunningItems AS (
    SELECT *
    , CASE WHEN TranCode IN('IN','RET') THEN (
            SELECT SUM(Items) 
            FROM cteLastPriceDate p2
            WHERE p2.TranCode IN('IN','RET')
            AND   p2.ArticleID = p.ArticleID
            AND   p2.TranDate <= p.TranDate
            )
        WHEN TranCode = 'OUT' THEN (
            SELECT SUM(Items) 
            FROM cteLastPriceDate p2
            WHERE p2.TranCode = 'OUT'
            AND   p2.ArticleID = p.ArticleID
            AND   p2.TranDate <= p.TranDate
            )
        END AS InOutItems
    , CASE WHEN TranCode IN('IN','RET') THEN (
            SELECT SUM(p2.Items*p2.LastPrice) 
            FROM cteLastPriceDate p2
            WHERE p2.TranCode IN('IN','RET')
            AND   p2.ArticleID = p.ArticleID
            AND   p2.TranDate <= p.TranDate
            ) END AS TotalValueIN
    FROM cteLastPriceDate p
    )
-- Map each OUT record to the (IN,RET) records that it will be consuming:
, cteOutMapIn AS (
    SELECT t1.*
    , t2.Items AS InItems
    , t2.LastPrice AS InPrice
    , t2.InOutItems AS TotalIn
    FROM cteRunningItems t1
    LEFT JOIN cteRunningItems t2
        ON  t1.ArticleID = t2.ArticleID
        AND t1.TranCode = 'OUT'
        AND t2.TranCode IN('IN','RET')
        AND t1.InOutItems-t1.Items <= t2.InOutItems
        AND t1.InOutItems >= t2.InOutItems-t2.Items
    )
-- Calculate the OUT consumed values, 
--based on how the OUT items overlap the (IN,RET) items:
, cteOverlapValue AS (
    SELECT *
    , CASE WHEN TranCode = 'OUT' THEN
        CASE 
            WHEN InOutItems >= TotalIn  AND (InOutItems-Items) <= (TotalIn-InItems)
                THEN InItems            -- OUT consumes all the INs
            WHEN InOutItems >= TotalIn  -- OUT consumes only the upper part of the INs
                THEN (TotalIn - (InOutItems-Items)) 
            WHEN (InOutItems-Items) <= (TotalIn-InItems)    -- OUT consumes the lower INs
                THEN (InOutItems - (TotalIn-InItems)) 
            ELSE Items  END             -- All OUTs consume an interior slice of IN
      END AS ItemsConsumed
    , CASE WHEN TranCode = 'OUT' THEN
        CASE
            WHEN InOutItems >= TotalIn  AND (InOutItems-Items) <= (TotalIn-InItems)
                THEN InItems * InPrice  -- OUT consumes all the INs
            WHEN InOutItems >= TotalIn  -- OUT consumes only the upper part of the INs
                THEN (TotalIn - (InOutItems-Items)) * InPrice
            WHEN (InOutItems-Items) <= (TotalIn-InItems)    -- OUT consumes the lower INs
                THEN (InOutItems - (TotalIn-InItems)) * InPrice
            ELSE Items * InPrice END    -- All OUTs consume an interior slice of IN
      END AS ConsumedValue
    FROM cteOutMapIn 
    )
-- Total everything up for each ArticleID:
, cteArticleTotals AS (
    SELECT t1.ArticleID
    , SUM( CASE WHEN TranCode='OUT' THEN -ItemsConsumed ELSE Items END ) AS CurrentItems
    , MAX( TotalValueIN ) - COALESCE(SUM( ConsumedValue ),0) AS CurrentValue
    FROM cteOverlapValue t1
    GROUP BY t1.ArticleID
    )
-- Output the results:
SELECT *
FROM cteArticleTotals t1
ORDER BY t1.ArticleID

~~Performance is not good, 9 seconds on the 5000 row (75 Articles) test set, and about 18 minutes on the full set.~~

Oops! I got those numbers completely wrong,...

In reality, performance is OK, but not great. it takes 9 seconds on 100,000 rows (about 1500 Articles) and 90 seconds on the full set. Not bad for virtually no optimizations.

1b: OK, I have made the corrections Requested by Peso (ORDER BY and Fixed NULLS on Article 25001). My current timings for it are 9sec on 100k rows and 95sec on 1M rows.

Answer 3

Well, I know this answer will be disqualified, but I thought it might be interesting to see how a CLR Type would help with this. Here is my code, which runs in about 45 seconds on my box. I also think the idea of a quirky update using a CLR type is funny!

CREATE ASSEMBLY [SqlClassLibrary]
AUTHORIZATION [dbo]
FROM
0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103002939E24A0000000000000000E00002210B0108000016000000060000000000006E340000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000203400004B000000004000006803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000074140000002000000016000000020000000000000000000000000000200000602E7273726300000068030000004000000004000000180000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001C000000000000000000000000000040000042000000000000000000000000000000005034000000000000480000000200050088260000980D0000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300200B5000000010000112000040000731300000A0A027B020000040B2B47027B06000004078F0300000271030000020C06120228110000066F1400000A260672010000706F1500000A2606120228100000060D1203281600000A6F1700000A26071758680B07027B0300000431B00672090000706F1500000A260602280900000613041204FE16040000016F1800000A6F1500000A260672110000706F1500000A260602280800000613051205281600000A6F1500000A26066F1800000A2A1E027B010000042A000000133005000702000002000011030440C8010000051308110817594503000000050000006D0000003401000038A00100000E04281900000A0A027B06000004060E0573140000068C0300000202257B030000041758682513097D0300000411096F1A00000A02257B040000040E05587D0400000402257B05000004060E05281B00000A281C00000A281D00000A7D050000040271020000022A027B06000004027B020000048F030000020E051201120228130000060D082C1102257B020000041758687D020000042B17027B06000004098C03000002027B020000046F1A00000A072D740216731E00000A7D0500000402167D04000004027B0200000413042B46027B0600000411048F030000027103000002130502257B0400000412052810000006587D0400000402257B0500000412052812000006281D00000A7D050000041104175813041104027B0300000431B00271020000022A0710053839FFFFFF027B06000004027B030000048F0300000271030000021306027B0600000412060E05280F0000068C03000002027B030000046F1A00000A02257B040000040E05587D0400000402257B05000004120628110000060E05281B00000A281C00000A281D00000A7D050000040271020000022A7219000070731F00000A7A1207FE15020000021207178D030000027D0600000412077B060000040E04281900000A0E0573140000068C03000002166F1A00000A11072A001330020012000000030000111200FE15020000021200177D01000004062AD2027B06000004027B06000004027B030000048F0300000203280F0000068C03000002027B030000046F1A00000A0271020000022A00133005003500000004000011027B0600000403281900000A0473140000068C0300000202257B03000004175868250A7D03000004066F1A00000A0271020000022A000000133004007300000005000011160A160B027B02000004027B03000004310B7237000070731F00000A7A027B06000004027B020000048F03000002031200120128130000060C072C1102257B020000041758687D020000042B17027B06000004088C03000002027B020000046F1A00000A062D070271020000022A0610012B911E027B040000042A32027B05000004282000000A2A1330040035000000030000111200FE15020000021200188D030000027D0600000412007B0600000402281900000A0373140000068C03000002166F1A00000A062A0000001330020017000000030000111200FE15020000021200168D030000027D06000004062A560F00282100000A2C0628040000062A280B0000062A000000133004009600000006000011036F2200000A0A020620800000005F16FE027D01000004061F7F5F0B020717588D030000027D0600000402071759687D03000004160C2B59036F2300000A0D036F2400000A130402257B040000041104587D0400000402257B050000041104281B00000A09281C00000A281D00000A7D05000004027B0600000409110473140000068C03000002086F1A00000A0817580C080732A32A0000133002006F00000007000011027B030000041758027B02000004591F7F5FD20A027B010000042C0906208000000058D20A03066F2500000A027B020000040B2B30027B06000004078F0300000271030000020C03120228110000066F2600000A03120228100000066F2700000A0717580B07027B0300000431C72A52027B07000004027B08000004035873140000062A1E027B080000042A1E027B070000042A5E027B07000004027B08000004281B00000A281C00000A2AF6027B0800000403311A041654051652027B07000004027B08000004035973140000062A0403027B08000004595405175216731E00000A1673140000062A3E02037D0700000402047D080000042A000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000004050000237E000070050000C805000023537472696E677300000000380B0000A400000023555300DC0B0000100000002347554944000000EC0B0000AC01000023426C6F6200000000000000020000015717A2030902000000FA013300160000010000001F0000000300000008000000140000001400000002000000270000000B000000070000000200000008000000080000000200000001000000020000000100000000000A000100000000000600480041000A0073005E000A0098007D000A00BD005E000A003A015E0006007301410006009D0193010600E001930106000603E70206004B03390306006203390306007F03390306009E0339030600B70339030600D00339030600EB03390306000604390306001F04E70206003304390306006C044C0406008C044C040600BA0441000A00D0047D000A00EC047D000600F304E70206000905E70206002005140506003505410006004605410006005905410006008C0541000000000001000000000001000100092110001E0000000500010001000B01100029000000050007000F0001004A014800010050014B0001005C014B00010067014E0001007B01510001008701550001004D02510001005D024E00502000000000C600A9000A000100112100000000E609B2000E0001001C21000000008600C600120001003023000000009608D4001D0006004E23000000008600DD00220006008423000000008600E90028000700C823000000008600F200220009004724000000008608FE0030000A004F240000000086080D0134000A005C240000000096001C0139000A00A02400000000960830011D000C00C324000000009600440141000C00DC2400000000E101AA015A000D00802500000000E101ED0160000E00FB25000000008600DD0078000F0010260000000086086D0230001000182600000000860877027E001000202600000000860881027E0010003826000000008600F20083001000762600000000861890028D00130000000100AD0200000200B70200000300C502000004009C0200000500960200000100960200000100CC0200000200960200000100960200000100CC0200000200960200000100D20200000100D40200000100D60200000100960200000100960202000200D802020003001303000001001B03000002002A030200090002000D001900DB015A0019001F026000490090029900510090029D00590090029D00610090029D00690090029D00710090029D00790090029D00810090029D00890090029D0091009002A200990090029D00A1009002A700A90090029900B10090029900B9009002AC00C9009002CD00D9009002A700D9002E05D300D9002E05DA00E100A9000A00D9003B05DA00E900A9000A0021004D05EC00F1005F05F30031006805F90031007405FF0031008005FF0031009002A700F90090029D00210068052A012900B2000E003900A60531013900AF057E003900BB05300041001F023E0141001F02430141001F02A7002E00430050012E007B008B012E002B0064012E00330064012E003B0064012E00230050012E004B006A012E00530064012E00630064012E007300820143008B00B200E00008011A011F01230135014901020001000300060000002502660000002C026A00000031026F0000003C027300000047026A00000096026F0000009C0294000000A202940002000200030002000400050002000800070002000900090002000B000B00020010000D00020011000F0002001200110002001A00030002001C0005000480000001000000FF0D94A3000000000000AA04000002000000000000000000000001003800000000000200000000000000000000000100520000000000030002000000003C4D6F64756C653E0053716C436C6173734C6962726172792E646C6C0053746F636B537461636B0053746F636B537461636B4974656D006D73636F726C69620053797374656D0056616C7565547970650053797374656D2E446174610053797374656D2E446174612E53716C547970657300494E756C6C61626C65004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A6500546F537472696E67006765745F49734E756C6C0053716C4D6F6E65790050726F636573734F70436F6465006765745F4E756C6C0052657475726E53746F636B0041646453746F636B0052656D6F766553746F636B006765745F546F74616C436F756E74006765745F546F74616C507269636500476574496E697469616C69736564537461636B006765745F456D7074790053716C537472696E67005061727365005F6E756C6C005F6669727374496E646578005F6C617374496E646578005F746F74616C436F756E7400446563696D616C005F746F74616C5072696365005F737461636B4974656D730053797374656D2E494F0042696E617279526561646572004D6963726F736F66742E53716C5365727665722E5365727665722E4942696E61727953657269616C697A652E5265616400526561640042696E617279577269746572004D6963726F736F66742E53716C5365727665722E5365727665722E4942696E61727953657269616C697A652E57726974650057726974650049734E756C6C004E756C6C00546F74616C436F756E7400546F74616C507269636500456D707479005F737461636B4974656D56616C7565005F737461636B4974656D436F756E74006765745F436F756E74006765745F56616C7565006765745F546F74616C56616C7565002E63746F7200436F756E740056616C756500546F74616C56616C75650041727469636C654944004C61737441727469636C654944004F70436F64650050726963650073007200770052656D61696E696E674974656D730053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465004973456D70747900537461636B4974656D56616C756500537461636B4974656D436F756E740053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C436C6173734C6962726172790053657269616C697A61626C654174747269627574650053716C55736572446566696E65645479706541747472696275746500466F726D6174005374727563744C61796F7574417474726962757465004C61796F75744B696E640053797374656D2E5465787400537472696E674275696C64657200417070656E6400496E74333200417070656E644C696E65004F626A656374006F705F4578706C696369740041727261790053657456616C7565006F705F496D706C69636974006F705F4D756C7469706C79006F705F4164646974696F6E00496E76616C69644F7065726174696F6E457863657074696F6E0052656164427974650052656164446563696D616C0052656164496E74333200000000000720007800200000072D002D002D00010720002D002000011D49006E00760061006C006900640020004F00700043006F00640065000069430061006E006E006F0074002000720065006D006F007600650020006D006F00720065002000730074006F0063006B002000660072006F006D002000740068006500200073007400610063006B0020007400680061006E0020006500780069007300740073002E00000000006DCC29ED161D2745988E398E2519A4500008B77A5C561934E0890320000E032000020A20051108080808111108040000110805200111080807200211081111080320000804200011110700021108111108060001110811150206020206060206080306111904061D110C05200101121D052001011221032800020408001108032800080428001111052001110C080420001119092003110C081008100206200201111908042800111903200001042001010E042001010204200101080520010111611A010002000000010054080B4D61784279746553697A65A00F0000052001011169062001126D1119052001126D0E0B0706126D06110C0811110806000111191111052002011C0805000111190808000211191119111911070A11190802110C08110C110C110808060407011108030701060607030802110C060001111111190320000508070505080811190804200101050520010111190607030508110C1301000E53746F636B537461636B54797065000005010000000017010012436F7079726967687420C2A920203230303900000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100004834000000000000000000005E340000002000000000000000000000000000000000000000000000503400000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000100300000000000000000000100334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE000001000000010094A3FF0D0000010094A3FF0D3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00470020000010053007400720069006E006700460069006C00650049006E0066006F0000004C020000010030003000300030003000340062003000000048000F000100460069006C0065004400650073006300720069007000740069006F006E0000000000530074006F0063006B0053007400610063006B0054007900700065000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003500380033002E00340031003800370036000000000048001400010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C006100730073004C006900620072006100720079002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003000390000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C006100730073004C006900620072006100720079002E0064006C006C00000040000F000100500072006F0064007500630074004E0061006D00650000000000530074006F0063006B0053007400610063006B0054007900700065000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003500380033002E00340031003800370036000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003500380033002E0034003100380037003600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000703400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
ALTER ASSEMBLY [SqlClassLibrary]
WITH VISIBILITY = ON
GO
CREATE TYPE [dbo].[StockStack]
EXTERNAL NAME [SqlClassLibrary].[StockStack]
GO
CREATE TABLE #StockWork (ArticleID [int] NOT NULL, TranDate [datetime] NOT NULL, OpCode [tinyint] NOT NULL, items [int] NOT NULL, Price [money] NOT NULL, IsFirst [bit] NOT NULL, totalItems [int] NOT NULL, totalPrice [money] NOT NULL)
CREATE CLUSTERED INDEX IX_StockWork_1 ON #StockWork (ArticleID, TranDate)
INSERT INTO #StockWork (ArticleID, TranDate, OpCode, Items, Price, IsFirst, totalItems, totalPrice)
SELECT [ArticleID],
       [TranDate],
       CASE [TranCode] WHEN 'IN' THEN 1 WHEN 'OUT' THEN 2 WHEN 'RET' THEN 3 END,
       [Items],
       [Price], 0, 0, 0.0 FROM [dbo].[Stock]

DECLARE @PrevArticleID [int], @PrevStack [dbo].[StockStack], @PrevIsFirst bit
SET @PrevArticleID = -1
SET @PrevStack = StockStack::GetInitialisedStack(0.0, 0);

UPDATE #StockWork
   SET @PrevIsFirst = IsFirst = CASE WHEN @PrevArticleID != ArticleID THEN 1 ELSE 0 END,
       @PrevStack = @PrevStack.ProcessOpCode(ArticleID, @PrevArticleID, OpCode, Price, Items),
       totalItems = @PrevStack.TotalCount,
       totalPrice = @PrevStack.TotalPrice,
       @PrevArticleID = ArticleID
  FROM #StockWork WITH(INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)

SELECT      ArticleID,
            CurrentItems,
            CurrentValue
FROM        (
                SELECT  ArticleID,
                        totalItems AS CurrentItems,
                        totalPrice AS CurrentValue,
                        ROW_NUMBER() OVER (PARTITION BY ArticleID ORDER BY TranDate DESC) AS recID
                FROM    #StockWork
            ) AS d
WHERE       recID = 1
ORDER BY    ArticleID

DROP TABLE #StockWork
GO
DROP TYPE [dbo].[StockStack]
GO
DROP ASSEMBLY [SqlClassLibrary]
GO

The source code for the assembly type is (no comments as yet - I might come back and fix this):

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Text;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize=4000)]
public struct StockStack : INullable, IBinarySerialize
{
    public override string ToString()
    {
        StringBuilder sb = new StringBuilder(1024);
        for (short i = _firstIndex; i <= _lastIndex; i++)
        {
            StockStackItem ssi = _stackItems[i];
            sb.Append(ssi.Value);
            sb.Append(" x ");
            sb.AppendLine(ssi.Count.ToString());
        }
        sb.Append("---");
        sb.Append(TotalPrice.ToString());
        sb.Append(" - ");
        sb.Append(TotalCount.ToString());
        return sb.ToString();
    }

public bool IsNull
    {
        get
        {
            return _null;
        }
    }

public StockStack ProcessOpCode(int ArticleID, int LastArticleID, int OpCode, SqlMoney Value, int Count)
    {
        if (ArticleID == LastArticleID)
        {
            switch (OpCode)
            {
                case 1:
                {
                    Decimal d = (Decimal)Value;
                    _stackItems.SetValue(new StockStackItem(d, Count), ++_lastIndex);
                    _totalCount += Count;
                    _totalPrice += d * Count;
                    return this;
                }
                case 2:
                {
                    int itemsLeft;
                    bool itemEmpty;
                    while (true)
                    {
                        StockStackItem newItem = _stackItems[_firstIndex].RemoveStock(Count, out itemsLeft, out itemEmpty);

if (itemEmpty)
                        {
                            _firstIndex++;
                        }
                        else
                        {
                            _stackItems.SetValue(newItem, _firstIndex);
                        }
                        if (itemsLeft == 0)
                        {
                            _totalPrice = 0;
                            _totalCount = 0;
                            for (int i = _firstIndex; i <= _lastIndex; i++)
                            {
                                StockStackItem ssi = _stackItems[i];
                                _totalCount += ssi.Count;
                                _totalPrice += ssi.TotalValue;
                            }
                            return this;
                        }
                        Count = itemsLeft;
                    }
                }
                case 3:
                {
                    StockStackItem ssi = _stackItems[_lastIndex];
                    _stackItems.SetValue(ssi.ReturnStock(Count), _lastIndex);
                    _totalCount += Count;
                    _totalPrice += ssi.Value * Count;
                    return this;
                }
                default:
                throw new InvalidOperationException("Invalid OpCode");
            }
        }
        else
        {
            StockStack ss = new StockStack();
            ss._stackItems = new StockStackItem[1];
            ss._stackItems.SetValue(new StockStackItem((Decimal)Value, Count), 0);
            return ss;
        }
    }

public static StockStack Null
    {
        get
        {
            StockStack ss = new StockStack();
            ss._null = true;
            return ss;
        }
    }

public StockStack ReturnStock(int Count)
    {
        _stackItems.SetValue(_stackItems[_lastIndex].ReturnStock(Count), _lastIndex);
        return this;
    }

public StockStack AddStock(SqlMoney Price, int Count)
    {
        _stackItems.SetValue(new StockStackItem((Decimal)Price, Count), ++_lastIndex);
        return this;
    }

public StockStack RemoveStock(int Count)
    {
        int itemsLeft = 0;
        bool itemEmpty = false;
        while (true)
        {
            if (_firstIndex > _lastIndex)
            {
                throw new InvalidOperationException("Cannot remove more stock from the stack than exists.");
            }
            StockStackItem newItem = _stackItems[_firstIndex].RemoveStock(Count, out itemsLeft, out itemEmpty);

if (itemEmpty)
            {
                _firstIndex++;
            }
            else
            {
                _stackItems.SetValue(newItem, _firstIndex);
            }
            if (itemsLeft == 0)
            {
                return this;
            }
            Count = itemsLeft;
        }
    }

public int TotalCount
    {
        get
        {
            return _totalCount;
        }
    }

public SqlMoney TotalPrice
    {
        get
        {
            return _totalPrice;
        }
    }

public static StockStack GetInitialisedStack(SqlMoney Price, int Count)
    {
        StockStack ss = new StockStack();
        ss._stackItems = new StockStackItem[2];
        ss._stackItems.SetValue(new StockStackItem((Decimal)Price, Count), 0);
        return ss;
    }

public static StockStack Empty
    {
        get
        {
            StockStack ss = new StockStack();
            ss._stackItems = new StockStackItem[0];
            return ss;
        }
    }

public static StockStack Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        // no implementation of from-string for this type
        return StockStack.Empty;
    }

// Nullability tracker
    private bool _null;

private short _firstIndex;
    private short _lastIndex;

int _totalCount;
    Decimal _totalPrice;

// List of items in the stock stack
    private StockStackItem[] _stackItems;

private struct StockStackItem
    {
        Decimal _stackItemValue;
        int _stackItemCount;

public StockStackItem ReturnStock(int Count)
        {
            return new StockStackItem(_stackItemValue, _stackItemCount + Count);
        }

public int Count
        {
            get
            {
                return _stackItemCount;
            }
        }

public Decimal Value
        {
            get
            {
                return _stackItemValue;
            }
        }

public Decimal TotalValue
        {
            get
            {
                return _stackItemValue * _stackItemCount;
            }
        }

public StockStackItem RemoveStock(int Count, out int RemainingItems, out bool IsEmpty)
        {
            if (_stackItemCount > Count)
            {
                RemainingItems = 0;
                IsEmpty = false;
                return new StockStackItem(_stackItemValue, _stackItemCount - Count);
            }
            else 
            {
                RemainingItems = Count - _stackItemCount;
                IsEmpty = true;
                return new StockStackItem(0, 0);
            }
        }

public StockStackItem(Decimal StackItemValue, int StackItemCount)
        {
            _stackItemValue = StackItemValue;
            _stackItemCount = StackItemCount;
        }
    }

#region IBinarySerialize Members

void IBinarySerialize.Read(System.IO.BinaryReader r)
    {
        byte b = r.ReadByte();
        _null = (b & 0x80) > 0;
        int stackCount = b & 0x7F;
        _stackItems = new StockStackItem[stackCount + 1];
        _lastIndex = (short)(stackCount - 1);
        for (int i = 0; i < stackCount; i++)
        {
            Decimal value = r.ReadDecimal();
            int count = r.ReadInt32();
            _totalCount += count;
            _totalPrice += count * value;
            _stackItems.SetValue(new StockStackItem(value, count), i);
        }
    }

void IBinarySerialize.Write(System.IO.BinaryWriter w)
    {
        byte b = (byte)(((_lastIndex + 1) - _firstIndex) & 0x7F);
        if (_null)
        {
            b += 0x80;
        }
        w.Write(b);
        for (int i = _firstIndex; i <= _lastIndex; i++)
        {
            StockStackItem ssi = _stackItems[i];
            w.Write(ssi.Value);
            w.Write(ssi.Count);
        }
    }

#endregion
}

Answer 4

Not finished yet , i think there is some more to come. Could you tell me what the performance is like against the 'test' system ? Just for reference i stopped Barry's running after 5 Minutes , this completes in a little over a minute.

drop table #inData
go
drop table #outData
go
Create table #InData(
ArticleId integer   not null,
StockId   integer   not null,
Items     integer   not null,
Price     money        not null,
RollingCount integer null)
go
Create unique clustered index #idxinData on #InData(ArticleId,StockID)
go

Create table #OutData(
ArticleId integer   not null,
StockId   integer   not null,
Items     integer   not null,
RollingCount integer null)
go
Create unique clustered index #idxoutData on #outData(ArticleId,StockID)
go
Drop Table  #OutStockAdjustment
go

Create Table #OutStockAdjustment
(
ArticleId integer,
OutStockId Integer,
ItemsOut   integer,
PriceTotal money
)

go

insert into #InData(ArticleId,StockId,Items,Price,RollingCount)
select Stock.ArticleId,Stock.StockId,Stock.Items,Price = isnull(Stock.Price,RetPrice.Price),NULL
from dbo.Stock  outer apply  (Select top 1 Price 
                               from dbo.Stock  innerstock
                               where InnerStock.StockId < Stock.StockId 
                                 and InnerStock.ArticleID = Stock.ArticleId
                                 and TranCode  ='In'
                                 and Stock.TranCode = 'Ret'
                              order by stockid desc)  as RetPrice

where TranCode in( 'IN','ret')
   --and ArticleId = 10000
go
Declare @RollingCount integer
Declare @RollingBalance money
Declare @LastArticleID integer
Select @LastArticleID = -1
Update #InData
  set @RollingCount = case when @LastArticleID <> ArticleId 
                           then Items
                           else Items +@RollingCount end,
      @RollingBalance = case when @LastArticleID <> ArticleId 
                               then Price * Items
                               else (Price * Items)+@RollingBalance end,
      RollingCount   = @RollingCount,
      @LastArticleId = ArticleId
 from #InData
go
insert into #OutData(ArticleId,StockId,Items,RollingCount)
Select ArticleId,StockId ,    Items ,Null
  from  dbo.Stock
 where  TranCode ='Out'
go
Declare @RollingCount integer
Declare @LastArticleID integer
Select @LastArticleID = -1
Update #OutData
  set @RollingCount = case when @LastArticleID <> ArticleId 
                           then Items
                           else Items +@RollingCount end,
      RollingCount   = @RollingCount,
      @LastArticleId = ArticleId
 from #OutData
go
delete from #OutStockAdjustment
go
with CteIndata(ArticleId,StockId,Items,Price,Item_id_lo,Item_id_Hi)
as
(
Select ArticleId,StockID,Items,Price,RollingCount - Items,RollingCount
  from #InData
)
,
CteOutdata(ArticleId,StockId,Items,Item_id_lo,Item_id_Hi)
as
(
Select ArticleId,StockID,Items,RollingCount - Items,RollingCount
  from #OutData
)
CteAudit
as
(
select CteIndata.ArticleId,
       CteInData.Price,
       CteIndata.StockId as InStockId,
       CteOutdata.StockId as OutStockId,
       case when CteIndata.Item_Id_Lo > CteOutdata.Item_Id_Lo then CteIndata.Item_Id_Lo else CteOutdata.Item_Id_Lo end as LoItemId,
       case when CteIndata.Item_Id_Hi < CteOutdata.Item_Id_Hi then CteIndata.Item_Id_Hi else CteOutdata.Item_Id_Hi end as HiItemId
       from 
CteIndata full join CteOutdata
 on CteIndata.ArticleId = CteOutdata.ArticleId
and( CteIndata.Item_Id_Lo between CteOutdata.Item_Id_lo and CteOutdata.Item_Id_hi
 or  CteOutdata.Item_Id_Lo between CteIndata.Item_Id_lo and CteIndata.Item_Id_hi)

)

insert #OutStockAdjustment(ArticleId,OutStockId,ItemsOut,PriceTotal)
Select ArticleId,OutStockId,sum(HiItemId-LoItemId)-1,SUM(Price * (HiItemId-LoItemId)-1)
from   CteAudit
GROUP by ArticleId,OutStockId

go
drop table  #Finalize
go
Create Table #Finalize(
StockId   integer  not null,
ArticleId integer  not null,
TranCode  varchar(3) not null,
Items     integer not null,
Price     money null,
RollingCount   integer null,
RollingBalance money null,
BalanceDecr    money null
)
go
create unique clustered index  #idxFinalize on #Finalize(ArticleId,StockId)
go
insert into #Finalize
(StockId,ArticleId,TranCode,Items,Price,RollingCount,RollingBalance,BalanceDecr)
select Stock.StockID,
       Stock.ArticleID,
       Stock.TranCode,
       Stock.Items,
       #InData.Price,
       null,
       null,
       Pricetotal
  from Stock
left join #InData
on  #InData.ArticleId =  Stock.ArticleId
and #InData.StockId =  Stock.StockId
left join #OutStockAdjustment
on  #OutStockAdjustment.ArticleId  = Stock.articleid
and #OutStockAdjustment.OutStockId = Stock.StockId
go

go
declare @RollingCount integer
Declare @RollingBalance money
declare @LastArticleId  integer
Select @LastArticleId =0
Select @RollingCount=0
select @RollingBalance = 0 
update #Finalize
  set  @RollingCount = case when @LastArticleId <> ArticleId then 0 else @RollingCount end + Case when TranCode = 'Out' then 0-Items else items end,
       @RollingBalance = ((isnull(Items,0)*isnull(Price,0))-ISNULL(BalanceDecr,0))+ case when @LastArticleId <> ArticleId then 0 else @RollingBalance end,
       RollingCount = @RollingCount,
       RollingBalance = @RollingBalance,
       @LastArticleId = ArticleId
 from #Finalize

go
select StockId ,    ArticleId,   TranCode, Items   ,    Price           ,      RollingCount, RollingBalance 
 from #Finalize
 where ArticleId = ArticleID
order by ArticleId,StockId

Answer 5

RBarryYoung_2d: Pure Set-Based, summary calcs

OK, similar to my first entry, but using a smarter way to calculate the final CurrentValues:

New version, 2c (don't ask about 2b :-)), primarily leveraging a new index....

OK, now we're really cooking with gas! Andriy's version spurred me to rethink certain parts of the query, resulting in a smaller and even fast version that finally uses the indexes the way that I always wanted it to.

First the new index to create before running:

    CREATE NONCLUSTERED INDEX IX_StockIN_RBY1 
    ON dbo.Stock (ArticleID, TranDate) 
    INCLUDE (Price) 
    WHERE TranCode = 'IN'

Andthe new version:

CREATE PROC [dbo].[SscFIFO_RBarryYoung_2d] AS
/*
    Set-based solution to SSC FIFO inventory challenge.

- RBarryYoung, Oct-26, 2009
    2009-10-26, RBarryYoung, smarter method, 3x faster: (shorter too)
                    use CurrentItems to calc CurrentVaules backwards
    2009-10-27, Use indexes that are better for this query, 
                    and fix LastPrice dual lookup problem
*/
-- Make sure the index exists:
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name='IX_StockIN_RBY1') 
    EXEC('CREATE NONCLUSTERED INDEX IX_StockIN_RBY1 ON dbo.Stock (ArticleID, TranDate) INCLUDE (Price) WHERE TranCode = ''IN''')

;WITH
-- the base CTE, (just an easy way to Alias to other version of the test table)
 cteStock AS ( SELECT ArticleID, TranDate, TranCode, Items, Price FROM StockX ) --WITH( INDEX (IX_All_RBY2) ) )
-- Fill in the date & price from Last IN record:
, cteLastPriceDate AS (
    SELECT * 
    FROM cteStock s
    )
-- Build running sums of InItems and OutItems 
, cteRunningItems AS (
    SELECT *
    , CASE TranCode WHEN 'IN' THEN Price 
        WHEN 'RET' THEN (
            SELECT TOP (1) s3.Price
            FROM cteStock s3 
            WHERE s3.ArticleID = t.ArticleID
            AND   s3.TranCode = 'IN' 
            AND   s3.TranDate < t.TranDate
            ORDER BY s3.TranDate DESC
            ) END AS LastPrice
    , CASE WHEN TranCode IN('IN','RET') THEN (
            SELECT SUM(Items) 
            FROM cteLastPriceDate p2
            WHERE p2.TranCode IN('IN','RET')
            AND   p2.ArticleID = t.ArticleID
            AND   p2.TranDate <= t.TranDate
            )
        END AS CurrentInItems
    FROM cteLastPriceDate t
    )
-- Total everything up for each ArticleID:
, cteArticleTotals AS (
    SELECT ArticleID
    , MAX(CurrentInItems) - MAX(COALESCE(TotalOutItems,0)) AS CurrentItems
    , SUM(CASE 
        WHEN CurrentInItems-Items >= COALESCE(TotalOutItems,0) THEN Items*LastPrice
        WHEN CurrentInItems >= TotalOutItems THEN (CurrentInItems-COALESCE(TotalOutItems,0))*LastPrice
        ELSE 0 END
        ) AS CurrentValue
    FROM cteRunningItems t
    CROSS APPLY (
        SELECT  SUM(s.Items) AS TotalOutItems
        FROM cteStock s
        WHERE   s.TranCode = 'OUT'
        AND     s.ArticleID = t.ArticleID
        ) s
    WHERE TranCode IN('IN','RET')
    GROUP BY ArticleID
    )
-- Output the results:
SELECT *
FROM cteArticleTotals
ORDER BY ArticleID

/* -- This should be run after:
    DROP INDEX IX_StockIN_RBY1 ON dbo.Stock;
*/

This is twice as fast as v2c (7 sec) on my machine.

Answer 6

For anyone having trouble loading the data file, here's the XML for a format file and a BULK INSERT statement that uses it.

1) Save this XML as fifo_format.xml:

<?xml version="1.0"?>
<BCPFORMAT
    xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
<!--StockID ArticleID    TranDate    TranCode    Items   Price   CurrentItems    CurrentValue-->
    <FIELD ID="StockID" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/> 
    <FIELD ID="ArticleID" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/> 
    <FIELD ID="TranDate" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20"/> 
    <FIELD ID="TranCode" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="5" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="Items" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/> 
    <FIELD ID="Price" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/> 
    <FIELD ID="CurrentItems" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/> 
    <FIELD ID="CurrentValue" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12"/> 
  </RECORD>
  <ROW>
    <COLUMN SOURCE="StockID" NAME="StockID" xsi:type="SQLINT"/>
    <COLUMN SOURCE="ArticleID" NAME="ArticleID" xsi:type="SQLSMALLINT"/>
    <COLUMN SOURCE="TranDate" NAME="TranDate" xsi:type="SQLDATETIME"/>
    <COLUMN SOURCE="TranCode" NAME="TranCode" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="Items" NAME="Items" xsi:type="SQLINT"/>
    <COLUMN SOURCE="Price" NAME="Price" xsi:type="SQLMONEY"/>
<!--
    <COLUMN SOURCE="CurrentItems" NAME="CurrentItems" xsi:type="SQLINT"/>
    <COLUMN SOURCE="CurrentValue" NAME="CurrentValue" xsi:type="SQLMONEY"/>
-->
  </ROW>
</BCPFORMAT>

2) Change the paths for the data file and format file as needed and execute this statement:

BULK INSERT dbo.Stock
    FROM 'e:\tom\fifo.txt'
    WITH 
    (
        FORMATFILE = 'e:\tom\fifo_format.xml',
        FIRSTROW = 2, -- first row has column headings
        KEEPIDENTITY
    )

Answer 7

/* Dave Ballantyne - 20091027 / / Ver 1a */

drop table #StockList
go
create table #StockList
(ArticleId integer not null ,
 StockId   integer not null, 
 Direction integer not null, 
 Items     integer not null,
 Price     money   null,
 RollingCount integer null,
 RollingBalance money null,
 LoCumItemsIn     integer null,
 HiCumItemsIn     integer null,
 LoCumItemsOut     integer null,
 HiCumItemsOut     integer null,
 OutTotalCost      money null,
 RowNum            integer null
)
go
Create Unique Clustered Index #IdxStockList on #StockList(ArticleId,StockId)
go
insert into #StockList(ArticleId,StockId,Direction,
                       Items ,Price , RollingCount, RollingBalance,RowNum)

select Stock.ArticleId,Stock.StockId,case when TranCode = 'Out' then -1 else 1 end ,Stock.Items,Stock.Price as PriceOut,null,null,
       ROW_NUMBER() over (partition by ArticleId order by StockId desc)
from dbo.Stock  
go
declare @RollingCount integer
declare @ArticleId    integer
Select @ArticleId =0
declare @CumulativeIn   integer
declare @CumulativeOut   integer
declare @LastInPrice     money

select @CumulativeOut = 0,
       @CumulativeIn =0,
       @RollingCount =0

update #StockList
   set @RollingCount  = (Direction * Items) + case when @ArticleId = ArticleId then @RollingCount else 0 end,
       @CumulativeIn  = case when  Direction <0 then case when @ArticleId = ArticleId then @CumulativeIn else 0 end else 
                           Items + case when @ArticleId = ArticleId then @CumulativeIn else 0 end
                         end,
       @CumulativeOut  = case when Direction >0 then case when @ArticleId = ArticleId then @CumulativeOut else 0 end else 
                           Items + case when @ArticleId = ArticleId then @CumulativeOut else 0 end
                         end,
       RollingCount = @RollingCount,
       HiCumItemsIn = case when Direction <0 then NULL else @CumulativeIn end,
       LoCumItemsIn = case when Direction <0 then NULL else (@CumulativeIn - Items)+1 end,
       HiCumItemsOut = case when Direction >0 then NULL else @CumulativeOut end,
       LoCumItemsOut = case when Direction >0 then NULL else (@CumulativeOut - Items)+1 end,
       Price = case when Price is not null then Price 
                    else case when Direction >0 then @LastInPrice else null end end,
       @LastInPrice =  case when Direction <0 or Price is null then @LastInPrice else Price end,

@ArticleId = ArticleId
go
with ctePreCalcOutPrice
as
(
select StockOut.StockId,StockIn.Price,
       case when StockOut.LoCumItemsOut>StockIn.LoCumItemsIn then StockOut.LoCumItemsOut else StockIn.LoCumItemsIn end as LoItem,
       case when StockOut.HiCumItemsOut<StockIn.HiCumItemsIn then StockOut.HiCumItemsOut else StockIn.HiCumItemsIn end as HiItem
  From #StockList StockOut join
       #StockList StockIn
   on  StockOut.ArticleId = StockIn.ArticleId
  and ( StockOut.LoCumItemsOut between StockIn.LoCumItemsIn and StockIn.HiCumItemsIn
  or    StockOut.HiCumItemsOut between StockIn.LoCumItemsIn and StockIn.HiCumItemsIn
  or    StockIn.LoCumItemsIn   between StockOut.LoCumItemsOut and  StockOut.HiCumItemsOut 
  or    StockIn.HiCumItemsIn   between StockOut.LoCumItemsOut and  StockOut.HiCumItemsOut ) 
  and   StockIn.StockId < StockOut.StockId
where StockOut.Direction <0
 and   StockIn.Direction >0

),
 cteSumTotal
 as
 (
 Select StockId,SUM(Price * ((HiItem-LoItem)+1)) as SumPrice
  from  ctePreCalcOutPrice
  group by StockId
 )
update StockList
  set  OutTotalCost  = SumPrice
 from  cteSumTotal,
       #StockList StockList
 where cteSumTotal.StockId = StockList.StockId

go
 declare @RollingBalance money
 declare @ArticleId      integer
 Select @RollingBalance =0
 select @ArticleId  = 0

update #StockList
    set @RollingBalance = (isnull(OutTotalCost,Price * Items)*Direction) +
                         case when @ArticleId =ArticleId 
                              then @RollingBalance else 0 end,
        RollingBalance = @RollingBalance,
        @ArticleId = ArticleId

go
select ArticleId,RollingCount as CurrentItems,RollingBalance as CurrentValue
from #StockList
where RowNum = 1
order by ArticleId

go

Answer 8

Here will the preliminary timings be presented. I write preliminary since Phil is (will be) away for a few days, so I will do the preliminary timings on a similar machine as Phil has.

Current standings

 1 - Dave 3d                       1.3 seconds
 2 - Dave 3c                       1.5 seconds
   - Matt 1a                       1.8 seconds (correct items, wrong value)
 3 - Dave 3b                       2.1 seconds
 4 - Gianluca 1a                   3   seconds
 5 - Steve 1a                      5   seconds
 6 - Gianluca 2a                   5   seconds
 7 - Scot 1                        6   seconds
 8 - Barry 2d                      7   seconds
   - Andriy 1a                     7   seconds (correct items, wrong value)
 9 - Herman 1d                     9   seconds
10 - Herman 1c                    12   seconds
   - Peso "Cursor"                12   seconds (not in competition)
11 - Barry 2c                     18   seconds
12 - Andriy 1c                    22   seconds
13 - Dave 2b                      26   seconds
   - Matt CLR                     44   seconds (not in competition) 
14 - Barry 2                      50   seconds   
15 - Dave 1b                      96   seconds
16 - Barry 1b                     97   seconds   
17 - Herman 1a                   172   seconds  
   - Article code             ~2,400   seconds (not in competition)

Answer 9

Dave Ballantyne ver 2.b -- Had a bit of a think and a eureka moment --- --- 2.a Had a bug with 0 valued articles not being shown

drop table tallynumbers
go
Create Table tallynumbers(
number integer
)
go
insert into tallynumbers
select top 100000 row_number() over(order by (select 1))
from syscolumns a cross join syscolumns b
go
create unique clustered index idxTally on tallynumbers(number)
go

with cteStockList
as
(
select ArticleId,
       ItemsIN=case when TranCode in('In','ret') then Items else 0 end,
       ItemsOUT=case when TranCode in('Out') then Items else 0 end
 from stock
-- where TranCode in('IN','RET')
--where ArticleId in( 10128)
),
cteStockSum
as
(
Select ArticleID,SUM(ItemsIn) as TotalItemsIn,SUM(ItemsOUT) as TotalItemsOut,SUM(ItemsIn) - SUM(ItemsOUT) as TotalStock
 from  cteStockList
group by ArticleID
)
,
CteStockLastItem
as
(
Select cteStockSum.ArticleID, TotalItemsIn, TotalItemsOut ,TotalStock,  LastIn.StockID ,    SumItems
 from cteStockSum 
 cross apply (Select top 1 Stock.ArticleId,
              Stock.StockID,
              SUM(InnerStock.Items) as SumItems
         from stock join 
              stock innerStock
           on innerStock.ArticleID = stock.ArticleID
          and InnerStock.StockId >=  stock.StockId
       where  innerStock.TranCode in('In','Ret')
          and Stock.TranCode in('In','Ret')
          and stock.ArticleID = cteStockSum.ArticleID
     group by Stock.ArticleId,Stock.StockID
        having SUM(InnerStock.Items)>=cteStockSum.TotalStock
          order by Stock.ArticleId,Stock.StockID  desc
          ) as LastIn
)
,
cteTallyList  
as
(
  select CteStockLastItem.*,stock.StockId as CalcStockId,case when TotalStock = 0 then NULL else ROW_NUMBER() over (Partition By stock.ArticleId order by stock.StockId desc) end as Rown
  from CteStockLastItem
   join stock 
     on  stock.ArticleId = CteStockLastItem.ArticleID
     and stock.StockId >=  CteStockLastItem.StockID
  left join TallyNumbers
    on TallyNumbers.Number <= Items
   and TotalStock > 0
  where TranCode in ('In','Ret')
)
,
ctePreCalc
as
(
select ArticleID, TotalItemsIn, TotalItemsOut ,TotalStock,CalcStockId,sum(case when Rown is not null then 1 else 0 end) as CountStock
 From cteTallyList
where Rown<=cteTallyList.TotalStock
 or   Rown is null
group by ArticleID, TotalItemsIn, TotalItemsOut ,TotalStock,CalcStockId
)

Select ctePreCalc.ArticleID, ctePreCalc.TotalStock as CurrentItems ,sum(Price*CountStock) as CurrentValue
 from ctePreCalc cross apply ( select top 1 Price
                                 from stock
                                where stock.StockId <=  ctePreCalc.CalcStockId
                                 and  stock.ArticleId = ctePreCalc.ArticleId
                                 and   TranCode='IN'
                                order by StockId desc ) as Price
group by ctePreCalc.ArticleID, ctePreCalc.TotalItemsIn, ctePreCalc.TotalItemsOut ,ctePreCalc.TotalStock
order by ctePreCalc.ArticleID

Answer 10

Essentially the same but added some comments , tidied it up , added an index

/* Dave Ballantyne - Phil Factor Challenge Entry 3.b*/
/* “A problem well stated is a problem half solved”  - Charles F. Kettering

Let us think about what has been asked within the challenge.

We have a warehouse which starts empty , the data contains the stock movements 
   in (including returns) and out.  The stock movements ALWAYS happen in a first in
   first out basis.  With this we know that if the present stock level (sum(in's) - sum(out's)
   is 50 that will be the last 50 that have entered the warehouse.  The key to this
   query is to efficiently find the cost of those 50 items.   */
go
Drop index Stock.IxStockDave 
go
Create NonClustered index IxStockDave on stock(ArticleID,StockId,TranCode) include(Items)
--  Not entirely neccesary but will shave of some time.
go

with cteStockList
as
(
select ArticleId,
       StockId,
       ItemsIN=case when TranCode in('In','ret') then items else  0 end,
       ItemsOUT=case when TranCode ='Out' then Items else 0 end
 from stock
),
cteStockSum
/* Sum up the ins and outs to calculate the remaining stock level */
as
(
Select ArticleID,
       SUM(ItemsIn) as TotalItemsIn,
       SUM(ItemsOUT) as TotalItemsOut,
       SUM(ItemsIn) - SUM(ItemsOUT) as TotalStock
 from  cteStockList
group by ArticleID
)
,
cteReverseInSum
/* Perform a rolling balance ( in reverse order ) through the stock movements in */
as
(
Select ArticleId,
       StockId,
       (Select SUM(Items)
          from Stock InnerStock
         where InnerStock.StockId >= Stock.StockId 
          and  InnerStock.TranCode in('In','Ret')
          and  InnerStock.ArticleID = Stock.ArticleID) as RollingStock,
       stock.items as ThisStock
  from stock
  where TranCode in('In','Ret')
)
,
/* Using the rolling balance above find the first stock movement in that meets (or exceeds) our required stock level */
cteWithLastStockId
as
(
select cteStockSum.ArticleId,
       cteStockSum.TotalStock,
       LastPartialStock.StockId,
       LastPartialStock.UseThisStock
  from cteStockSum cross apply ( Select Top 1 cteReverseInSum.StockId,
                                             cteReverseInSum.ThisStock as StockToUse,
                                             cteReverseInSum.RollingStock     as RunningTotal,
                                             TotalStock - (cteReverseInSum.RollingStock -ThisStock ) as UseThisStock
                                       from  cteReverseInSum
                                     where   cteStockSum.ArticleId = cteReverseInSum.ArticleId          
                                       and   cteStockSum.TotalStock <= cteReverseInSum.RollingStock 
                                    order by cteReverseInSum.StockId desc )  
                                    as LastPartialStock

)
/*  Sum up the cost of 100% of the stock movements in after the returned stockid and
    for that stockid we need 'UseThisStock' items' */
select cteWithLastStockId.ArticleId,
       cteWithLastStockId.TotalStock as CurrentItems ,
       sum(case when stock.stockId = cteWithLastStockId.StockId then cteWithLastStockId.UseThisStock else stock.Items end *
       Price.Price) as CurrentValue
  from cteWithLastStockId join stock
           on stock.ArticleId = cteWithLastStockId.ArticleId
          and stock.StockId  >= cteWithLastStockId.StockID
          and stock.TranCode in('In','Ret')
          /* Find the Price of the item in */
          cross apply(Select Top 1 innerstock.Price
                       from  stock innerstock 
                       where innerstock.ArticleId = stock.ArticleID
                        and  innerstock.StockId <=  stock.StockId
                        and  innerstock.TranCode ='In'
                        order by innerstock.StockId Desc) as Price

group by cteWithLastStockId.ArticleId,
         cteWithLastStockId.TotalStock
 order by cteWithLastStockId.ArticleId