none
FIFO Cost of Goods Sold

    Question

  • I thought I had this problem solved already, but turned out my solution returned wrong info and also I can not figure out what that solution is doing by looking at it. So I'll present the problem again and hopefully some bright minds can help me to solve it.

    We have an #Inventory table and I will only show 3 rows for now for simplicity of the problem

    USE [tempdb] GO /****** Object: Table [dbo].[Inventory] Script Date: 07/22/2013 11:56:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Inventory]( [ref_no] [numeric](17, 0) NOT NULL, [locatn_id] [int] NOT NULL, [date_time] [datetime] NOT NULL, [fifo_rank] [bigint] NULL, [InvNo] [bigint] NULL, [department] [char](10) NOT NULL, [category] [char](10) NOT NULL, [item] [char](10) NOT NULL, [invent_id] [int] NOT NULL, [trans_type] [char](1) NOT NULL, [quantity] [numeric](8, 2) NOT NULL, [unit_cost] [money] NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[Inventory] ([ref_no], [locatn_id], [date_time],

    [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type],

    [quantity], [unit_cost]) VALUES (CAST(53 AS Numeric(17, 0)), 1,

    CAST(0x0000A20000FF6D74 AS DateTime), 1, 1, N'RETAIL ',

    N'BK-CHILD ', N'DSCATTEST ', 0, N'P', CAST(40.00 AS Numeric(8, 2)), 10.0000) INSERT [dbo].[Inventory] ([ref_no], [locatn_id], [date_time],

    [fifo_rank], [InvNo], [department], [category], [item], [invent_id],

    [trans_type], [quantity], [unit_cost]) VALUES (CAST(53 AS Numeric(17, 0)), 1,

    CAST(0x0000A20000FF6D74 AS DateTime), 2, 1, N'RETAIL ',

    N'BK-CHILD ', N'DSCATTEST ', 0, N'P', CAST(40.00 AS Numeric(8, 2)), 5.0000) INSERT [dbo].[Inventory] ([ref_no], [locatn_id], [date_time],

    [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type],

    [quantity], [unit_cost]) VALUES (CAST(136005001 AS Numeric(17, 0)), 1,

    CAST(0x0000A200011967D8 AS DateTime), 3, 1, N'RETAIL ', N'BK-CHILD ',

    N'DSCATTEST ', 0, N'S', CAST(-50.00 AS Numeric(8, 2)), 0.0000)


    So, we have one item here (I added a new key InvNo so I don't have to deal with 4 columns (department, category, item, location)). Trans_Type column defines the type of the transaction (P - purchase, A - adjustment, T - transfer and S - sale).

    So, types P,A,T (when quantity >0) are movements in and S or negative quantity are movements out. 

    I also added Fifo_Rank column to order by datetime for each item

    My task is to find the cost of goods sold. In this particular scenario we purchased 40 items by $10, then again 40 by $5 and then sold 50 items. So, the cost of goods sold will be 40*10 + 10*5 = $450.

    I need solutions for both (SQL Server 2005-2008) and SQL Server 2012.

    Can you help with some ideas?

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, July 22, 2013 5:03 PM
    Moderator

All replies

  • Once upon a time, in another life, I had to program a FIFO solution in Oracle. You don't want to go through the entire FIFO table every time something is sold. Therefore, I added a column telling me how many items of that particular batch were still present. But that remark is not going to help you right now. I will have a look at it, but I cannot promise you anything. Give me an hour.
    Tuesday, July 23, 2013 1:13 PM
  • I would like to complicat the example. What if, after the first sale, you purchase another batch of 40 (at $ 7.50 per item) and then sell another 50.

    The cost of the second sales action is now30 * $5 + 20 * $7.50 = $300

    INSERT [dbo].[Inventory] ([ref_no], [locatn_id], [date_time], [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost]) VALUES (CAST(53 AS Numeric(17, 0)), 1, CAST(0x0000A20000FF6D74 AS DateTime), 1, 1, N'RETAIL    ', N'BK-CHILD  ', N'DSCATTEST ', 0, N'P', CAST(40.00 AS Numeric(8, 2)), 10.0000)
    INSERT [dbo].[Inventory] ([ref_no], [locatn_id], [date_time], [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost]) VALUES (CAST(53 AS Numeric(17, 0)), 1, CAST(0x0000A20000FF6D74 AS DateTime), 2, 1, N'RETAIL    ', N'BK-CHILD  ', N'DSCATTEST ', 0, N'P', CAST(40.00 AS Numeric(8, 2)), 5.0000)
    INSERT [dbo].[Inventory] ([ref_no], [locatn_id], [date_time], [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost]) VALUES (CAST(136005001 AS Numeric(17, 0)), 1, CAST(0x0000A200011967D8 AS DateTime), 3, 1, N'RETAIL    ', N'BK-CHILD  ', N'DSCATTEST ', 0, N'S', CAST(-50.00 AS Numeric(8, 2)), 0.0000)
    INSERT [dbo].[Inventory] ([ref_no], [locatn_id], [date_time], [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost]) VALUES (CAST(54 AS Numeric(17, 0)), 1, CAST(0x0000A200011967DA AS DateTime), 4, 1, N'RETAIL    ', N'BK-CHILD  ', N'DSCATTEST ', 0, N'P', CAST(40.00 AS Numeric(8, 2)), 7.5000)
    INSERT [dbo].[Inventory] ([ref_no], [locatn_id], [date_time], [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost]) VALUES (CAST(136005002 AS Numeric(17, 0)), 1, CAST(0x0000A200011967DE AS DateTime), 5, 1, N'RETAIL    ', N'BK-CHILD  ', N'DSCATTEST ', 0, N'S', CAST(-50.00 AS Numeric(8, 2)), 0.0000)


    • Edited by Chris Sijtsma Tuesday, July 23, 2013 1:22 PM forgot some info
    Tuesday, July 23, 2013 1:22 PM
  • This is probably not what you are looking for because I used a cursor (no window functions in SQL 2005, so nu running total), and because I changed the structure of your FIFO table. I added the "quantity_present" column. But for what it is worth, here is my solution (Be careful, no PK and no proper indexes added).

    USE Naomi
    GO
    IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Inventory') DROP TABLE dbo.Inventory;
    IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'DoSell') DROP PROC dbo.DoSell;
    GO
    /****** Object:  Table dbo.Inventory    Script Date: 07/22/2013 11:56:09 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE dbo.Inventory
    ( ref_no            numeric(17, 0)  NOT NULL
    , locatn_id         int             NOT NULL
    , date_time         datetime        NOT NULL
    , fifo_rank         bigint          NULL
    , InvNo             bigint          NULL
    , department        char(10)        NOT NULL
    , category          char(10)        NOT NULL
    , item              char(10)        NOT NULL
    , invent_id         int             NOT NULL
    , trans_type        char(1)         NOT NULL
    , quantity          numeric(8, 2)   NOT NULL
    , unit_cost         money           NOT NULL
    , quantity_present  numeric(8, 2)   NOT NULL
    );
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT dbo.Inventory (ref_no, locatn_id, date_time, fifo_rank, InvNo, department, category, item, invent_id, trans_type, quantity, unit_cost, quantity_present) VALUES (CAST(53 AS Numeric(17, 0)), 1, CAST(0x0000A20000FF6D74 AS DateTime), 1, 1, 'RETAIL    ', 'BK-CHILD  ', 'DSCATTEST ', 0, 'P', CAST(40.00 AS Numeric(8, 2)), 10.0000, 0.00)
    INSERT dbo.Inventory (ref_no, locatn_id, date_time, fifo_rank, InvNo, department, category, item, invent_id, trans_type, quantity, unit_cost, quantity_present) VALUES (CAST(53 AS Numeric(17, 0)), 1, CAST(0x0000A20000FF6D74 AS DateTime), 2, 1, 'RETAIL    ', 'BK-CHILD  ', 'DSCATTEST ', 0, 'P', CAST(40.00 AS Numeric(8, 2)), 5.0000, 30.00)
    INSERT dbo.Inventory (ref_no, locatn_id, date_time, fifo_rank, InvNo, department, category, item, invent_id, trans_type, quantity, unit_cost, quantity_present) VALUES (CAST(136005001 AS Numeric(17, 0)), 1, CAST(0x0000A200011967D8 AS DateTime), 3, 1, 'RETAIL    ', 'BK-CHILD  ', 'DSCATTEST ', 0, 'S', CAST(-50.00 AS Numeric(8, 2)), 0.0000, 0.00)
    INSERT dbo.Inventory (ref_no, locatn_id, date_time, fifo_rank, InvNo, department, category, item, invent_id, trans_type, quantity, unit_cost, quantity_present) VALUES (CAST(54 AS Numeric(17, 0)), 1, CAST(0x0000A200011967DA AS DateTime), 4, 1, 'RETAIL    ', 'BK-CHILD  ', 'DSCATTEST ', 0, 'P', CAST(40.00 AS Numeric(8, 2)), 7.5000, 40.00)
    INSERT dbo.Inventory (ref_no, locatn_id, date_time, fifo_rank, InvNo, department, category, item, invent_id, trans_type, quantity, unit_cost, quantity_present) VALUES (CAST(54 AS Numeric(17, 0)), 1, CAST(0x0000A200011967DA AS DateTime), 5, 1, 'RETAIL    ', 'BK-CHILD  ', 'DSCATTEST ', 0, 'P', CAST(40.00 AS Numeric(8, 2)), 12.5000, 40.00)
    GO
    
    CREATE PROCEDURE dbo.DoSell
    ( @ref_no      numeric(17, 0)
    , @locatn_id   int
    , @date_time   datetime
    , @InvNo       bigint
    , @department  char(10)
    , @category    char(10)
    , @item        char(10)
    , @invent_id   int
    , @quantity    numeric(8, 2)
    , @cost_sales  money          OUTPUT
    ) AS
    BEGIN
        DECLARE @cr_fifo_rank         bigint
    	,       @cr_quantity_present  numeric(8, 2)
    	,       @cr_unit_cost         money
    	,       @quantity_needed      numeric(8, 2);
    
        SET @quantity_needed = @quantity;
    	SET @cost_sales = 0;
    
        DECLARE cr_ActiveFIFO INSENSITIVE CURSOR FOR
            SELECT fifo_rank
            ,      quantity_present
            ,      unit_cost
            FROM   dbo.Inventory
            WHERE  trans_type = 'P'
            AND    quantity_present > 0
            ORDER  BY fifo_rank;
    
    	OPEN cr_ActiveFIFO;
    
    	FETCH cr_ActiveFIFO
    	INTO @cr_fifo_rank
    	,    @cr_quantity_present
    	,    @cr_unit_cost;
    
        WHILE (@@FETCH_STATUS = 0) AND (@quantity_needed > 0)
    	BEGIN
            IF @cr_quantity_present <= @quantity_needed
    		BEGIN
    		    UPDATE dbo.Inventory
    			SET    quantity_present = 0
    			WHERE  fifo_rank = @cr_fifo_rank;
    
    			SET @quantity_needed = @quantity_needed - @cr_quantity_present;
    			SET @cost_sales = @cost_sales + (@cr_unit_cost * @cr_quantity_present);
    		END
    		ELSE
    		BEGIN
    		    UPDATE dbo.Inventory
    			SET    quantity_present = quantity_present - @quantity_needed
    			WHERE  fifo_rank = @cr_fifo_rank;
    
    			SET @quantity_needed = 0
    			SET @cost_sales = @cost_sales + (@cr_unit_cost * @quantity_needed);
    		END;
    
    		FETCH cr_ActiveFIFO
    		INTO @cr_fifo_rank
    		,    @cr_quantity_present
    		,    @cr_unit_cost;
    	END;
    
    	CLOSE cr_ActiveFIFO;
    
        DEALLOCATE cr_ActiveFIFO;
    
        INSERT dbo.Inventory
    	(      ref_no
    	,      locatn_id
    	,      date_time
    	,      fifo_rank
    	,      InvNo
    	,      department
    	,      category
    	,      item
    	,      invent_id
    	,      trans_type
    	,      quantity
    	,      unit_cost
    	,      quantity_present
    	)
    	SELECT ref_no           = @ref_no
    	,      locatn_id        = @locatn_id
    	,      date_time        = @date_time
    	,      fifo_rank        = (SELECT ISNULL(MAX(fifo_rank), 0) + 1 FROM dbo.Inventory)
    	,      InvNo            = @InvNo
    	,      department       = @department
    	,      category         = @category
    	,      item             = @item
    	,      invent_id        = @invent_id
    	,      trans_type       = 'S'
    	,      quantity         = -@quantity
    	,      unit_cost        = 0.00
    	,      quantity_present = 0.00;
    
        RETURN 0;
    END;
    GO
    
    SELECT * FROM dbo.Inventory;
    
    DECLARE @last_sales money = NULL
    EXEC dbo.DoSell @ref_no     = 136005002
                  , @locatn_id  = 1
                  , @date_time  = '2013-07-19 17:04:34.020'
                  , @InvNo      = 1
                  , @department = 'RETAIL'
                  , @category   = 'BK-CHILD'
                  , @item       = 'DSCATTEST'
                  , @invent_id  = 0
                  , @quantity   = 50.00
                  , @cost_sales = @last_sales  OUTPUT;
    
    SELECT @last_sales AS last_sales;
    SELECT * FROM dbo.Inventory;

    Tuesday, July 23, 2013 2:37 PM
  • True, that will be correct. The program should calculate the FIFO cost of goods sold correctly for that case too. I don't even consider for now movements (say, we moved items from Warehouse to the Gift Shop or vs. versa) or exotic cases when we sell items we don't yet have in the inventory and only in the purchase order table.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 23, 2013 2:50 PM
    Moderator
  • Arrgh, selling items you don't yet have while using FIFO. I would rather use the 'Fixed Valuation Price' or the 'Average Purchase Price' system in those cases. But we cannot choose. Our customers demand it.

    I remember all kinds of problems we had with selling items you don't yet have while using a FIFO valuation system. We ended up valuing a sale like that against the latest price available while letting the quantity_present for that FIFO record become negative. After doing a purchase, we had to lower the quantity present of that purchase by the negative amount of the FIFO record with the negative quantity_present amount and resetting the negative quantity_present to 0. If the unit_cost of the record with the negative amount and the latest sales is not the same, you also have to do a revaluation because you sold something for an imaganary cost that turned out to be wrong.

    REMARK: You can only do the above when there is at least one purchase record. Selling an item that you never purchased before, isn't going to work.

    I really cannot see how you can do these exotic cases while working set based. Maybe our solution of 16 years ago wasn't so bad after all. Although my solution goes through part of the FIFO queue on a record by record basis, it only considers records with quantity_present > 0. For every article in your inventory this should be a limited number of records. Because it is a FIFO queue, the quantity_present of the oldest purchases will drop to 0 and stay that way. By creating smart indexes, you should be able to find the records you need in a jiffy.

    Would you like me to expand my example with the exotic cases and to take things into acount like the inventory item, or do you prefer to wait for a clever solution?

    Tuesday, July 23, 2013 3:21 PM
  • Oops, I didn't read your reply properly. You do have an idea of what an item is going to cost if it is not in the inventory yet because it already is a purchase order.
    Tuesday, July 23, 2013 3:23 PM
  • I haven't run your procedure, but it looks like you're calculating the cost of goods sold for the particular sale.

    My task is different - without changing the current structure of the Inventory (I actually produced it by a select first and the FIFO_RANK and InvNo columns I added using DENSE_RANK and ROW_NUMBER functions) I need to calculate cost of goods sold for the period (@dtStart and @dtEnd).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 23, 2013 4:23 PM
    Moderator
  • You are right in thinking that my proc calculates the cost of goods for the particular sale. I also remember that we made it possible to put a reservation on a purchase order for a particular sale so that the cost per unit of that purchase order could be used and the goods were already earmarked for a particular sale.

    I will have a look at your code tomorrow. I'm afraid my wife would like me to help in the household tonight.

    Tuesday, July 23, 2013 6:19 PM
  • Thanks for the help so far, Chris. 

    I am guessing you're from England, right? Was thinking about your expression 'tons of bricks' - don't think I heard it in US.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 23, 2013 6:23 PM
    Moderator
  • Hi Naomi,

    I tried running your proc, but I am missing the table 'dbo.numbers'. Could you give the DDL and the records needed for your simple example?

    Nope, I'm not from England. I am Dutch, so I am a citizen of the country called "The Netherlands" (sometimes wrongly called Holland). Calling the Netherlands Holland is the same difference as calling the United Kingdom England. The Scots, Welsh and Northern Irish will really be upset if you call them English. The Netherlands consists of 12 provinces, only two of which are Holland.

    As you probably understood, English is not my mother tongue, but my wife is a translator. She teaches me all kinds of interesting idiom. But it is UK English idiom.

    Actually 'tons of bricks' was my idea of a word joke. "You're a brick" is an idiomatic expression. But I wanted to use the superlative degree. I should have said "You'r a real brick" of course, but instead of acknowledging that in an idiomatic expression the whole of the expression is used figuratively, I zoomed in on the word "brick" and tried to think of a superlative for it. Silly joke. Jokes like that must stem from the time I did my PhD in General Linguistics.

    But computers scientists also make silly word jokes, don't they? Most of the time we do so be applying strict boolean logic to a natural language question, instead of answering the obviously intended question. One I really like, and my wife abhores, is to answer a question like "Would you like coffee or tea" with "Yes please, I would like coffee or tea".

    • Edited by Chris Sijtsma Wednesday, July 24, 2013 7:35 AM Added extra explanation
    Tuesday, July 23, 2013 7:03 PM
  • I am AFK till tomorow morning (Dutch time). I really should do the washing up and start vacuuming.
    Tuesday, July 23, 2013 7:10 PM
  • Maybe I can help in a bit.

    In a business perspective especially costing method, Average/FIFO/LIFO/Standard Costing method affects greatly on Accounting. What I do when the Costing Method is Average/LIFO/FIFO is I have separate stored procedure that calculates the cost.

    ((currectcost * currentitem) + (goodsreceivedcost * goodsreceivedqty)) / (currentitem + goodsreceivedqty) after computing the cost, I have 2 table then, 1 table is for goods received with the cost tied to it and 1 is for invoice with the sales price tied to it also.

    I then loop through the record (CTE_recursion) per item, if the qty of goods received still enough to fill the quantity of the invoice and so on... since I computed the average/fifo/lifo cost for the goods received, i then update the invoice that was filled up by goods received qty for the cost

    basically, u can sell an item even w/o stock in your inventory, u just have to schedule the delivery when the item arrives (Agree?). Now, when the item arrives it maybe the cost of goods received change, now in accounting you should have an Inventory Valuation, to bring  back the cost that is lost or added.


    Captain Gol D. Rogers, Vice Captain/Firstmate --Silvers Rayleigh.


    Tuesday, July 23, 2013 7:47 PM
  • I posted the very latest version as I have right now. For the solution I used Peso's idea from another thread, but I still use double cursors. I'm wondering if you can take a look at the logic I have if it makes sense or can be somehow improved.

    I decided to create a new table #MovingInventory that has CurrentQuantity column as well as Removed and Returned columns. It would be nice if we would have such columns directly in our inventory table - this way the calculations would have been easier. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 25, 2013 4:04 PM
    Moderator
  • Hi Naomi,

    I will have a look at your tomorrow. I'm sorry, but we were behind schedule with a deliverable. It is 22:25 PM in our part of the world, and I just came home. I still have to cook and feed the cats. The good news is that the update pack can be shipped tomorrow. The bad news is that I didn't have time to look at your code today.

    The reason I asked you to add the CurrentQuantity column is because that is the only way you can prevent doing a table scan (or at least a scan of all FIFO items for a certain inventory item) if you want to calculate the costs. If you don't have a column like that, you need to scan each and record of a particular inventory item to be able to calculate the cost correctly. But I guess I'm not telling you anything you didn't know already.

    Thursday, July 25, 2013 8:24 PM
  • Yes, without CurrentQuantity I have to re-calculate the inventory pretty much every time.

    A while ago I wrote a proposition of the changes we may want to improve the inventory process. I believe this is documented and a footprint is created, but we never got around to implementing what I suggested then.

    So, I am working with the table I have at present.

    I also deleted my messages in this thread as I changed the code again. The most current code is in my other thread about debugging procedure.

    I also started a new TechNet article today where I plan to put what I've done and some explanations and several references I know.

    If you want to take this off-line (as the code is quite long and I don't want to post it again and again) you can e-mail me at n following my last name at gmail dot com.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 25, 2013 8:35 PM
    Moderator
  • Erm, is Naomi your last name? I thought it was your Christian name. I am not allowed to look at your LinkedIn profile because we don't know someone in common (on LinkedIn of course). I am not on facebook, so that is not going to work. So I am not able to check if Naomi is your first or your last name. If it is your first name, I am afraid I do not know your last name. I'll give it a shot and see if the mail bounces or not.

    Ignore my rambling above. Google knows everything. Found it.

    • Edited by Chris Sijtsma Friday, July 26, 2013 7:31 AM No need to respond
    Friday, July 26, 2013 7:29 AM
  • I posted the very latest version as I have right now. For the solution I used Peso's idea from another thread, but I still use double cursors. I'm wondering if you can take a look at the logic I have if it makes sense or can be somehow improved.

    I decided to create a new table #MovingInventory that has CurrentQuantity column as well as Removed and Returned columns. It would be nice if we would have such columns directly in our inventory table - this way the calculations would have been easier. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    I checked the workings of the latest version of your proc [dbo].[siriussp_CostOfGoodsSold_FIFO]. That is the one with #MovingInventory and #Removed. As far as I can see, the logic is fine. I also tested various scenarios. Not only does the logic seem fine, also the right costs are calculated ;-)

    I guess your question is: Can I do it without nested cursors to enhance the performance. I will look into that question this afternoon. I found out that on SQL Server 2005 we still have some windowing functions. Maybe that can help.


    Monday, July 29, 2013 9:14 AM
  • Hi Naomi,

    I am sorry to say that I do not see how you can do this without two nested cursors. Probably that is because of a lack of intelligence on my side and/or a lack of experience with windowed functions and CTEs. I tried the following, but I do not know how to get on from here:

    USE NaomiDB
    GO
    IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Inventory') DROP TABLE dbo.Inventory;
    GO
    /****** Object:  Table dbo.Inventory    Script Date: 07/22/2013 11:56:09 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE dbo.Inventory
    ( ref_no            numeric(17, 0)  NOT NULL
    , locatn_id         int             NOT NULL
    , date_time         datetime        NOT NULL
    , fifo_rank         bigint          NULL
    , InvNo             bigint          NULL
    , department        char(10)        NOT NULL
    , category          char(10)        NOT NULL
    , item              char(10)        NOT NULL
    , invent_id         int             NOT NULL
    , trans_type        char(1)         NOT NULL
    , quantity          numeric(8, 2)   NOT NULL
    , unit_cost         money           NOT NULL
    );
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT dbo.Inventory (ref_no, locatn_id, date_time, fifo_rank, InvNo, department, category, item, invent_id, trans_type, quantity, unit_cost) VALUES (CAST(53 AS Numeric(17, 0)), 1, CAST(0x0000A20000FF6D74 AS DateTime), 1, 1, 'RETAIL    ', 'BK-CHILD  ', 'DSCATTEST ', 0, 'P', CAST(40.00 AS Numeric(8, 2)), 10.0000)
    INSERT dbo.Inventory (ref_no, locatn_id, date_time, fifo_rank, InvNo, department, category, item, invent_id, trans_type, quantity, unit_cost) VALUES (CAST(53 AS Numeric(17, 0)), 1, CAST(0x0000A20000FF6D74 AS DateTime), 2, 1, 'RETAIL    ', 'BK-CHILD  ', 'DSCATTEST ', 0, 'P', CAST(40.00 AS Numeric(8, 2)), 5.0000)
    INSERT dbo.Inventory (ref_no, locatn_id, date_time, fifo_rank, InvNo, department, category, item, invent_id, trans_type, quantity, unit_cost) VALUES (CAST(136005001 AS Numeric(17, 0)), 1, CAST(0x0000A200011967D8 AS DateTime), 3, 1, 'RETAIL    ', 'BK-CHILD  ', 'DSCATTEST ', 0, 'S', CAST(-50.00 AS Numeric(8, 2)), 0.0000)
    INSERT dbo.Inventory (ref_no, locatn_id, date_time, fifo_rank, InvNo, department, category, item, invent_id, trans_type, quantity, unit_cost) VALUES (CAST(54 AS Numeric(17, 0)), 1, CAST(0x0000A200011967DA AS DateTime), 4, 1, 'RETAIL    ', 'BK-CHILD  ', 'DSCATTEST ', 0, 'P', CAST(40.00 AS Numeric(8, 2)), 7.5000)
    INSERT dbo.Inventory (ref_no, locatn_id, date_time, fifo_rank, InvNo, department, category, item, invent_id, trans_type, quantity, unit_cost) VALUES (CAST(54 AS Numeric(17, 0)), 1, CAST(0x0000A200011967DA AS DateTime), 5, 1, 'RETAIL    ', 'BK-CHILD  ', 'DSCATTEST ', 0, 'P', CAST(40.00 AS Numeric(8, 2)), 12.5000)
    INSERT dbo.Inventory (ref_no, locatn_id, date_time, fifo_rank, InvNo, department, category, item, invent_id, trans_type, quantity, unit_cost) VALUES (CAST(136005002 AS Numeric(17, 0)), 1, CAST(0x0000A200011967DE AS DateTime), 6, 1, 'RETAIL    ', 'BK-CHILD  ', 'DSCATTEST ', 0, 'S', CAST(-50.00 AS Numeric(8, 2)), 0.0000)
    INSERT dbo.Inventory (ref_no, locatn_id, date_time, fifo_rank, InvNo, department, category, item, invent_id, trans_type, quantity, unit_cost) VALUES (CAST(136005003 AS Numeric(17, 0)), 1, CAST(0x0000A200011967E3 AS DateTime), 7, 1, 'RETAIL    ', 'BK-CHILD  ', 'DSCATTEST ', 0, 'S', CAST(-50.00 AS Numeric(8, 2)), 0.0000)
    GO
    
    SELECT fifo_rank
    ,      InvNo
    ,      item
    ,      trans_type
    ,      quantity
    ,      unit_cost
    ,      (SUM(quantity) OVER (PARTITION BY InvNo ORDER BY fifo_rank) - quantity) AS running_total_prev
    ,      SUM(quantity) OVER (PARTITION BY InvNo ORDER BY fifo_rank) AS running_total
    FROM   Inventory
    ORDER  BY InvNo, fifo_rank;
    GO

    Result set (I am sorry for the layout, but I cannot get it right):

    fifornk     invno   item              tran     qty        cost      tot_prev    tot
    -------    -------   -------------  -----    -------  --------   ----------  ----------
    1                1                DSCATTEST   P     40.00    10,00         0.00      40.00
    2                1                DSCATTEST   P     40.00      5,00       40.00      80.00
    3                1                DSCATTEST   S    -50.00      0,00      80.00      30.00
    4                1                DSCATTEST   P     40.00      7,50       30.00      70.00
    5                1                DSCATTEST   P     40.00    12,50       70.00    110.00
    6                1                DSCATTEST   S    -50.00      0,00    110.00      60.00
    7                1                DSCATTEST   S    -50.00      0,00       60.00     10.00

    I cannot figure out how to find the unit_costs for a sales record (tran_type = 'S') without iterating through the records corresponding to a purchase (tran_type = 'P'). Because the quantity of the sales and the purchases can vary, and because a sale can leave part of a batch, the only option I see is using a cursor. I only did this for one particular item, so I also would need an extra cursor, looping over the different items (= DISTINCT InvNo). I am sorry it took me so long, and I am also sorry that I cannot offer you a better solution than you already thought up yourself.






    • Edited by Chris Sijtsma Monday, July 29, 2013 11:56 AM grammar corrected
    Monday, July 29, 2013 11:34 AM