In this article I am going to explain the FIFO (first in first out) algorithm for calculating cost of goods sold. This is the real business problem I am working on now.



Different methods of calculating Cost of Goods Sold in the Inventory Calculation


There are many articles on the Internet explaining concepts of Calculating Cost of Goods On Hand and Cost of Goods Sold in the inventory calculation. I will give just a few of them and quote a bit of material from these articles to provide a brief overview. I suggest readers of this article review the mentioned articles or just do a Google search on the terms "FIFO Cost of Goods Inventory calculation".

Inventory Valuation Method - FIFO Vs. Moving Average

How to Calculate Cost of Goods Sold (CoGS)

Inventory and Cost of Goods Sold

Chapter 4 by Hugo Kornelis in the "SQL Server MVP Deep Dives" book (first book) talks a bit about Running Total problem, so it may be useful to read this chapter as well.

There are several valuation methods, but for small businesses it is generally restricted to FIFO and Moving Average. 

In our application we have two methods of calculating inventory:  RWAC (Running Weighted Average Cost) and FIFO. The preferred method of the calculation can be set in the Inventory Preference form.

Implementing FIFO Cost of Goods Sold in our application


After we have briefly discussed the theory, I am going to talk about implementing the FIFO algorithm of calculating the Cost of Goods in our software. Historically, our application had only simpler RWAC method (and not even the true RWAC, but rather just Average cost method). Few years ago the company management team decided that it's time to offer our clients a FIFO method of calculating Cost of Goods On Hand and Cost of Goods Sold. My first task was to identify all places in our software where we may need adjustments and my colleague was tasked with creating necessary T-SQL functions.

I need to describe the Inventory table used in our application.

Here is its DDL

CREATE TABLE [dbo].[i_invent](
    [pri_key] [int] IDENTITY(1,1) NOT 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,
    [ref_no] [numeric](17, 0) NOT NULL,
    [quantity] [numeric](8, 2) NOT NULL,
    [unit_cost] [money] NOT NULL,
    [locatn_id] [int] NOT NULL,
    [message] [varchar](25) NOT NULL,
    [exportd_on] [datetime] NULL,
    [operator] [char](6) NOT NULL,
    [salespoint] [char](6) NOT NULL,
    [date_time] [datetime] NOT NULL,
    [po_link] [int] NOT NULL,
    [adj_type] [int] NOT NULL,
 CONSTRAINT [i_invent_track_no] PRIMARY KEY CLUSTERED
(
    [pri_key] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [SaleTransactions]
) ON [SaleTransactions]
 
CREATE NONCLUSTERED INDEX [date_time] ON [dbo].[i_invent]
(
    [date_time] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [SaleTransactions]

CREATE
NONCLUSTERED INDEX [department] ON [dbo].[i_invent]
(
    [department] ASC,
    [category] ASC,
    [item] ASC,
    [invent_id] ASC,
    [quantity] ASC,
    [locatn_id] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [SaleTransactions]

CREATE
NONCLUSTERED INDEX [i_invent_po_link] ON [dbo].[i_invent]
(
    [po_link] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [SaleTransactions]

CREATE
NONCLUSTERED INDEX [locatn_id] ON [dbo].[i_invent]
(
    [locatn_id] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [SaleTransactions]

CREATE
NONCLUSTERED INDEX [ref_no] ON [dbo].[i_invent]
(
    [ref_no] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [SaleTransactions]
GO
 
ALTER TABLE [dbo].[i_invent]  WITH NOCHECK ADD  CONSTRAINT [FK_i_invent_i_items] FOREIGN KEY([invent_id])
REFERENCES [dbo].[i_items] ([invent_id])
NOT FOR REPLICATION
GO
 
ALTER TABLE [dbo].[i_invent] NOCHECK CONSTRAINT [FK_i_invent_i_items]
GO
 
ALTER TABLE [dbo].[i_invent]  WITH NOCHECK ADD  CONSTRAINT [FK_i_invent_i_locatn] FOREIGN KEY([locatn_id])
REFERENCES [dbo].[i_locatn] ([locat_id])
GO
 
ALTER TABLE [dbo].[i_invent] CHECK CONSTRAINT [FK_i_invent_i_locatn]
GO
 
ALTER TABLE [dbo].[i_invent]  WITH NOCHECK ADD  CONSTRAINT [FK_i_invent_i_pchord] FOREIGN KEY([po_link])
REFERENCES [dbo].[i_pchord] ([pri_key])
NOT FOR REPLICATION
GO
 
ALTER TABLE [dbo].[i_invent] NOCHECK CONSTRAINT [FK_i_invent_i_pchord]
GO
 
ALTER TABLE [dbo].[i_invent]  WITH NOCHECK ADD  CONSTRAINT [FK_i_invent_items] FOREIGN KEY([department], [category], [item])
REFERENCES [dbo].[items] ([department], [category], [item])
GO
 
ALTER TABLE [dbo].[i_invent] CHECK CONSTRAINT [FK_i_invent_items]
GO

Each inventory item was defined by these attributes: department, category, item, invent_id, locatn_id. These 5 columns are used to identify a single inventory item in its current location. Quantity and unit_cost columns are used to identify each inventory movement. In case of Sales or Returns (Trans_Type = 'S') the unit_cost is 0 and has to be calculated. Trans_Type can be one of the following: P - purchase, A - adjustment, T - transfer and S - Sale (negative quantity) or Return (positive quantity). The ref_no column in case of sales / returns provides a reference to the trans_no from transactions table. Also the date_time column is important for our calculations. Other columns in the Inventory table are used for other purposes and not relevant for the calculation of Cost of Goods on Hand or Cost of Goods Sold.

So, as I said, the first implementation of the Cost of Goods on Hand calculation was written by my colleague as a multi-statements table valued function that accepted many parameters (some of them were optional) and the calculation method type (RWAC, FIFO or LIFO) and returned the result as a table. I checked the date of the first implementation in our Source of Safe software and it is August 2010. 

It was quickly determined that using the multi-statements table valued function in that way led to a very bad performance. Also, somehow the process of developing the functions (or procedures) to do these calculations was turned into my hands. I tried to change these functions to inline table-valued functions for each method separately (one for FIFO and one for RWAC, we decided to drop LIFO method then) but yet the performance on these set based functions was really bad for the clients with substantial inventory movements. 

In addition to discussing the FIFO calculation problem in the forum's threads I also had private e-mail exchange with Peter Larsson who eventually helped me to adapt his solution from the Set-based Speed Phreakery: The FIFO Stock Inventory SQL Problem for our table's structure and Cost of Goods on Hand problem.

I discussed this problem in many threads in Transact-SQL forum in MSDN. Here is one of the earliest threads (from May 2011), where I found that inline CTE based solution when we needed to use the same CTE multiple times, was significantly slower than using temp tables to hold intermediate calculations:

Temp tables vs CTE

I just re-read that long thread. Essentially, I confirmed that using inline UDF to calculate Cost of Goods on Hand for the selected inventory and using CROSS APPLY with that function was very slow compared to getting the inventory to work with into a temporary table first and then apply the calculations as a stored procedure. 

It also started to become clear that using our current structure of the table and not having anything pre-calculated will lead to bad performance as we need to re-calculate the cost every time from the very beginning. About a year or so ago I proposed a plan to re-design our inventory table by adding few more tables we will be updating at the same time as transaction occur. Unfortunately, we haven't proceed in this direction yet and I don't know if we ever going to look into these ideas in order to make the calculation process easier.

In this article I planned to discuss the Cost of Goods Sold calculations, so I will just give the current code of the Cost of Goods on Hand FIFO procedure without too many explanations.

Current procedure to calculate Cost of Goods on Hand



--==========================================================
/*  SP that returns total quantity and cost of goods on hand
    by department, category, item, invent_id, and locatn_id,
    using FIFO (first in/first out) method of cost valuation:
    To retrieve the total (FIFO) cost of goods on hand
    for all inventory by location, by department:
     
    EXECUTE dbo.siriussp_CostOfGoodsOnHand_FIFO  1  
              
    locatn_id   department QuantityOnHand CostOfGoodsOnHand
    ----------- ---------- -------------- ---------------------
    -999        RETAIL     2              0.90
    1           RETAIL     2359           31567.73
    3           RETAIL     1609           19001.21  
   */
--=========================================================  
ALTER PROCEDURE dbo.siriussp_CostOfGoodsOnHand_FIFO
    (    
      @bIncludeZeroes BIT = 1         /* If 1, then include records for items with zero on-hand */
    )
 
AS    
  BEGIN
  SET NOCOUNT  ON;
    
  WITH cteInventorySum
       AS (SELECT department,
                    category,
                    item,
                    invent_ID,
                    locatn_ID,
                    SUM(quantity) AS TotalInventory,
                    MAX(date_time) AS LastDateTime
           FROM     #Inventory
           GROUP BY department,
                    category,
                    item,
                    invent_ID,
                    locatn_ID),
       cteReverseInSum
       AS (/* Perform a rolling balance ( in reverse order ) through the inventory movements in */
          SELECT s.department,
                 s.category,
                 s.item,
                 s.invent_ID,
                 s.locatn_ID,
                 s.Fifo_Rank,
                 (SELECT SUM(i.quantity)
                  FROM   #Inventory AS i
                  WHERE  i.department = s.department
                         AND i.category = s.category
                         AND i.item = s.item
                         AND i.invent_id = s.invent_id
                         AND i.locatn_id = s.locatn_id
                         AND i.trans_Type IN ('P','A','T')
                         AND i.Fifo_Rank >= s.Fifo_Rank) AS RollingInventory,
                 SUM(s.Quantity) AS ThisInventory
           FROM     #Inventory AS s
           WHERE    s.Trans_Type IN ('P','A','T')
           GROUP BY s.Department,
                    s.Category,
                    s.Item,
                    s.Invent_ID,
                    s.Locatn_ID,
                    s.Fifo_Rank),
       cteWithLastTranDate
       AS (SELECT w.Department,
                  w.Category,
                  w.Item,
                  w.Invent_ID,
                  w.Locatn_ID,
                  w.LastDateTime,
                  w.TotalInventory,
                  COALESCE(LastPartialInventory.Fifo_Rank,0)                                                                         AS Fifo_Rank,
                  COALESCE(LastPartialInventory.InventoryToUse,0)                                                                    AS InventoryToUse,
                  COALESCE(LastPartialInventory.RunningTotal,0)                                                                      AS RunningTotal,
                  w.TotalInventory - COALESCE(LastPartialInventory.RunningTotal,0) + COALESCE(LastPartialInventory.InventoryToUse,0) AS UseThisInventory
           FROM   cteInventorySum AS w
                  OUTER APPLY (SELECT   TOP ( 1 ) z.Fifo_Rank,
                                                  z.ThisInventory    AS InventoryToUse,
                                                  z.RollingInventory AS RunningTotal
                               FROM     cteReverseInSum AS z
                               WHERE    z.Department = w.Department
                                        AND z.Category = w.Category
                                        AND z.Item = w.Item
                                        AND z.Invent_ID = w.Invent_ID
                                        AND z.Locatn_ID = w.Locatn_ID
                                        AND z.RollingInventory >= w.TotalInventory
                               ORDER BY z.Fifo_Rank DESC) AS LastPartialInventory),
       LastCost
       AS (SELECT DISTINCT Cogs.department,
                           Cogs.category,
                           Cogs.item,
                           Cogs.invent_id,
                           LastCost.LastCost
           FROM   cteWithLastTranDate Cogs
                  CROSS APPLY dbo.siriusfn_LastCostUpToDate(Cogs.department,Cogs.category,Cogs.item,Cogs.invent_id, Cogs.LastDateTime) LastCost
           WHERE  Cogs.UseThisInventory IS NULL
                   OR Cogs.UseThisInventory = 0 OR Cogs.TotalInventory IS NULL OR Cogs.TotalInventory = 0),
    
   cteSource
AS (
         
    SELECT  y.Department,
            y.Category,
            y.Item,
            y.Invent_ID,
            y.Locatn_ID,       
            y.TotalInventory as QuantityOnHand,
            SUM(CASE WHEN e.Fifo_Rank = y.Fifo_Rank
               THEN y.UseThisInventory
               ELSE e.Quantity END * Price.Unit_Cost) AS CostOfGoodsOnHand,
            LastCost.LastCost
    FROM        cteWithLastTranDate AS y
    LEFT JOIN #Inventory AS e ON e.Department = y.Department
                AND e.Category = y.Category
                AND e.Item = y.Item
                AND e.Invent_ID = y.Invent_ID
                AND e.Locatn_ID = y.Locatn_ID
                AND e.Fifo_Rank >= y.Fifo_Rank
                AND e.Trans_Type IN ('P', 'A', 'T')
    LEFT JOIN LastCost
       ON y.Department = LastCost.Department
                AND y.Category = LastCost.Category
                AND y.Item = LastCost.Item
                AND y.Invent_ID = LastCost.Invent_ID
             
     
    OUTER APPLY (
                /* Find the Price of the item in */
                SELECT TOP (1) p.Unit_Cost
                FROM    #Inventory AS p
                WHERE   p.Department = e.Department and
                        p.Category = e.Category and
                        p.Item = e.Item and
                        p.Invent_ID = e.Invent_ID and
                        p.Locatn_ID = e.Locatn_ID and                  
                        p.Fifo_Rank <= e.Fifo_Rank and
                        p.Trans_Type IN ('P', 'A', 'T')
                ORDER BY p.Fifo_Rank DESC
            ) AS Price
  
    GROUP BY y.Department,
            y.Category,
            y.Item,
            y.Invent_ID,
            y.Locatn_ID,
            y.TotalInventory,
            LastCost.LastCost)
             
  SELECT Department,
         Category,
         Item,
         Invent_ID,
         Locatn_ID,
         CONVERT(INT,QuantityOnHand) as QuantityOnHand,
         COALESCE(CostOfGoodsOnHand,0) AS CostOfGoodsOnHand,
         COALESCE(CASE
           WHEN QuantityOnHand <> 0
                AND CostOfGoodsOnHand <> 0 THEN ABS(CostOfGoodsOnHand / QuantityOnHand)
           ELSE LastCost
         END, 0) AS AverageCost
  FROM   cteSource
  WHERE  @bIncludeZeroes = 1
          OR (@bIncludeZeroes = 0
              AND CostOfGoodsOnHand <> 0)
  ORDER BY Department,
              Category,
              Item,
              Invent_ID,
              Locatn_ID;           
END
 GO
/* Test Cases
CREATE TABLE [dbo].[#Inventory](
      [pri_key] [int] IDENTITY(1,1) NOT NULL,
      [ref_no] [numeric](17, 0) NOT NULL,
      [locatn_id] [int] NOT NULL,
      [date_time] [datetime] NOT NULL,
      [fifo_rank] [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]
SET IDENTITY_INSERT [dbo].[#Inventory] ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[#Inventory]([pri_key], [ref_no], [locatn_id], [date_time], [fifo_rank], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost])
SELECT 774, 0, 1, '20120627 11:58:26.000', 1, N'RETAIL    ', N'SUPPLIES  ', N'BUG_SPRAY ', 0, N'T', 10.00, 2.0000 UNION ALL
SELECT 775, 129005001, 1, '20120627 13:02:57.000', 2, N'RETAIL    ', N'SUPPLIES  ', N'BUG_SPRAY ', 0, N'S', -9.00, 0.0000 UNION ALL
SELECT 778, 0, 1, '20120627 13:06:07.000', 3, N'RETAIL    ', N'SUPPLIES  ', N'BUG_SPRAY ', 0, N'T', 10.00, 2.6667 UNION ALL
SELECT 779, 130005001, 1, '20120627 13:17:46.000', 4, N'RETAIL    ', N'SUPPLIES  ', N'BUG_SPRAY ', 0, N'S', -7.00, 0.0000 UNION ALL
SELECT 780, 131005001, 1, '20120627 13:18:16.000', 5, N'RETAIL    ', N'SUPPLIES  ', N'BUG_SPRAY ', 0, N'S', 3.00, 0.0000 UNION ALL
SELECT 772, 24, 3, '20120627 11:57:17.000', 1, N'RETAIL    ', N'SUPPLIES  ', N'BUG_SPRAY ', 0, N'P', 20.00, 2.0000 UNION ALL
SELECT 773, 0, 3, '20120627 11:58:26.000', 2, N'RETAIL    ', N'SUPPLIES  ', N'BUG_SPRAY ', 0, N'T', -10.00, 2.0000 UNION ALL
SELECT 776, 24, 3, '20120627 13:04:29.000', 3, N'RETAIL    ', N'SUPPLIES  ', N'BUG_SPRAY ', 0, N'P', 20.00, 3.0000 UNION ALL
SELECT 777, 0, 3, '20120627 13:06:07.000', 4, N'RETAIL    ', N'SUPPLIES  ', N'BUG_SPRAY ', 0, N'T', -10.00, 2.6667
COMMIT;
RAISERROR (N'[dbo].[#Inventory]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
 
SET IDENTITY_INSERT [dbo].[#Inventory] OFF;
 
PRINT 'FIFO Calculation Cost:'
declare @Time datetime2(7) = SYSDATETIME(), @Elapsed int
 EXECUTE dbo.siriussp_CostOfGoodsOnHand_FIFO          1
set @Elapsed = DATEDIFF(microsecond,@time, getdate())
print 'Elapsed: ' + convert(varchar(10),@Elapsed) + ' microseconds'
go
*/

You can see that this procedure is using #Inventory temporary table and that it also has fifo_rank column which is not present in the i_invent table from the database. I am pre-selecting rows I may be interested in into the temporary #Inventory table and creating fifo_rank column using ROW_NUMBER() function to partition by the 5 columns that determine a single inventory item and order by date_time, po_link columns. You can also see that this procedure references this function siriusfn_LastCostUpToDate. This function calculates last cost of the item to date using iterative approach  - it first tries to calculate it for the specific invent_id (invent_id <> 0 is for the "matrix" items, e.g. items that may come in different sizes or colors). If there are no rows for the specific invent_id it tries to get the last cost for the item itself regardless of invent_id. If it is still unknown, it checks purchase orders table (i_pchord) also first for the invent_id and then for the item itself.

Here is the current code of this function:
ALTER FUNCTION [dbo].[siriusfn_LastCostUpToDate] (
    @cDepartment CHAR(10)
    ,@cCategory CHAR(10)
    ,@cItem CHAR(10)
    ,@iInventID INT
    ,@dtEnd DATETIME -- cut off date
    )
RETURNS TABLE
    --==============================================
    /*  Function that returns the last unit cost value for
    every matrix item within the given range.  It evaluates,
    in order, until it finds the first applicable record:
     
        1. last received cost at the matrix level
        2. last received cost at the item level
        3. last ordered cost at the matrix level
        4. last ordered cost at the item level
        5. If no history is found, then last cost is zero.
*/
AS
RETURN
WITH cteItemsOnly AS (
        SELECT i.department
            ,i.category
            ,i.item
            ,i.inventory
        FROM dbo.items i
        WHERE i.department = @cDepartment
            AND i.category = @cCategory
            AND i.item = @cItem
        )
    ,cteItems AS (
        SELECT i.department
            ,i.category
            ,i.item
            ,ISNULL(ii.invent_id, 0) AS invent_id
            ,inventory
        FROM cteItemsOnly i
        LEFT JOIN dbo.i_items ii ON i.inventory = 0
            AND ii.department = i.department
            AND ii.category = i.category
            AND ii.item = i.item
            AND ii.invent_id = @iInventID
        )
    ,cteRcvdMatrix AS (
        SELECT i.department
            ,i.category
            ,i.item
            ,i.invent_id
            ,F.unit_cost AS LastCost
        FROM cteItems i
        OUTER APPLY (
            SELECT TOP 1 unit_cost
            FROM dbo.i_invent ii
            WHERE trans_type IN (
                    'P'
                    ,'A'
                    ,'T'
                    )
                AND i.department = ii.department
                AND i.category = ii.category
                AND i.item = ii.item
                AND i.invent_id = ii.invent_id
                AND ii.date_time <= @dtEnd
            ORDER BY ii.date_time DESC
                ,unit_cost DESC
            ) F
        )
    ,cteRcvdItem AS (
        SELECT *
        FROM cteRcvdMatrix
        WHERE LastCost IS NOT NULL
         
        UNION ALL
         
        SELECT i.department
            ,i.category
            ,i.item
            ,i.invent_id
            ,F.unit_cost AS LastCost
        FROM cteRcvdMatrix i
        OUTER APPLY (
            SELECT TOP 1 unit_cost
            FROM dbo.i_invent ii
            WHERE trans_type IN (
                    'P'
                    ,'A'
                    ,'T'
                    )
                AND i.department = ii.department
                AND i.category = ii.category
                AND i.item = ii.item
                AND ii.date_time <= @dtEnd
            ORDER BY ii.date_time DESC
                ,unit_cost DESC
            ) F
        WHERE i.LastCost IS NULL
        )
    ,ctePOMatrix AS (
        SELECT *
        FROM cteRcvdItem
        WHERE LastCost IS NOT NULL
         
        UNION ALL
         
        SELECT i.department
            ,i.category
            ,i.item
            ,i.invent_id
            ,F.unit_cost AS LastCost
        FROM cteRcvdItem i
        OUTER APPLY (
            SELECT TOP (1) unit_cost
            FROM dbo.i_pchord ii
            WHERE i.department = ii.department
                AND i.category = ii.category
                AND i.item = ii.item
                AND i.invent_id = ii.invent_id
                AND ii.date_time <= @dtEnd
            ORDER BY ii.date_time DESC
                ,unit_cost DESC
            ) F
        WHERE i.LastCost IS NULL
        )
    ,ctePOItem AS (
        SELECT *
        FROM ctePOMatrix
        WHERE LastCost IS NOT NULL
         
        UNION ALL
         
        SELECT i.department
            ,i.category
            ,i.item
            ,i.invent_id
            ,F.unit_cost AS LastCost
        FROM ctePOMatrix i
        OUTER APPLY (
            SELECT TOP (1) unit_cost
            FROM dbo.i_pchord ii
            WHERE i.department = ii.department
                AND i.category = ii.category
                AND i.item = ii.item
                AND ii.date_time <= @dtEnd
            ORDER BY ii.date_time DESC
                ,unit_cost DESC
            ) F
        WHERE i.LastCost IS NULL
        )
 
SELECT i.department
    ,i.category
    ,i.item
    ,i.invent_id
    ,coalesce(i.LastCost, 0) AS LastCost
FROM ctePOItem i
GO
/* Test Cases
set statistics io on
  SELECT * FROM dbo.siriusfn_LastCost('RT34HANDW','058GLOVEL''19599     ',   409) -- RT34HANDW   058GLOVEL   19599    
   SELECT * FROM dbo.siriusfn_LastCostUpToDate('RT34HANDW','058GLOVEL',     '19599     ',   409, '20040101')
 -- select top (1) * from dbo.i_invent where invent_id = 409 and trans_type in ('A','P','T') and quantity > 0 order by date_time desc
set statistics io off
*/


FIFO Cost of Goods Sold



Now I am going to discuss the procedure I am using to calculate Cost of Goods Sold using FIFO method. About a year ago I spent a lot of time creating two versions of the procedure - one for SQL Server 2005-2008 and one for SQL Server 2012. I thought I tested these procedures extensively and had them working great. Our testers also tested them in various scenarios (I hope). Turned out I had not tested them well enough and they were failing in a really simple scenario. Also, our client found a more complex scenario and was able to perform analysis of these procedures and showed their faults. Therefore I needed to look at them again and fix the problems.

So, I looked at them recently and I had to admit, I could not really understand what I was doing in them.  I think if I had written this article then rather than now, it may have helped. So, by documenting my line of thoughts now in creating this procedure and also accepting the revisions by other people, it may help me (and others) to perfect this procedure in the future, or re-design it again if needed.

The scenario that my colleague found failing in the last implementation of the procedure was the following:

1. Create a new retail tracking item if necessary
2. Receive 20 units of the item at $10.00 each
3. Receive another 20 units of the item at $5.00 each
4. Sell 30 units of the item
5. Sell 10 units of the item the next day (or set carryover 1 day forward)
6. Make sure the "Closing Cost Calculation Algorithm" under Retail Preferences in SysManager is set to FIFO. Run the Profit and Loss Report against each day. For the second day (sale of 10 units), COGS correctly shows $50.00. For the first day (sale of 30 units), COGS shows $150.00 (should be $250.00) 

So, I decided I am going to try to re-write this procedure again rather than trying to figure out what was that procedure doing and where the bug may be. I also found the following thread in MSDN SQL FIFO Query which I already used in my prior attempts to solve the FIFO Cost of Goods Sold problem. This time I concentrated on the Peter Larsson's solution in that thread (SwePeso).

In the procedure, that is invoked before the FIFO Cost of Goods Sold procedure is called, I am selecting inventory items for the user's selections (say, for the Profit and Loss report the user can select particular department (or department and category), may select specific vendor and also selects a date range interval). So, I select rows into #Inventory temp table up to the end date of the selected dates interval. I again add FIFO_RANK column and also for simplicity I add InvNo numerical column using DENSE_RANK() function with order by Department, Category, Item, Invent_ID, Locatn_ID. This is done in order to use a single integer column to identify each inventory item rather than 5 columns. In my calculations I am also using dbo.Numbers table that has a single number column. In our database that table contains numbers from ~-100K to 100K.

The idea of the new design of this procedure is to calculate the starting point of the inventory in one step (up to Start Date - dtStart parameter) using Peter's idea and then process each individual sale or return (and negative quantity transfers) within the selected date intervals. The final result should have all sales and returns in the selected period (quantity and unit_cost).

So, I decided to introduce yet another temporary table I called #MovingInventory. In this table I have InvNo column (this is artificial Item Id for each inventory item in a location I created in the pre-step), fifo_rank column, quantity - the same quantity as in the #Inventory, CurrentQuantity (this column should reflect the current remaining quantity), Removed (quantity removed) and Returned (quantity returned). If we are to change our current Inventory process, we may create this table as a permanent table in the database and update it on each inventory movement. We can also create InventorySales table. Using these tables will significantly simplify the current calculation process.

Therefore, in the beginning of the procedure I now have this code:

IF OBJECT_ID('TempDB..#MovingInventory', N'U') IS NOT NULL
        DROP TABLE #MovingInventory;
 
    CREATE TABLE [dbo].[#MovingInventory] (
        InvNo INT NOT NULL
        ,fifo_rank INT NOT NULL
        ,quantity INT
        ,unit_cost MONEY
        ,Removed INT
        ,Returned INT
        ,CurrentQuantity INT
        ,CONSTRAINT pkMovingInventory PRIMARY KEY (
            InvNo
            ,fifo_rank
            )
        )
 
    INSERT INTO #MovingInventory (
        InvNo
        ,fifo_rank
        ,quantity
        ,unit_cost
        ,Removed
        ,Returned
        ,CurrentQuantity
        )
    SELECT InvNo
        ,fifo_rank
        ,quantity
        ,unit_cost
        ,0
        ,0
        ,quantity
    FROM #Inventory
    WHERE trans_type IN (
            'P'
            ,'A'
            ,'T'
            )
        AND quantity > 0
    ORDER BY InvNo
        ,fifo_rank;

So, we start with populating this new #MovingInventory temporary table with all positive additions to the inventory with their unit_cost. I set CurrentQuantity to quantity and Returned and Removed to 0. 

I have two more temporary tables used in this procedure: #Sales - this table will be used to generate our final result and it will contain all sales and returns in the specified date range with the quantity sold (returned) and unit cost used.

I also have #Removed table. I could have used table variable here instead but I recall I had some problems with the table variable before in my prior version of that procedure so I decided to use temporary table again. This table will be used to hold items removed (or returned) on each iteration and it will be cleaned (truncated) on each iteration.

Here is the definition of these 2 temporary tables at the top of the procedure:

IF OBJECT_ID('TempDB..#Sales', N'U') IS NOT NULL
        DROP TABLE #Sales;
 
    CREATE TABLE [dbo].[#Sales] (
        InvNo INT NOT NULL
        ,[trans_no] [numeric](17, 0) NOT NULL
        ,[locatn_id] [int] NOT NULL
        ,[date_time] [datetime] NOT NULL
        ,[department] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
        ,[category] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
        ,[item] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
        ,[invent_id] [int] NOT NULL
        ,quantity INT
        ,unit_cost MONEY
        )
 
    IF OBJECT_ID('TempDB..#Removed', N'U') IS NOT NULL
        DROP TABLE #Removed;
 
    CREATE TABLE [dbo].[#Removed] (
        unit_cost MONEY
        ,Removed INT
        )


Now, I decided to use two cursor loops in my procedure - one to iterate through each inventory item and another inner loop to go through each individual sale for that item. We all know well, that cursor based solutions are generally not recommended as they normally perform much worse than set based solutions. However, for solving this problem I simply don't see a set-based solution that's why I decided to use cursors. I may eventually re-design this procedure into CLR based procedure although I am not sure CLR based procedures may work with the temporary tables to start with.

So, my first step is to calculate prior inventory in one step. Here is the code I use for this:

WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SELECT @fifo_rank = MAX(fifo_rank)
            ,@Removed = - 1 * SUM(quantity)
        FROM #Inventory
        WHERE date_time < @dtStart
            AND (
                trans_type = 'S'
                OR quantity < 0
                )
            AND InvNo = @InvNo;
 
        IF COALESCE(@Removed, 0) > 0 -- what to do when we start with returns - unlikely to happen, though?
        BEGIN
            IF @Debug = 1
                PRINT 'Calculating starting inventory';;
 
            WITH cteSource
            AS (
                SELECT TOP (@Removed) s.unit_Cost
                    ,s.fifo_rank
                    ,s.quantity
                FROM #MovingInventory AS s
                CROSS APPLY (
                    SELECT TOP (CAST(s.Quantity AS INT)) ROW_NUMBER() OVER (
                            ORDER BY number
                            ) AS n
                    FROM dbo.numbers n5
                    WHERE number > 0
                    ) AS f(n)
                WHERE s.InvNo = @InvNo
                    AND s.fifo_rank < @fifo_rank
                ORDER BY s.fifo_rank
                )
                ,cteRemoved
            AS (
                SELECT unit_Cost
                    ,fifo_rank
                    ,quantity
                    ,COUNT(*) AS Removed
                FROM cteSource
                GROUP BY unit_Cost
                    ,fifo_rank
                    ,quantity
                )
            UPDATE M
            SET Removed = R.Removed
                ,CurrentQuantity = M.CurrentQuantity - R.Removed
            FROM #MovingInventory M
            INNER JOIN cteRemoved R ON M.fifo_rank = R.fifo_rank
            WHERE M.InvNo = @InvNo;
                -- We can also check if Removed = @Removed (if less, we have negative inventory - unlikely situation)
        END

Here I am attempting to calculate our current working inventory in one step. I get the total sold quantity and last date (fifo_rank) when it was sold prior to dtStart and then distribute that sold quantity among all prior additions into inventory.

Here I am not considering situations when somehow we already sold more than we had in the inventory originally or when we returned more than sold (so total quantity will be greater than 0). To be honest, I am not 100% sure how to treat these situations, so I assume that possibility of them occurring is very low.

Once we got the inventory up to the starting date (dtStart) I am ready to process each individual sale or return. Here is how I do it for Sales and negative transfers:

WHILE (@@FETCH_STATUS = 0)
        BEGIN
            IF @quantity < 0 -- Sale or transfer
            BEGIN
                IF @Debug = 1
                BEGIN
                    SET @Message = 'Sale or transfer with quantity = ' + CAST(- 1 * @quantity AS VARCHAR(20))
 
                    RAISERROR (
                            @Message
                            ,10
                            ,1
                            )
                    WITH NOWAIT;
                END
 
                SELECT @Removed = - 1 * @quantity;
 
                WITH cteSource
                AS (
                    SELECT TOP (@Removed) s.unit_Cost
                        ,s.fifo_rank
                        ,s.CurrentQuantity
                    FROM #MovingInventory AS s
                    CROSS APPLY (
                        SELECT TOP (s.CurrentQuantity) ROW_NUMBER() OVER (
                                ORDER BY number
                                ) AS n
                        FROM dbo.numbers n5
                        WHERE number > 0
                        ) AS f(n)
                    WHERE s.InvNo = @InvNo
                        AND s.fifo_rank < @fifo_rank
                        AND s.CurrentQuantity > 0
                    ORDER BY s.fifo_rank
                    )
                    ,cteRemoved
                AS (
                    SELECT unit_Cost
                        ,fifo_rank
                        ,CurrentQuantity
                        ,COUNT(*) AS Removed
                    FROM cteSource
                    GROUP BY unit_Cost
                        ,fifo_rank
                        ,CurrentQuantity
                    )
                UPDATE I
                SET CurrentQuantity = I.CurrentQuantity - R.Removed
                    ,Removed = I.Removed + R.Removed
                OUTPUT Inserted.unit_cost
                    ,Inserted.Removed - deleted.Removed
                INTO #Removed(unit_cost, Removed)
                FROM #MovingInventory I
                INNER JOIN cteRemoved R ON I.fifo_rank = R.fifo_rank
                WHERE I.InvNo = @InvNo;
 
                IF @Debug = 1
                BEGIN
                    SELECT *
                    FROM #MovingInventory I
                    WHERE I.InvNo = @InvNo;
 
                    RAISERROR (
                            'Current Moving Inventory after Sale or Return'
                            ,10
                            ,1
                            )
                    WITH NOWAIT
                END
 
                IF @trans_type = 'S'
                    AND @date_time >= @dtStart
                    INSERT INTO #Sales (
                        trans_no
                        ,InvNo
                        ,locatn_id
                        ,date_time
                        ,department
                        ,category
                        ,item
                        ,invent_id
                        ,unit_cost
                        ,quantity
                        )
                    SELECT @ref_no
                        ,@InvNo
                        ,@locatn_id
                        ,@date_time
                        ,@department
                        ,@category
                        ,@item
                        ,@invent_id
                        ,unit_cost
                        ,Removed
                    FROM #Removed;
 
                --- Need to check for situations when we sell more than currently in the inventory (rare cases)
                SELECT @Difference = @Removed - COALESCE((
                            SELECT SUM(Removed)
                            FROM #Removed
                            ), 0);
 
                IF @Difference > 0 -- Sold more than were in the inventory
                BEGIN
                    IF @Debug = 1
                    BEGIN
                        SET @Message = 'Sold more than in the inventory - Difference = ' + CAST(@Difference AS VARCHAR(10))
 
                        RAISERROR (
                                @Message
                                ,10
                                ,1
                                )
                        WITH NOWAIT;
                    END
 
                    SET @LastCost = 0;
 
                    SELECT @LastCost = LastCost.LastCost
                    FROM dbo.siriusfn_LastCostUpToDate(@department, @category, @item, @invent_id, @date_time) LastCost;
  IF @trans_type = 'S' -- only insert sales/returns
                    INSERT INTO #Sales (
                        trans_no
                        ,InvNo
                        ,locatn_id
                        ,date_time
                        ,department
                        ,category
                        ,item
                        ,invent_id
                        ,unit_cost
                        ,quantity
                        )
                    SELECT @ref_no
                        ,@InvNo
                        ,@locatn_id
                        ,@date_time
                        ,@department
                        ,@category
                        ,@item
                        ,@invent_id
                        ,@LastCost
                        ,@Difference

So, for each sale (or negative transfer) I use the same idea as in calculating starting inventory. I remove the sold quantity distributing it among rows where current quantity > 0 ordering by date_time (fifo_rank) column. I then update the #MovingInventory table (current quantity and Removed columns) and I output results using OUTPUT keyword for UPDATE into #Removed table. In addition, I populate #Sales table if the Trans_Type is 'S' (sales) to be used in the final select statement.

I also try to consider situations when we sold (or moved out) more than we have in the inventory. In this case we're using Last Cost for the item.
Here lies another problem not currently considered - if we have the negative quantity balance, we need to keep decrementing that difference after we receive that item. This is not currently done in my procedure - so we may get incorrect Cost of Goods Sold in such scenarios. I may need to think more how to handle this problem.

For the returns I am using a similar process to what I use for Sales, but I try to return back what I've already removed in the opposite direction (e.g. last removed - first returned). So, this is how I handle returns:

SELECT @Returned = @quantity;
 
            WITH cteSource
            AS (
                SELECT TOP (@Returned) s.unit_Cost
                    ,s.fifo_rank
                    ,s.quantity
                FROM #MovingInventory AS s
                CROSS APPLY (
                    SELECT TOP (s.Removed - s.Returned) ROW_NUMBER() OVER (
                            ORDER BY number
                            ) AS n
                    FROM dbo.numbers n5
                    WHERE number > 0
                    ) AS f(n)
                WHERE s.InvNo = @InvNo
                    AND s.fifo_rank < @fifo_rank
                    AND (s.Removed - s.Returned) > 0
                ORDER BY s.fifo_rank DESC -- returns in the LIFO order
                )
                ,cteReturned
            AS (
                SELECT unit_Cost
                    ,fifo_rank
                    ,quantity
                    ,COUNT(*) AS Returned
                FROM cteSource
                GROUP BY unit_Cost
                    ,fifo_rank
                    ,quantity
                )
            UPDATE I
            SET CurrentQuantity = I.CurrentQuantity + R.Returned
                ,Returned = I.Returned + R.Returned
            OUTPUT Inserted.unit_cost
                ,Inserted.Returned - deleted.Returned
            INTO #Removed(unit_cost, Removed)
            FROM #MovingInventory I
            INNER JOIN cteReturned R ON I.fifo_rank = R.fifo_rank
            WHERE I.InvNo = @InvNo;
 
            IF @Debug = 1
            BEGIN
                SELECT *
                FROM #MovingInventory I
                WHERE I.InvNo = @InvNo;
 
                RAISERROR (
                        'Result after return'
                        ,10
                        ,1
                        )
                WITH NOWAIT;
            END
 
            IF @trans_type = 'S'
                AND @date_time >= @dtStart
                INSERT INTO #Sales (
                    trans_no
                    ,InvNo
                    ,locatn_id
                    ,date_time
                    ,department
                    ,category
                    ,item
                    ,invent_id
                    ,unit_cost
                    ,quantity
                    )
                SELECT @ref_no
                    ,@InvNo
                    ,@locatn_id
                    ,@date_time
                    ,@department
                    ,@category
                    ,@item
                    ,@invent_id
                    ,unit_cost
                    ,(- 1) * Removed
                FROM #Removed;-- handle returns
                    -- Need to check for situations when we return what we didn't have in the inventory before
 
            IF @Debug = 1
            BEGIN
                SELECT *
                FROM #Sales;
 
                RAISERROR (
                        'Current Sales after return'
                        ,10
                        ,1
                        )
                WITH NOWAIT;
            END
 
            SELECT @Difference = @Returned - COALESCE((
                        SELECT SUM(Removed)
                        FROM #Removed
                        ), 0);
 
            IF @Difference > 0 -- Returned more than were in the inventory originally, use Last Cost
            BEGIN
                IF @Debug = 1
                BEGIN
                    SET @Message = 'Returned more than removed - Difference= ' + CAST(@Difference AS VARCHAR(10)) + ' Last Cost = ' + CAST(@LastCost AS VARCHAR(20));
 
                    RAISERROR (
                            @Message
                            ,10
                            ,1
                            )
                    WITH NOWAIT;
                END
 
                SET @LastCost = 0;
 
                SELECT @LastCost = LastCost.LastCost
                FROM dbo.siriusfn_LastCostUpToDate(@department, @category, @item, @invent_id, @date_time) LastCost;
  IF @trans_type = 'S' -- only insert sales/returns
                INSERT INTO #Sales (
                    trans_no
                    ,InvNo
                    ,locatn_id
                    ,date_time
                    ,department
                    ,category
                    ,item
                    ,invent_id
                    ,unit_cost
                    ,quantity
                    )
                SELECT @ref_no
                    ,@InvNo
                    ,@locatn_id
                    ,@date_time
                    ,@department
                    ,@category
                    ,@item
                    ,@invent_id
                    ,@LastCost
                    ,- 1 * @Difference;
            END
        END


Here again if we returned back more than we originally removed, I am returning using the last known cost for the item.

The Cost of Goods Sold FIFO procedure



Now I will give you the whole procedure code and hopefully you will see my logic. I also will appreciate comments or code corrections as this is still a work in progress and hasn't been tested extensively yet.

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
SET NOCOUNT ON;
 
---------------- #Inventory test object creation so the script below doesn't complain about #Inventory table -----------
IF OBJECT_ID('tempdb..#Inventory', N'U') IS NOT NULL
    DROP TABLE #Inventory;
 
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
    )
 
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
    )
GO
 
IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_NAME = 'siriussp_CostOfGoodsSold_FIFO'
            AND ROUTINE_TYPE = 'PROCEDURE'
        )
    EXECUTE ('CREATE PROCEDURE dbo.siriussp_CostOfGoodsSold_FIFO AS SET NOCOUNT ON;');
GO
 
ALTER PROCEDURE dbo.siriussp_CostOfGoodsSold_FIFO (
    @dtStart DATETIME
    ,@Debug BIT = 0
    )
    --=============================================================
    /*  SP that returns total quantity and cost of goods sold
    by department, category, item, invent_id, and locatn_id,
    using FIFO (First IN, First OUT) method of cost valuation.
    Modified on 07/10/2012  
    Modified on 07/19/2013 - 7/26/2013
--=============================================================
*/
AS
BEGIN
    SET NOCOUNT ON;
 
    --IF CAST(LEFT(CAST(serverproperty('ProductVersion') AS VARCHAR(max)), 2) AS DECIMAL(10, 2)) >= 11
    --  AND OBJECT_ID('dbo.siriussp_CostOfGoodsSold_FIFO_2012', 'P') IS NOT NULL
    --BEGIN
    --  PRINT 'Using 2012 version of the stored procedure'
    --  EXECUTE sp_ExecuteSQL N'EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO_2012 @dtStart, @Debug'
    --      ,N'@dtStart DATETIME, @Debug BIT'
    --      ,@dtStart, @Debug ;
    --  RETURN;
    --END
    --PRINT 'Using cursor based version of the stored procedure'
    IF OBJECT_ID('TempDB..#Sales', N'U') IS NOT NULL
        DROP TABLE #Sales;
 
    CREATE TABLE [dbo].[#Sales] (
        InvNo INT NOT NULL
        ,[trans_no] [numeric](17, 0) NOT NULL
        ,[locatn_id] [int] NOT NULL
        ,[date_time] [datetime] NOT NULL
        ,[department] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
        ,[category] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
        ,[item] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
        ,[invent_id] [int] NOT NULL
        ,quantity INT
        ,unit_cost MONEY
        )
 
    IF OBJECT_ID('TempDB..#Removed', N'U') IS NOT NULL
        DROP TABLE #Removed;
 
    CREATE TABLE [dbo].[#Removed] (
        unit_cost MONEY
        ,Removed INT
        )
 
    IF OBJECT_ID('TempDB..#MovingInventory', N'U') IS NOT NULL
        DROP TABLE #MovingInventory;
 
    CREATE TABLE [dbo].[#MovingInventory] (
        InvNo INT NOT NULL
        ,fifo_rank INT NOT NULL
        ,quantity INT
        ,unit_cost MONEY
        ,Removed INT
        ,Returned INT
        ,CurrentQuantity INT
        ,CONSTRAINT pkMovingInventory PRIMARY KEY (
            InvNo
            ,fifo_rank
            )
        )
 
    INSERT INTO #MovingInventory (
        InvNo
        ,fifo_rank
        ,quantity
        ,unit_cost
        ,Removed
        ,Returned
        ,CurrentQuantity
        )
    SELECT InvNo
        ,fifo_rank
        ,quantity
        ,unit_cost
        ,0
        ,0
        ,quantity
    FROM #Inventory
    WHERE trans_type IN (
            'P'
            ,'A'
            ,'T'
            )
        AND quantity > 0
    ORDER BY InvNo
        ,fifo_rank;
 
    IF NOT EXISTS (
            SELECT NAME
            FROM TempDB.sys.sysindexes
            WHERE NAME = 'idx_Inventory_fifo_rank'
            )
        CREATE INDEX idx_Inventory_fifo_rank ON #Inventory (
            InvNo
            ,fifo_rank
            );
 
    DECLARE @InvNo INT
        ,@ref_no NUMERIC(17, 0)
        ,@locatn_id INT
        ,@date_time DATETIME
        ,@fifo_rank INT
        ,@department CHAR(10)
        ,@category CHAR(10)
        ,@item CHAR(10)
        ,@invent_id INT
        ,@trans_type CHAR(1)
        ,@quantity INT
        ,@unit_cost MONEY
        ,@LastCost MONEY
        ,@CurInvNo INT
        ,@Removed INT
        ,@Returned INT
        ,@Elapsed INT
        ,@StartTime DATETIME
        ,@Message VARCHAR(MAX)
        ,@Difference INT;
 
    SET @StartTime = CURRENT_TIMESTAMP;
 
    DECLARE curMainProcess CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT DISTINCT InvNo
    FROM #Inventory
    ORDER BY InvNo;
 
    OPEN curMainProcess;
 
    FETCH NEXT
    FROM curMainProcess
    INTO @InvNo;
 
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SELECT @fifo_rank = MAX(fifo_rank)
            ,@Removed = - 1 * SUM(quantity)
        FROM #Inventory
        WHERE date_time < @dtStart
            AND (
                trans_type = 'S'
                OR quantity < 0
                )
            AND InvNo = @InvNo;
 
        IF COALESCE(@Removed, 0) > 0 -- what to do when we start with returns - unlikely to happen, though?
        BEGIN
            IF @Debug = 1
                PRINT 'Calculating starting inventory';
 
            WITH cteSource
            AS (
                SELECT TOP (@Removed) s.unit_Cost
                    ,s.fifo_rank
                    ,s.quantity
                FROM #MovingInventory AS s
                CROSS APPLY (
                    SELECT TOP (CAST(s.Quantity AS INT)) ROW_NUMBER() OVER (
                            ORDER BY number
                            ) AS n
                    FROM dbo.numbers n5
                    WHERE number > 0
                    ) AS f(n)
                WHERE s.InvNo = @InvNo
                    AND s.fifo_rank < @fifo_rank
                ORDER BY s.fifo_rank
                )
                ,cteRemoved
            AS (
                SELECT unit_Cost
                    ,fifo_rank
                    ,quantity
                    ,COUNT(*) AS Removed
                FROM cteSource
                GROUP BY unit_Cost
                    ,fifo_rank
                    ,quantity
                )
            UPDATE M
            SET Removed = R.Removed
                ,CurrentQuantity = M.CurrentQuantity - R.Removed
            FROM #MovingInventory M
            INNER JOIN cteRemoved R ON M.fifo_rank = R.fifo_rank
            WHERE M.InvNo = @InvNo;
                -- We can also check if Removed = @Removed (if less, we have negative inventory - unlikely situation)
        END
 
        IF @Debug = 1
        BEGIN
            SELECT *
            FROM #MovingInventory
            WHERE InvNo = @InvNo;
 
            RAISERROR (
                    'Done with the prior inventory - starting checking Sales we''re interested in'
                    ,10
                    ,1
                    )
            WITH NOWAIT;
        END
 
        DECLARE curProcess CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
        FOR
        SELECT InvNo
            ,ref_no
            ,date_time
            ,fifo_rank
            ,quantity
            ,unit_cost
            ,trans_type
            ,department
            ,category
            ,item
            ,invent_id
            ,locatn_id
        FROM #Inventory
        WHERE InvNo = @InvNo
            AND (
                trans_type = 'S'
                OR quantity < 0
                )
            AND date_time >= @dtStart -- now process only the Sales we're interested in
        ORDER BY InvNo
            ,fifo_rank
 
        OPEN curProcess
 
        FETCH NEXT
        FROM curProcess
        INTO @InvNo
            ,@ref_no
            ,@date_time
            ,@fifo_rank
            ,@quantity
            ,@unit_cost
            ,@trans_type
            ,@department
            ,@category
            ,@item
            ,@invent_id
            ,@locatn_id
 
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            IF @quantity < 0 -- Sale or transfer
            BEGIN
                IF @Debug = 1
                BEGIN
                    SET @Message = 'Sale or transfer with quantity = ' + CAST(- 1 * @quantity AS VARCHAR(20))
 
                    RAISERROR (
                            @Message
                            ,10
                            ,1
                            )
                    WITH NOWAIT;
                END
 
                SELECT @Removed = - 1 * @quantity;
 
                WITH cteSource
                AS (
                    SELECT TOP (@Removed) s.unit_Cost
                        ,s.fifo_rank
                        ,s.CurrentQuantity
                    FROM #MovingInventory AS s
                    CROSS APPLY (
                        SELECT TOP (s.CurrentQuantity) ROW_NUMBER() OVER (
                                ORDER BY number
                                ) AS n
                        FROM dbo.numbers n5
                        WHERE number > 0
                        ) AS f(n)
                    WHERE s.InvNo = @InvNo
                        AND s.fifo_rank < @fifo_rank
                        AND s.CurrentQuantity > 0
                    ORDER BY s.fifo_rank
                    )
                    ,cteRemoved
                AS (
                    SELECT unit_Cost
                        ,fifo_rank
                        ,CurrentQuantity
                        ,COUNT(*) AS Removed
                    FROM cteSource
                    GROUP BY unit_Cost
                        ,fifo_rank
                        ,CurrentQuantity
                    )
                UPDATE I
                SET CurrentQuantity = I.CurrentQuantity - R.Removed
                    ,Removed = I.Removed + R.Removed
                OUTPUT Inserted.unit_cost
                    ,Inserted.Removed - deleted.Removed
                INTO #Removed(unit_cost, Removed)
                FROM #MovingInventory I
                INNER JOIN cteRemoved R ON I.fifo_rank = R.fifo_rank
                WHERE I.InvNo = @InvNo;
 
                IF @Debug = 1
                BEGIN
                    SELECT *
                    FROM #MovingInventory I
                    WHERE I.InvNo = @InvNo;
 
                    RAISERROR (
                            'Current Moving Inventory after Sale or Return'
                            ,10
                            ,1
                            )
                    WITH NOWAIT
                END
 
                IF @trans_type = 'S'
                    AND @date_time >= @dtStart
                    INSERT INTO #Sales (
                        trans_no
                        ,InvNo
                        ,locatn_id
                        ,date_time
                        ,department
                        ,category
                        ,item
                        ,invent_id
                        ,unit_cost
                        ,quantity
                        )
                    SELECT @ref_no
                        ,@InvNo
                        ,@locatn_id
                        ,@date_time
                        ,@department
                        ,@category
                        ,@item
                        ,@invent_id
                        ,unit_cost
                        ,Removed
                    FROM #Removed;
 
                --- Need to check for situations when we sell more than currently in the inventory (rare cases)
                SELECT @Difference = @Removed - COALESCE((
                            SELECT SUM(Removed)
                            FROM #Removed
                            ), 0);
 
                IF @Difference > 0 -- Sold more than were in the inventory
                BEGIN
                    IF @Debug = 1
                    BEGIN
                        SET @Message = 'Sold more than in the inventory - Difference = ' + CAST(@Difference AS VARCHAR(10))
 
                        RAISERROR (
                                @Message
                                ,10
                                ,1
                                )
                        WITH NOWAIT;
                    END
 
                    SET @LastCost = 0;
 
                    SELECT @LastCost = LastCost.LastCost
                    FROM dbo.siriusfn_LastCostUpToDate(@department, @category, @item, @invent_id, @date_time) LastCost;
 
                    IF @trans_type = 'S' -- only insert sales / returns
                        INSERT INTO #Sales (
                            trans_no
                            ,InvNo
                            ,locatn_id
                            ,date_time
                            ,department
                            ,category
                            ,item
                            ,invent_id
                            ,unit_cost
                            ,quantity
                            )
                        SELECT @ref_no
                            ,@InvNo
                            ,@locatn_id
                            ,@date_time
                            ,@department
                            ,@category
                            ,@item
                            ,@invent_id
                            ,@LastCost
                            ,@Difference
 
                    IF @Debug = 1
                    BEGIN
                        SET @Message = 'Last Cost = ' + CAST(@LastCost AS VARCHAR(10))
 
                        RAISERROR (
                                @Message
                                ,10
                                ,1
                                )
                        WITH NOWAIT;
 
                        SELECT *
                        FROM #Sales
 
                        RAISERROR (
                                'Currently in #Sales'
                                ,10
                                ,1
                                )
                        WITH NOWAIT;
                    END
                END
            END
            ELSE -- Returns
            BEGIN
                IF @Debug = 1
                BEGIN
                    SET @Message = 'Return with quantity = ' + CAST(@quantity AS VARCHAR(20));
 
                    RAISERROR (
                            @Message
                            ,10
                            ,1
                            )
                    WITH NOWAIT;
                END
 
                SELECT @Returned = @quantity;
 
                WITH cteSource
                AS (
                    SELECT TOP (@Returned) s.unit_Cost
                        ,s.fifo_rank
                        ,s.quantity
                    FROM #MovingInventory AS s
                    CROSS APPLY (
                        SELECT TOP (s.Removed - s.Returned) ROW_NUMBER() OVER (
                                ORDER BY number
                                ) AS n
                        FROM dbo.numbers n5
                        WHERE number > 0
                        ) AS f(n)
                    WHERE s.InvNo = @InvNo
                        AND s.fifo_rank < @fifo_rank
                        AND (s.Removed - s.Returned) > 0
                    ORDER BY s.fifo_rank DESC -- returns in the LIFO order
                    )
                    ,cteReturned
                AS (
                    SELECT unit_Cost
                        ,fifo_rank
                        ,quantity
                        ,COUNT(*) AS Returned
                    FROM cteSource
                    GROUP BY unit_Cost
                        ,fifo_rank
                        ,quantity
                    )
                UPDATE I
                SET CurrentQuantity = I.CurrentQuantity + R.Returned
                    ,Returned = I.Returned + R.Returned
                OUTPUT Inserted.unit_cost
                    ,Inserted.Returned - deleted.Returned
                INTO #Removed(unit_cost, Removed)
                FROM #MovingInventory I
                INNER JOIN cteReturned R ON I.fifo_rank = R.fifo_rank
                WHERE I.InvNo = @InvNo;
 
                IF @Debug = 1
                BEGIN
                    SELECT *
                    FROM #MovingInventory I
                    WHERE I.InvNo = @InvNo;
 
                    RAISERROR (
                            'Result after return'
                            ,10
                            ,1
                            )
                    WITH NOWAIT;
                END
 
                IF @trans_type = 'S'
                    AND @date_time >= @dtStart
                    INSERT INTO #Sales (
                        trans_no
                        ,InvNo
                        ,locatn_id
                        ,date_time
                        ,department
                        ,category
                        ,item
                        ,invent_id
                        ,unit_cost
                        ,quantity
                        )
                    SELECT @ref_no
                        ,@InvNo
                        ,@locatn_id
                        ,@date_time
                        ,@department
                        ,@category
                        ,@item
                        ,@invent_id
                        ,unit_cost
                        ,(- 1) * Removed
                    FROM #Removed;-- handle returns
                        -- Need to check for situations when we return what we didn't have in the inventory before
 
                IF @Debug = 1
                BEGIN
                    SELECT *
                    FROM #Sales;
 
                    RAISERROR (
                            'Current Sales after return'
                            ,10
                            ,1
                            )
                    WITH NOWAIT;
                END
 
                SELECT @Difference = @Returned - COALESCE((
                            SELECT SUM(Removed)
                            FROM #Removed
                            ), 0);
 
                IF @Difference > 0 -- Returned more than were in the inventory originally, use Last Cost
                BEGIN
                    IF @Debug = 1
                    BEGIN
                        SET @Message = 'Returned more than removed - Difference= ' + CAST(@Difference AS VARCHAR(10)) + ' Last Cost = ' + CAST(@LastCost AS VARCHAR(20));
 
                        RAISERROR (
                                @Message
                                ,10
                                ,1
                                )
                        WITH NOWAIT;
                    END
 
                    SET @LastCost = 0;
 
                    SELECT @LastCost = LastCost.LastCost
                    FROM dbo.siriusfn_LastCostUpToDate(@department, @category, @item, @invent_id, @date_time) LastCost;
 
                    IF @trans_type = 'S' -- only insert sales/returns
                        INSERT INTO #Sales (
                            trans_no
                            ,InvNo
                            ,locatn_id
                            ,date_time
                            ,department
                            ,category
                            ,item
                            ,invent_id
                            ,unit_cost
                            ,quantity
                            )
                        SELECT @ref_no
                            ,@InvNo
                            ,@locatn_id
                            ,@date_time
                            ,@department
                            ,@category
                            ,@item
                            ,@invent_id
                            ,@LastCost
                            ,- 1 * @Difference;
                END
            END
 
            TRUNCATE TABLE #Removed;-- done with this table for this iteration     
 
            FETCH NEXT
            FROM curProcess
            INTO @InvNo
                ,@ref_no
                ,@date_time
                ,@fifo_rank
                ,@quantity
                ,@unit_cost
                ,@trans_type
                ,@department
                ,@category
                ,@item
                ,@invent_id
                ,@locatn_id
        END -- while
 
        CLOSE curProcess
 
        DEALLOCATE curProcess
 
        FETCH NEXT
        FROM curMainProcess
        INTO @InvNo
    END -- while
 
    CLOSE curMainProcess
 
    DEALLOCATE curMainProcess
 
    IF @Debug = 1
    BEGIN
        SET @Elapsed = datediff(second, @StartTime, CURRENT_TIMESTAMP);
 
        PRINT ' Finished with the creation of #Sales tables using cursor in ' + cast(@Elapsed AS VARCHAR(30)) + ' seconds';
    END
 
    SELECT S.trans_no
        ,S.department
        ,S.category
        ,S.item
        ,S.invent_id
        ,S.locatn_id
        ,SUM(S.quantity) AS QuantitySold
        ,CAST(SUM(S.quantity * S.unit_cost) AS MONEY) AS CostOfGoodsSold
    FROM #Sales S
    GROUP BY S.trans_no
        ,S.department
        ,S.category
        ,S.item
        ,S.invent_id
        ,S.locatn_id;
 
    IF @Debug = 1
    BEGIN
        SET @Elapsed = datediff(second, @StartTime, CURRENT_TIMESTAMP);
 
        PRINT ' Finished with the final selection in ' + cast(@Elapsed AS VARCHAR(30)) + ' seconds';
    END
END
 
RETURN;
GO
 
/* Test Cases
IF OBJECT_ID('TempDB..#Inventory',N'U') IS NOT NULL DROP TABLE #Inventory;
CREATE TABLE [dbo].[#Inventory](
      [InvNo] [int] NOT NULL,
      [ref_no] [numeric](17, 0) NOT NULL,
      [locatn_id] [int] NOT NULL,
      [date_time] [datetime] NOT NULL,
      [fifo_rank] [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
)
;with cte as (SELECT N'25' AS [ref_no], N'1' AS [locatn_id], N'2012-06-29 16:48:39.000' AS [date_time], N'1' AS [fifo_rank], N'1' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BATT_TEST' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'100.00' AS [quantity], N'1.00' AS [unit_cost] UNION ALL
SELECT N'133005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-29 17:00:13.000' AS [date_time], N'2' AS [fifo_rank], N'1' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BATT_TEST' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-90.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'25' AS [ref_no], N'1' AS [locatn_id], N'2012-06-29 17:26:47.000' AS [date_time], N'3' AS [fifo_rank], N'1' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BATT_TEST' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'100.00' AS [quantity], N'2.00' AS [unit_cost] UNION ALL
SELECT N'135005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-29 17:28:19.000' AS [date_time], N'4' AS [fifo_rank], N'1' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BATT_TEST' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'10.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 11:58:26.000' AS [date_time], N'1' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'10.00' AS [quantity], N'2.00' AS [unit_cost] UNION ALL
SELECT N'129005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 13:02:57.000' AS [date_time], N'2' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-9.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 13:06:07.000' AS [date_time], N'3' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'10.00' AS [quantity], N'2.6667' AS [unit_cost] UNION ALL
SELECT N'130005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 13:17:46.000' AS [date_time], N'4' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-7.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'131005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 13:18:16.000' AS [date_time], N'5' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'3.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'24' AS [ref_no], N'3' AS [locatn_id], N'2012-06-27 11:57:17.000' AS [date_time], N'1' AS [fifo_rank], N'3' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'20.00' AS [quantity], N'2.00' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'3' AS [locatn_id], N'2012-06-27 11:58:26.000' AS [date_time], N'2' AS [fifo_rank], N'3' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-10.00' AS [quantity], N'2.00' AS [unit_cost] UNION ALL
SELECT N'24' AS [ref_no], N'3' AS [locatn_id], N'2012-06-27 13:04:29.000' AS [date_time], N'3' AS [fifo_rank], N'3' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'20.00' AS [quantity], N'3.00' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'3' AS [locatn_id], N'2012-06-27 13:06:07.000' AS [date_time], N'4' AS [fifo_rank], N'3' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-10.00' AS [quantity], N'2.6667' AS [unit_cost] UNION ALL
SELECT N'4' AS [ref_no], N'1' AS [locatn_id], N'2011-04-03 18:34:44.000' AS [date_time], N'1' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'24.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
SELECT N'11005001' AS [ref_no], N'1' AS [locatn_id], N'2011-04-07 09:57:51.000' AS [date_time], N'2' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'33005001' AS [ref_no], N'1' AS [locatn_id], N'2011-04-07 10:04:39.000' AS [date_time], N'3' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'103005001' AS [ref_no], N'1' AS [locatn_id], N'2011-07-06 17:55:17.000' AS [date_time], N'4' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'108005001' AS [ref_no], N'1' AS [locatn_id], N'2011-07-06 17:55:47.000' AS [date_time], N'5' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'115005001' AS [ref_no], N'1' AS [locatn_id], N'2011-08-01 17:47:11.000' AS [date_time], N'6' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'41005001' AS [ref_no], N'1' AS [locatn_id], N'2011-09-04 11:24:03.000' AS [date_time], N'7' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-2.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'48005001' AS [ref_no], N'1' AS [locatn_id], N'2011-09-04 11:38:31.000' AS [date_time], N'8' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-3.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'65005001' AS [ref_no], N'1' AS [locatn_id], N'2011-09-04 11:59:59.000' AS [date_time], N'9' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'1' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 17:02:19.000' AS [date_time], N'10' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'A' AS [trans_type], N'5.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 17:09:46.000' AS [date_time], N'11' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'A' AS [trans_type], N'5.00' AS [quantity], N'0.10' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 17:15:05.000' AS [date_time], N'12' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'5.00' AS [quantity], N'0.5469' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 17:15:47.000' AS [date_time], N'13' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'5.00' AS [quantity], N'0.5469' AS [unit_cost] UNION ALL
SELECT N'125005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 18:00:26.000' AS [date_time], N'14' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-10.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'126005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 18:01:05.000' AS [date_time], N'15' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'5.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'127005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 18:02:07.000' AS [date_time], N'16' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-50.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'128005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 18:02:51.000' AS [date_time], N'17' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'30.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'5' AS [ref_no], N'3' AS [locatn_id], N'2011-04-03 16:41:21.000' AS [date_time], N'1' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'60.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
SELECT N'1' AS [ref_no], N'3' AS [locatn_id], N'2011-04-03 17:46:45.000' AS [date_time], N'2' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'A' AS [trans_type], N'-2.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
SELECT N'4' AS [ref_no], N'3' AS [locatn_id], N'2011-04-03 18:34:44.000' AS [date_time], N'3' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-24.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
SELECT N'23' AS [ref_no], N'3' AS [locatn_id], N'2012-06-26 17:00:58.000' AS [date_time], N'4' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'10.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
SELECT N'23' AS [ref_no], N'3' AS [locatn_id], N'2012-06-26 17:04:59.000' AS [date_time], N'5' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'20.00' AS [quantity], N'0.10' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'3' AS [locatn_id], N'2012-06-26 17:15:05.000' AS [date_time], N'6' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-5.00' AS [quantity], N'0.5469' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'3' AS [locatn_id], N'2012-06-26 17:15:47.000' AS [date_time], N'7' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-5.00' AS [quantity], N'0.5469' AS [unit_cost] )
insert #Inventory ([ref_no], [locatn_id], [date_time], [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost])
SELECT [ref_no], [locatn_id], [date_time], [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost]
from cte
--CREATE INDEX idx_Inventory_fifo_rank ON #Inventory (InvNo, fifo_rank) 
 
SELECT * FROM #Inventory
DECLARE @Time datetime, @Elapsed int, @dtStart datetime
set @dtStart = '20120629'
SET @time = GETDATE()
EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO_TEST   @dtStart =  '20010629'
set @Elapsed = DATEDIFF(second,@time, getdate())
print 'Elapsed for SQL 2005-2008: - cursor version ' + convert(varchar(10),@Elapsed) + ' seconds'
SET @time = GETDATE()
 
SET @time = GETDATE()
EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO   @dtStart=  '20010629'
set @Elapsed = DATEDIFF(second,@time, getdate())
print 'Elapsed for SQL 2005-2008: - Prior cursor version ' + convert(varchar(10),@Elapsed) + ' seconds'
 
--EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO_2012          '20010629'
--SET @time = GETDATE()
--set @Elapsed = DATEDIFF(second,@time, getdate())
--print 'Elapsed for SQL 2012: ' + convert(varchar(10),@Elapsed) + ' seconds'
go
  
*/


At the top of the script code I provided #Inventory table for the original failing scenario in order to confirm that it works correctly with the new code. I also have a scenario I tested originally in the comments after the stored procedure.

Summary


In this article I described the process of working on a complex problem of Calculating Cost of Goods Sold using FIFO method and gave my current procedure code. I also showed potential problems and flaws in that code. I will appreciate comments and ideas of improving this algorithm.


See Also



This article participated in the TechNet Guru Competition for July 2013 and won the Silver Prize.