## Herman, 30 Oct 2009 ##

Hi there, I decided to give it a try since I solved this problem for the stock market once before, using a slow cursor solution, and I always wanted to write it as a Set based solution.

This is my first attempt, and I am happy I got the answers right (I think), but I did not optimize it yet. It ran 86 secs on my laptop. I know I should how to do it different, but anyway decided to send this version.

--For my solution I need to create a function to determine the minimum of 4 values
IF (SELECT  OBJECT_ID('dbo.Min4')
   ) IS NOT NULL 
    DROP FUNCTION dbo.Min4 
GO

CREATE FUNCTION dbo.Min4(@V1 money,  @V2 money, @V3 money, @V4 money)
RETURNS Money
AS
BEGIN
  DECLARE @Min Money
  SET @Min = CASE WHEN @V1<@V2 THEN CASE WHEN @V1<@V3 THEN CASE WHEN @V1<@V4 THEN @V1 ELSE @V4 END 
                                                      ELSE CASE WHEN @V3<@V4 THEN @V3 ELSE @V4 END END 
                               ELSE CASE WHEN @V2<@V3 THEN CASE WHEN @V2<@V4 THEN @V2 ELSE @V4 END 
                                                      ELSE CASE WHEN @V3<@V4 THEN @V3 ELSE  @V4 END END END 

  RETURN @Min
END 
GO


WITH 
-- First generate a transaction order per aticle, the last transaction has TransOrder=1
  OrderedTransactions AS
 (SELECT ROW_NUMBER() OVER(PARTITION BY ArticleID ORDER BY TranDate DESC) as TranOrder,
         StockID,ArticleID, TranDate, TranCode, Items, Price
    FROM dbo.Stock),
--Calculate running totals and determine for every transaction the last date a price was defined for the article
 RunningTotals AS
 (SELECT Tr.*,Tot.*
   FROM OrderedTransactions as Tr
  CROSS APPLY (select SUM(case when trancode = 'OUT' then -Items else Items end) as CurrentItems,
                      SUM(case when trancode <> 'OUT' then Items else 0 end) as CurrentInItems,
                      MAX(case trancode when 'IN' then TranDate else CAST(0 as datetime) end) as LastPriceDate
          from OrderedTransactions as PrevTr
          where Tr.TranDate >= PrevTr.TranDate 
            and Tr.ArticleID = PrevTr.ArticleID
          group by ArticleId ) as Tot),
--Use LastPriceDate to determine the price of The 'RET' transactions
  PricedTotals AS
  (SELECT Tr.TranOrder, Tr.ArticleID, Tr.TranDate, Tr.TranCode, Tr.Items, 
          Tr.CurrentItems, Tr.CurrentInItems,
          case Tr.TranCode when 'IN' THEN Tr.Price 
                        when 'RET' THEN Pr.Price 
                        else 0 end as Price          
     FROM RunningTotals as Tr
     LEFT JOIN OrderedTransactions Pr
       on Tr.ArticleID = Pr.ArticleID
      and Tr.LastPriceDate = Pr.TranDate),
--Now match the 'OUT' transactionjs with their FIFO 'INI' and 'RET' transactions
  MatchedTransactions as    
  (SELECT Tr.TranOrder, Tr.ArticleID, Tr.TranDate, Tr.TranCode, Tr.Price, Tr.Items, Tr.CurrentItems,
          IT.TranOrder AS InTranOrder, IT.TranDate as InTranDate, IT.TranCode as InTranCode, IT.Price AS InPrice, IT.Items as InItems,
          case when Tr.TranCode = 'OUT' 
               then dbo.Min4(Tr.CurrentINItems-IT.CurrentINItems+IT.Items-Tr.CurrentItems,
    						 Tr.CurrentItems+Tr.Items - (Tr.CurrentINItems-IT.CurrentINItems),
    						 IT.Items,
    						 Tr.Items) 
          end as Usage
     FROM PricedTotals as Tr
     LEFT JOIN PricedTotals as IT
       on Tr.TranCode = 'OUT'
      and IT.TranCode <> 'OUT'
      and Tr.TranDate > IT.TranDate
      and Tr.ArticleID = IT.ArticleID
    where ((Tr.CurrentINItems-IT.CurrentINItems+IT.Items>Tr.CurrentItems
             and Tr.CurrentINItems-IT.CurrentINItems<=Tr.CurrentItems+Tr.Items) 
           OR Tr.TranCode <> 'OUT'))
--And finally calculate the SUM of the Value of all transactions, and get CurrentItems from the last transaction.
SELECT T.ArticleID, MAX(CASE WHEN T.TranOrder=1 THEN T.CurrentItems ELSE 0 END) as CurrentItems,
       SUM(CASE WHEN T.TranCode='OUT' 
            THEN (-T.Usage*T.InPrice)
            ELSE (T.Items*T.Price) END) as CurrentValueValue
  FROM MatchedTransactions T
 GROUP BY T.ArticleID
GO

DROP FUNCTION dbo.Min4

Hey, all! I've implemented simple approach learned from Celko DW challenge. Here it is:

    -- Andriy Z. 1
CREATE NONCLUSTERED INDEX IX_Input_2 ON dbo.Stock (TranCode, ArticleID,TranDate)
INCLUDE (Price) -- Remove comment for SQL Server 2005 and later
WHERE TranCode IN ('IN')   -- Remove comment for SQL Server 2008
go
select s.ArticleID
      ,CurrentItems = sum(Items * InOut.s)
      ,CurrentValue = sum(P.Price * Items * InOut.s)       
  from Stock s
       cross apply (
           select case when s.TranCode in('IN', 'Ret') then 1 else -1 end 
       )InOut(s)
       cross apply (
           select top 1 Price
             from Stock ss
            where ss.ArticleID = s.ArticleID
              and ss.TranCode = 'IN'
              and ss.TranDate <= s.TranDate
            order by ss.TranDate desc  
       )P
 group by
       s.ArticleID

Regards, Andriy Zabavskyy

Another try, with a quirky update. It doesn't perform as fast as the others, but it's another way to solve it quite quickly.

-- FIFO Stock Inventory
-- Gianluca 2a
-- 06 Nov 2009

CREATE TABLE #Summary (
    ArticleId int,
    RN tinyint,
    Items int,
    Price money,
    OutItems int,
    PRIMARY KEY CLUSTERED (ArticleId, RN)
)

;WITH
NumberedStock (StockId, ArticleId, TranCode, Items, Price, RN2, RN)
AS ( 
    SELECT *, 
    	RN = ROW_NUMBER() OVER (
    		PARTITION BY ArticleId
    		ORDER BY StockId
    	)
    FROM (	
    	SELECT  StockID,
    			ArticleID,
    			TranCode,
    			Items,
    			Price,
    			RN2 = ROW_NUMBER() OVER (
    				PARTITION BY ArticleId
    				ORDER BY StockId
    			)
    	FROM    Stock AS S WITH(TABLOCKX)
    	WHERE Trancode = 'IN' 
    	UNION ALL
    	SELECT  StockID,
    			ArticleID,
    			TranCode,
    			Items,
    			Price,
    			RN2 = ROW_NUMBER() OVER (
    				PARTITION BY ArticleId
    				ORDER BY StockId
    			)
    	FROM    Stock AS S WITH(TABLOCKX)
    	WHERE Trancode = 'RET' 
    ) AS A
), 
SummaryStock 
AS (
    SELECT 
    	ArticleId,
    	RN = CASE Trancode WHEN 'IN' THEN RN2 ELSE RN - RN2 END,
    	Items = SUM(Items), 
    	Price = SUM(ISNULL(Price, 0)),
    	OutItems = ISNULL((
    		SELECT SUM(Items)
    		FROM Stock
    		WHERE ArticleId = N.ArticleId
    			AND TranCode = 'OUT'
    	),0)
    FROM NumberedStock AS N
    GROUP BY ArticleId, CASE Trancode WHEN 'IN' THEN RN2 ELSE RN - RN2 END
)
INSERT INTO #Summary
SELECT * 
FROM SummaryStock


DECLARE @Items int = 0
DECLARE @ArticleId int = 0
DECLARE @OutItems int = 0

UPDATE #Summary
SET @Items = CASE WHEN @ArticleId = ArticleId THEN @Items + Items ELSE Items END,
    @ArticleId = ArticleId,
    OutItems = CASE WHEN OutItems <= @Items THEN CASE WHEN @Items - OutItems > Items THEN Items ELSE @Items - OutItems END ELSE 0 END


SELECT ArticleId, 
    CurrentItems = SUM(OutItems), 
    CurrentValue = SUM(OutItems * Price)
FROM #Summary
GROUP BY ArticleId

Herman 1d, 9 nov 2009

I was calculating much more than I was displaying, so I reconsidered my first algorithm to get to the requested result.
By using some new indexes, and optimizing the code so that it gets the price for RET transaction only when required, I was able to get my solution to work even faster.
The only thing to do is to somehow stop the RunningTotals to be calculated for transactions which are not needed in the final result.
I guess that is why some of you get amazing fast results.

I did some attempts to stop the RunningTotals when enough In transacrtions have been processed to cover the stock, but without success. I would never come up with Dave's top 1 (LastStockID) solution.
I did however improve my solution by replacing the SUM() OVER .. with SUM() GROUP BY. I initially expected the new SUM() OVER to be as efficient as the GROUP BY, but it turned out that it better to calculate totals per article and not per transaction. Which sound very reasonable actually...

I am using the same indexes:

CREATE NONCLUSTERED INDEX [IX_GetPrice] on [dbo].[Stock]([ArticleID] asc, [TranCode] , [TranDate] asc)
  include([Price]) 
  WHERE (TranCode='IN') 
go

CREATE NONCLUSTERED INDEX [IX_Transactions] on [dbo].[Stock]([ArticleID] asc ,[TranDate] desc)
  include([TranCode],[Items],[Price]) 
go

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

And here is my 1d solution

WITH  Transactions AS
 (SELECT ArticleID, TranDate, TranCode, Items, Price--, 
    FROM dbo.Stock),
 SummedTrans    AS
--Calculate the totals  for In and Out transactions 
 (SELECT ArticleID,
         SUM(CASE WHEN TranCode ='OUT' THEN 0 ELSE Items END) AS TotalsInItems,
         SUM(CASE WHEN TranCode ='OUT' THEN Items ELSE 0 END) AS TotalsOutItems
   FROM Transactions
  GROUP BY ArticleID),
 RunningTotals AS
-- Determine the corresponding In tranacions to be matched to an Out transaction
 (SELECT Tr.*, S.TotalsInItems, S.TotalsOutItems,
         CASE WHEN TranCode IN ('IN', 'RET') 
              THEN (SELECT SUM(NextTr.Items) FROM Transactions AS NextTr
                     WHERE Tr.TranDate <= NextTr.TranDate 
                       and Tr.ArticleID = NextTr.ArticleID
                       AND NextTr.TranCode IN ('IN', 'RET')
                     GROUP BY NextTr.ArticleID
                     HAVING SUM(NextTr.Items)-Tr.Items <= S.TotalsInItems-S.TotalsOutItems)
          END AS CurrentInItemsRev
   FROM Transactions as Tr
   inner join SummedTrans s
   ON Tr.ArticleID = S.ArticleID),
  MatchedTotals AS
--Match the In/Out transactions  for current Articles on stock
  (SELECT *,
      --CASE WHEN CurrentInItemsRev-Items <= TotalsInItems-TotalsOutItems
      --     THEN 
           CASE WHEN CurrentInItemsRev < TotalsInItems-TotalsOutItems THEN Items
                     ELSE -(CurrentInItemsRev-Items)+TotalsInItems-TotalsOutItems
           --      END
           --ELSE 0
       END Usage
     FROM RunningTotals 
    WHERE (CurrentInItemsRev-Items <= TotalsInItems-TotalsOutItems)),
  PricedTotals AS
-- Determine the value of the In transacions 
  (SELECT *, 
          CASE WHEN Price IS NOT NULL THEN Price
               ELSE (SELECT TOP 1 Price
                       FROM RunningTotals P
                      WHERE P.ArticleID = T.ArticleID
                        and P.TranCode ='IN'
                        AND P.TranDate < T.TranDate
                      ORDER BY P.TranDate DESC)
          END * Usage AS Value       
     FROM MatchedTotals T
    WHERE TranCode IN ('IN', 'RET') )  
--And Sum everything up
SELECT ArticleID, max(TotalsInItems-TotalsOutItems) as CurrentItems, sum(Value) as CurrentValue 
  FROM PricedTotals 
 GROUP BY ArticleID
 ORDER BY ArticleID
 GO

Now the query should be correct:

-- Andriy Z. 1 // still version 1, because first one was incorrect
create nonclustered index [IX_StockIdx] on [dbo].[Stock]([ArticleID] asc ,[TranDate] asc)
include([TranCode] ,[Items]) 

select si.ArticleID
      ,CurrentItems = sum(itemNums.val)
      ,CurrentValue = sum(si.Price * itemNums.val) 
  from Stock si
       outer apply (
           select sum(I)
             from (select case when sPrev.TranCode = 'IN' and sPrev.TranDate < si.TranDate then sPrev.Items
                               when sPrev.TranCode = 'IN' and sPrev.TranDate > si.TranDate then 0
                               when sPrev.TranCode = 'RET' then sPrev.Items  
                               when sPrev.TranCode = 'OUT' then -1 * sPrev.Items 
                          end
                     from Stock sPrev
                    where sPrev.ArticleID = si.ArticleID
                      and sPrev.TranDate <> si.TranDate
                  )Ax(i)              
       )prevB(val)
       cross apply (
           select case when (si.Items + isnull(prevB.val, 0) ) > 0 
                       then (si.Items + isnull(prevB.val, 0) )
                       else 0
                  end
       )itemNums(val)
 where si.TranCode = 'IN' 
 group by si.ArticleID 
 order by si.ArticleID

Regards, Andriy Z.