Scot Hauder v1, 2 Nov 2009

I modified one index as shown below

CREATE NONCLUSTERED INDEX IX_Input 
ON dbo.Stock (TranCode, ArticleID, trandate desc)
INCLUDE (Items, Price)
WHERE TranCode IN ('IN', 'RET')

;WITH voodoomagick AS 
(
SELECT  ROW_NUMBER() OVER (PARTITION BY ArticleID ORDER BY TranDate) [r]
,ArticleID
,Items
,LastPrice
,(SELECT COALESCE(SUM(items),0) 
    FROM Stock s0 WITH (NOLOCK) 
    WHERE TranCode IN ('RET','IN') 
    AND s0.TranDate <= s1.TranDate  
    AND s0.ArticleID = s1.ArticleID) [in]

,(SELECT COALESCE(SUM(Items),0) 
    FROM Stock s0 WITH (NOLOCK) 
    WHERE TranCode = 'OUT' 
    AND s0.ArticleID=s1.ArticleID) [out]

    FROM Stock s1
CROSS APPLY
(SELECT TOP (1) Price [LastPrice] 
    FROM  Stock s3 WITH (NOLOCK)
    WHERE  s3.TranCode = 'IN'                   


    AND s3.ArticleID = s1.ArticleID 
    AND s3.TranDate <= s1.TranDate 
    ORDER BY TranDate DESC) [x1]

WHERE (SELECT COALESCE(SUM(Items),0) 
        FROM Stock s0 WITH (NOLOCK) 
        WHERE TranCode IN ('IN','RET') 
        AND s0.TranDate <= s1.TranDate 
        AND s0.ArticleID = s1.ArticleID)  

    >=(SELECT COALESCE(SUM(Items),0) 
        FROM Stock s0 WITH (NOLOCK) 
        WHERE TranCode = 'OUT' 
        AND s0.ArticleID = s1.ArticleID)

AND TranCode IN ('IN','RET')
)
SELECT  ArticleID
,MAX([in])-MAX([out]) [CurrentItems]
,SUM(CASE WHEN r = 1 THEN [in]-[out] ELSE Items END * LastPrice ) [CurrentValue]
FROM voodoomagick WITH (NOLOCK)
GROUP BY ArticleID
ORDER BY ArticleID

What do you think?
Am I allowed to compete, since all rules are known?

Please comment here what you think.

I can't find the Dave 3.a version in answer list.

Here is my cursor solution as counterweight to all set-based solutions :-)

-- Peso CURSOR    
CREATE TABLE    #Stage
    	(
    		ArticleID INT,
    		CurrentItems INT,
    		CurrentValue MONEY
    	)

DECLARE 	@ArticleID INT,
    	@PrevID INT = 0,
    	@Items INT,
    	@CurrItems INT,
    	@Price MONEY,
    	@LatestPrice MONEY,
    	@Away INT,
    	@Diff INT

DECLARE 	curYak CURSOR LOCAL FORWARD_ONLY FOR
    		SELECT		s.ArticleID,
    				s.Items,
    				s.Price,
    				COALESCE(w.Items, 0) AS Away
    		FROM		(
    					SELECT	ArticleID,
    						TranDate,
    						Items,
    						Price
    					FROM	dbo.Stock
    					WHERE	TranCode IN ('IN', 'RET')
    				) AS s
    		LEFT JOIN	(
    					SELECT		ArticleID,
    							SUM(Items) AS Items
    					FROM		dbo.Stock
    					WHERE		TranCode = 'OUT'
    					GROUP BY	ArticleID
    				) AS w ON w.ArticleID = s.ArticleID
    		ORDER BY	s.ArticleID,
    				s.TranDate

OPEN    curYak

FETCH   NEXT
FROM    curYak
INTO    @ArticleID,
    @Items,
    @Price,
    @Away

WHILE @@FETCH_STATUS = 0
    BEGIN
    	SELECT	@Diff =	CASE
    				WHEN @ArticleID > @PrevID THEN 0
    				WHEN @CurrItems > @Away THEN @CurrItems - @Away
    				ELSE @Diff
    			END,
    		@CurrItems =	CASE
    					WHEN @ArticleID > @PrevID THEN @Items
    					ELSE @CurrItems + @Items - @Diff
    				END,
    		@PrevID = @ArticleID,
    		@LatestPrice = COALESCE(@Price, @LatestPrice)

    	IF @CurrItems >= @Away
    		INSERT	#Stage
    			(
    				ArticleID,
    				CurrentItems,
    				CurrentValue
    			)
    		VALUES	(
    				@ArticleID,
    				@CurrItems - @Away,
    				(@CurrItems - @Away) * @LatestPrice
    			)

    	FETCH	NEXT
    	FROM	curYak
    	INTO	@ArticleID,
    		@Items,
    		@Price,
    		@Away
    END

CLOSE curYak
DEALLOCATE curYak

SELECT  	ArticleID,
    	SUM(CurrentItems) AS CurrentItems,
    	SUM(CurrentValue) AS CurrentValue
FROM    	#Stage
GROUP BY    ArticleID
ORDER BY    ArticleID

DROP TABLE  #Stage

Ok, well I would post a pure set based solution, except that the site is refusing to let me post the whole thing!! Lame.

Anyway, the SQL is just

EXEC [dbo].[FIFOStock]

It runs in about 6.3 seconds on my box.

There are two source files, one for the support class (mostly the same guts as the CLR type version, except for the fact that IBinarySerialize support is removed, and it is now class, rather than struct based).

FIFOStock Procedure:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void FIFOStock()
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("SELECT [ArticleID], CASE [TranCode] WHEN 'IN' THEN 1 WHEN 'OUT' THEN 2 ELSE 3 END, [Items], [Price] FROM [dbo].[Stock] ORDER BY [ArticleID], [TranDate]", connection))
            {
                short lastArticleID = -1;
                StockStack ss = new StockStack();

                // Create a record object that represents an individual row, including it's metadata.
                SqlDataRecord record =
                    new SqlDataRecord(new SqlMetaData[] {
                    new SqlMetaData("ArticleID", SqlDbType.Int),
                    new SqlMetaData("CurrentItems", SqlDbType.Int),
                    new SqlMetaData("CurrentValue", SqlDbType.Money),
                });

                SqlContext.Pipe.SendResultsStart(record);

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        short articleID = reader.GetInt16(0);
                        if (articleID != lastArticleID)
                        {
                            if (lastArticleID != -1)
                            {
                                // Populate the record.
                                record.SetInt32(0, lastArticleID);
                                record.SetInt32(1, ss.TotalCount);
                                record.SetSqlMoney(2, ss.TotalPrice);

                                SqlContext.Pipe.SendResultsRow(record);
                            }
                            ss = new StockStack();
                            lastArticleID = articleID;
                        }
                        int opCode = reader.GetInt32(1);
                        switch (opCode)
                        {
                            case 1:
                            {
                                ss.AddStock(reader.GetSqlMoney(3), reader.GetInt32(2));
                            } break;
                            case 2:
                            {
                                ss.RemoveStock(reader.GetInt32(2));
                            } break;
                            case 3:
                            {
                                ss.ReturnStock(reader.GetInt32(2));
                            } break;
                        }
                    }

                    // populate and emit the final record
                    record.SetInt32(0, lastArticleID);
                    record.SetInt32(1, ss.TotalCount);
                    record.SetSqlMoney(2, ss.TotalPrice);
                    SqlContext.Pipe.SendResultsRow(record);
                }
                SqlContext.Pipe.SendResultsEnd();
            }
        }
    }
}

StockStack class:

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

internal class StockStack
{
    public override string ToString()
    {
        StringBuilder sb = new StringBuilder(1024);
        foreach (StockStackItem ssi in _stackItems)
        {
            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 void ReturnStock(int Count)
    {
        _stackItems[_stackItems.Count - 1].ReturnStock(Count);
    }

    public void AddStock(SqlMoney Price, int Count)
    {
        _stackItems.Add(new StockStackItem((Decimal)Price, Count));
    }

    public void RemoveStock(int Count)
    {
        int itemsLeft = 0;
        bool itemEmpty = false;
        while (true)
        {
            _stackItems[0].RemoveStock(Count, out itemsLeft, out itemEmpty);

            if (itemEmpty)
            {
                _stackItems.RemoveAt(0);
            }
            if (itemsLeft == 0)
            {
                return;
            }
            Count = itemsLeft;
        }
    }

    public int TotalCount
    {
        get
        {
            int totalCount = 0;
            foreach (StockStackItem ssi in _stackItems)
            {
                totalCount += ssi.Count;
            }
            return totalCount;
        }
    }


    public SqlMoney TotalPrice
    {
        get
        {
            Decimal totalValue = 0;
            foreach (StockStackItem ssi in _stackItems)
            {
                totalValue += ssi.TotalValue;
            }
            return totalValue;
        }
    }

    public static StockStack GetInitialisedStack(SqlMoney Price, int Count)
    {
        StockStack ss = new StockStack();
        ss._stackItems.Add(new StockStackItem((Decimal)Price, Count));
        return ss;
    }

    public static StockStack Empty
    {
        get
        {
            return new StockStack();
        }
    }

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

    // List of items in the stock stack
    private List<StockStackItem> _stackItems = new List<StockStackItem>(4);

    private class StockStackItem
    {
        Decimal _stackItemValue;
        int _stackItemCount;

        public void ReturnStock(int Count)
        {
            _stackItemCount += Count;
        }

        public int Count
        {
            get
            {
                return _stackItemCount;
            }
        }

        public Decimal Value
        {
            get
            {
                return _stackItemValue;
            }
        }

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

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

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