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` `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;`

`                    ``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;`

`                ``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;`

`                    ``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;`

`                    ``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.