Timings are quite an eye-opener. They do vary quite a bit, and some solutions vary more than others.

I took the average of six timings.

It is a tie between GianLuca and Dave Ballantyne. Because I can only award the competition to one person, I'm going to use as a tie-breaker the fact that the fastest timing in the whole run was by Dave Ballantyne at an amazing 2220 ms. He therefore is awarded the Phil factor Speed Phreak award, and sets the next competition. However, I've asked Red-Gate to award a second $60 Amazon Voucher to Gianluca for his wonderful entry 1a which was so close to Dave's average timings that one couldn't put a cigarette paper between them.

Matt Whitfield's late modification to his CLR comes in at 4300 ms, and gets an honourable mention, as does Scot Hauder (4620 ms) Steve(1a) with 4416 ms

The original run gave the following figures (I've used Matt's second CLR solution)....

**Secs       Ms Entry**
     3     2560 Dave Ballantyne - Phil Factor Challenge Entry 3.d
     2     2750 Gianluca 1a
     3     3050 Gianluca 2a
     3     3190 Dave Ballantyne - Phil Factor Challenge Entry 3.b
     4     3313 Matt (waiting for fixed version!)
     4     4360 Matt Whitfield CLR solution v2
     4     4560 Steve 1a
     5     5033 Scot Hauder
     6     5830 Herman 1d
     6     5906 RBarryYoung 2d Pure Set-Based, summary calcs
    10    10173 Andriy Z. 1
    16    16300 Dave Ballantyne Ver 2b
    41    41000 Dave Ballantyne Ver 1a
    62    61673 RBarryYoung_1b: Naive, Pure Set-Based Solution
   102   101430 Peso CURSOR (not an entry)
   111   111160 Herman, 30 Oct 2009 

Just to show you the sort of variations i was seeing, here is another sample run. In future, I'm going to judge the winner over at least 20 runs, and record the variance as well as the average time.

4   4236	Dave Ballantyne - Phil Factor Challenge Entry 3.b
4   4546	Gianluca 1a
5   4703	Dave Ballantyne - Phil Factor Challenge Entry 3.d
6   5566	Steve 1a
6   6190	Scot Hauder
7   7216	RBarryYoung_2d: Pure Set-Based, summary calcs
7   7450	Herman 1d
9   8610	Gianluca 2a
10  10296	Andriy Z. 1
18  17813	Dave Ballantyne Ver 2b
28  27500	Matt Whitfield CLR solution (V1)
42  42003	Dave Ballantyne Ver 1a
63  62803	RBarryYoung_1b: Naive, Pure Set-Based Solution
114 113116	 Herman, 30 Oct 2009
198 198136	Peso CURSOR (not an entry)

Sadly, I left the harness to watch a DVD, and when I got back to do the next lot of runs, windows had decided to upgrade itself and I hadn't saved the latest version, so I'm afraid the umpire's decision is now final

Final order over eight runs:

1. Dave Ballantyne - Phil Factor Challenge Entry 3.d
2. Gianluca 1a
3. Gianluca 2a
4. Dave Ballantyne - Phil Factor Challenge Entry 3.b
5. Steve 1a
6. Scot Hauder
7. Andriy Z. 1
8. Dave Ballantyne Ver 2b
9. RBarryYoung_2d: Pure Set-Based, summary calcs
10. Matt Whitfield CLR solution
11. Dave Ballantyne Ver 1a
12. RBarryYoung_1b: Naive, Pure Set-Based Solution
13. Herman 1d
14. Peso CURSOR (not an entry)
15. Herman, 30 Oct 2009
16. Dave Ballantyne 1

(I had to disqualify Matt as I haven't got an entry that gives the right answer)

I had some help with this one from a friend (thanks Mark!). it's just at a second on my machine. Let me know if I did anything wrong

/* Matt Clingan 11/5/2009 Phil Factor Challenge Entry Edition 1 */

--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--cleanup
--=--=-=-=-=-=-=-=-=-=-=-=-=-=-

IF  not EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Stock]') AND name = 'ix_StockWork_1') CREATE  INDEX ix_StockWork_1   on stock(ArticleID, stockid desc,trancode,price,items)
IF exists (select 1 from tempdb.sys.tables where name like '%#tt1%') DROP TABLE #tt1
IF exists (select 1 from tempdb.sys.tables where name like '%#tTemp%') DROP TABLE #tTemp
IF exists (select 1 from tempdb.sys.tables where name like '%#fifofinal%') DROP TABLE #FIFOFINAL
IF exists (select 1 from tempdb.sys.tables where name like '%#f%') DROP TABLE #f

--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--temp tables
--=--=-=-=-=-=-=-=-=-=-=-=-=-=-

create table #tt1 (articleid smallint,stockid int)
create table #tTemp(articleid smallint,stockid int, items  int,price  money)
create table #f(articleid smallint, finalitems int)
create table #fifoFinal (articleid smallint, stockid int, itemcount int, finalitems int, finalDollars money) 

insert into #tt1
select articleid,MAX(stockid)  as stockid 
from stock
where trancode='IN'  
group by articleid

insert into #f
select
articleid,SUM(case when trancode = 'OUT' then -items else items end) as finalitems 
from
stock 
group
by articleid 

insert into #fifofinal 
select 
    #f.articleid
    ,#tt1.stockid
    ,finalitems-sum(items) 
    ,finalitems,
    case when sum(items)>finalitems then finalitems else sum(items) end * max(price)
from #f left join #tt1 on #f.articleid = #tt1.articleid 
 join stock s on #tt1.articleid = s.articleid and #tt1.stockid <=s.stockid and s.trancode in ('IN','RET')
group by #f.articleid,#tt1.stockid,finalitems

select * from #fifofinal

I have a question regarding the rule for Returns. As follows:

You explain that because we can't track which original sales were being returned, therefore we always price returns at the price of the IN immediately preceding the return. However, this does not make sense in the case when there were no OUTS after the IN which immediately precedes the RET. In such a case, because we know that the RET could not have been of the items that just came IN. The rule should be "RET is priced at the price of the IN immediately preceding the OUT immediately preceding the RET."

This way RETs can be consistently ignored in the calculations, because only real INs count. And it is consistent with FIFO.

please advise

Even though under FIFO, current value is tracked at the more recent purchase prices, the value of goods already in inventory is not adjusted up or down just because another purchase is made at a different price. The premise of FIFO is not to track the value of current inventory at the latest, or most accurate basis. This is not market based cost tracking, or LCM. It's one of two accounting constructs designed for an environment where a) inventory value is tracked at cost, and b) subtractions from inventory cannot be tracked back to the exact point and price of entry. In such an environment, the arbitrary, yet consistent, assumption that First in First Out provides a method to consistently track value in an inherently inconsistent and unknown environment.

The problem with always using the latest IN price for returns is that in cases where no OUTS occurred after the latest IN, or where the number of items returned exceeds the number of OUT items that sold after the latest IN, the practice violates condition a) above - that value is tracked at cost.

Since RETs can only happen if there are OUTS, it makes sense to treat RETs as cancellations of the OUTS. And just like OUTS can span several INs (that's a premise of the rule), RETs can span, or cancel, several OUTs.

I'm not hung up over it, I can see how treating a RET as another purchase at the most recent price is consistent as well, but that complicates the query:)

Here's my entry. I'm calculating running count in reverse order and grouping INs and RETs based on their rank in the stock. Runs in 1.4 sec on my crappy laptop and requires "just" 53933 scans.

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


CREATE NONCLUSTERED INDEX IX_Stock_GS1 ON STOCK (ArticleID, StockID, TranCode) INCLUDE(Items)


;WITH RunItems(StockId, ArticleId, Items, Run_Items)
AS (
        SELECT StockId, 
                ArticleId, 
                Items,
                Run_Items = (
                        SELECT SUM(Items)
                        FROM Stock
                        WHERE ArticleId = S.ArticleId
                                AND StockId >= S.StockId
                                AND TranCode IN ('IN','RET')
                )
        FROM Stock S WITH(TABLOCKX)
        WHERE TranCode = 'IN'
),
FilteredStock (ArticleId, DiffItems, StockId, ResidualItems)
AS (
        SELECT *
        FROM (
                SELECT ArticleId, 
                        DiffItems = SUM(CASE Trancode WHEN 'OUT' THEN -A.Items ELSE A.Items END)
                FROM Stock AS A WITH(TABLOCKX)
                GROUP BY ArticleId
        ) AS T
        CROSS APPLY (
                SELECT TOP 1
                        StockId, 
                        ResidualItems = DiffItems - R.Run_Items + R.Items
                FROM RunItems AS R
                WHERE DiffItems <= Run_Items
                        AND ArticleId = T.ArticleId
                ORDER BY R.StockId DESC
        ) AS B
),
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 
                MinStockId = MIN(StockId),
                ArticleId,
                Items = SUM(PriceItems), 
                Price = SUM(ISNULL(Price, 0)),
                MIN(DiffItems) AS DiffItems
        FROM (
                SELECT 
                        StockId = N.StockId, 
                        ArticleId = N.ArticleId,
                        N.Price,
                        RN = CASE N.Trancode WHEN 'IN' THEN RN2 ELSE RN - RN2 END,
                        DiffItems,
                        ResidualItems,
                        PriceItems = 
                                CASE 
                                        WHEN N.StockId = B.StockID THEN ResidualItems 
                                        ELSE N.Items * CASE WHEN N.StockId > B.StockId THEN 1 ELSE 0 END
                                END
                FROM NumberedStock AS N
                CROSS APPLY (
                        SELECT *
                        FROM FilteredStock
                        WHERE ArticleId = N.ArticleId
                                AND StockId <= N.StockId
                ) AS B
        ) AS S
        GROUP BY ArticleId, RN 
)
SELECT ArticleId, 
        CurrentItems = MAX(DiffItems),
        CurrentValue = SUM(Items * Price)
FROM SummaryStock
GROUP BY ArticleId
ORDER BY ArticleId