x

The ‘FIFO Stock Inventory’ SQL Problem

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

more ▼

asked Oct 23, 2009 at 03:56 PM in Default

Peso gravatar image

Peso
1.6k 5 6 8

Was there any particular reason you disallowed CLR functionality?

On an initial look, this would be the ideal scenario in which to use a CLR type...
Oct 23, 2009 at 04:34 PM Peso
Two things had us made this decision: 1) We have tried CLR before and there was no performance gain. 2) To test and verify, poster need to disclode the source code for the suggestion. But if you have a CLR routine that runs on 15 seconds or less, I think Phil will be interested in testing it.
Oct 23, 2009 at 05:08 PM Peso
Questions: 1) Is the Stock Transaction List referred to the one that will be used for Final Evaluation? If not, can you describe the differences? 2) What is the configuration that entries will be (finally) evaluated on?
Oct 23, 2009 at 05:09 PM RBarryYoung
Yes, the downloadable file is the data against all suggestions will be measured. Phil will drop or rename the two last columns so that no suggestion can use them. I guess Phil will be using same test harness as last time with 'Subscription List' competition.
Oct 23, 2009 at 05:16 PM Peso
Those filtered indexes will only work on SQL Server 2008, right?
Oct 23, 2009 at 05:46 PM RBarryYoung
(comments are locked)
10|1200 characters needed characters left

28 answers: sort voted first

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  ) 
more ▼

answered Oct 25, 2009 at 11:24 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Nice collateral benefit to following the challenge. Thanks!
Oct 27, 2009 at 06:43 PM KenJ
(comments are locked)
10|1200 characters needed characters left

/* 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
more ▼

answered Oct 27, 2009 at 06:25 AM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

Please follow the guidelines when naming your suggestions. Is this a better edition 1? or a new edition?
Oct 27, 2009 at 09:45 AM Peso
@Peso : Slighty more streamlined than before. Dont think it'll get anywhere near Barrys best at the moment
Oct 27, 2009 at 10:14 AM dave ballantyne
(comments are locked)
10|1200 characters needed characters left

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)
more ▼

answered Oct 24, 2009 at 04:08 PM

Peso gravatar image

Peso
1.6k 5 6 8

Barry's timings on his solution varies due to a lot of tempdb space. But it is still a great suggestion.
Oct 26, 2009 at 08:51 AM Peso
What configuration are you testing on, Peso? I would think that my solution #2 would do a lot better than that on a multi-core environment. (Or is MAXDOP set down?)
Oct 26, 2009 at 08:58 PM RBarryYoung
It's probably my tempDB going through the roof. I'll try the suggestions on a better tempdb configuration. Your queries generates a lot of activity on the tempdbs.
Oct 27, 2009 at 09:44 AM Peso
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 28, 2009 at 07:48 AM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

Excellent. I just had to change "on TallyNumbers.Number <= Items" to "on TallyNumbers.Number between 1 and Items" because the TallyNumbers table starts with 0.
Oct 28, 2009 at 08:50 AM Peso
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 28, 2009 at 11:12 AM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

Now, this baby is flying!
Oct 28, 2009 at 02:21 PM Peso
2.873 seconds on my box, correct results too - excellent!
Oct 28, 2009 at 03:03 PM Matt Whitfield ♦♦
+1: Wow! Got the StockID subsumes TranDate trick to work, I see.
Oct 28, 2009 at 03:26 PM RBarryYoung
A little note however. The sample system depicted in the competition DOES have StockID in same order as TranDate. If your system doesn't follow same order, this will return wrong value! If your system allows to register future planned INs this will not work. For example you know you are getting 500 items next wednesday and you register them already today with wednesday's date.
Oct 28, 2009 at 05:12 PM Peso
I'd suggest you change all "StockID" to "TranDate". It will in fact run faster. With StockID you average at 2.0 seconds and uses 438k reads. With TranDate you average at 1.8 seconds and uses 243k reads.
Oct 28, 2009 at 05:24 PM Peso
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x343
x8
x7
x5

asked: Oct 23, 2009 at 03:56 PM

Seen: 28593 times

Last Updated: Nov 14, 2009 at 01:55 PM