T-SQL: FIFO Inventory Problem - Cost of Goods Sold
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".
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:
I just re-read that long thread. Essentially, I confirmed that using inline UDF to calculate Cost of Goods on Hand for the selected inventory and using CROSS APPLY with that function was very slow compared to getting the inventory to work with into a temporary table first and then apply the calculations as a stored procedure.
It also started to become clear that using our current structure of the table and not having anything pre-calculated will lead to bad performance as we need to re-calculate the cost every time from the very beginning. About a year or so ago I proposed a plan to re-design our inventory table by adding few more tables we will be updating at the same time as transaction occur. Unfortunately, we haven't proceed in this direction yet and I don't know if we ever going to look into these ideas in order to make the calculation process easier.
In this article I planned to discuss the Cost of Goods Sold calculations, so I will just give the current code of the Cost of Goods on Hand FIFO procedure without too many explanations.
Current procedure to calculate Cost of Goods on Hand
--==========================================================
/* SP that returns total quantity and cost of goods on hand
by department, category, item, invent_id, and locatn_id,
using FIFO (first in/first out) method of cost valuation:
To retrieve the total (FIFO) cost of goods on hand
for all inventory by location, by department:
EXECUTE dbo.siriussp_CostOfGoodsOnHand_FIFO 1
locatn_id department QuantityOnHand CostOfGoodsOnHand
----------- ---------- -------------- ---------------------
-999 RETAIL 2 0.90
1 RETAIL 2359 31567.73
3 RETAIL 1609 19001.21
*/
--=========================================================
ALTER PROCEDURE dbo.siriussp_CostOfGoodsOnHand_FIFO
(
@bIncludeZeroes BIT = 1 /* If 1, then include records for items with zero on-hand */
)
AS
BEGIN
SET NOCOUNT ON;
WITH cteInventorySum
AS (SELECT department,
category,
item,
invent_ID,
locatn_ID,
SUM(quantity) AS TotalInventory,
MAX(date_time) AS LastDateTime
FROM #Inventory
GROUP BY department,
category,
item,
invent_ID,
locatn_ID),
cteReverseInSum
AS (/* Perform a rolling balance ( in reverse order ) through the inventory movements in */
SELECT s.department,
s.category,
s.item,
s.invent_ID,
s.locatn_ID,
s.Fifo_Rank,
(SELECT SUM(i.quantity)
FROM #Inventory AS i
WHERE i.department = s.department
AND i.category = s.category
AND i.item = s.item
AND i.invent_id = s.invent_id
AND i.locatn_id = s.locatn_id
AND i.trans_Type IN ('P','A','T')
AND i.Fifo_Rank >= s.Fifo_Rank) AS RollingInventory,
SUM(s.Quantity) AS ThisInventory
FROM #Inventory AS s
WHERE s.Trans_Type IN ('P','A','T')
GROUP BY s.Department,
s.Category,
s.Item,
s.Invent_ID,
s.Locatn_ID,
s.Fifo_Rank),
cteWithLastTranDate
AS (SELECT w.Department,
w.Category,
w.Item,
w.Invent_ID,
w.Locatn_ID,
w.LastDateTime,
w.TotalInventory,
COALESCE(LastPartialInventory.Fifo_Rank,0) AS Fifo_Rank,
COALESCE(LastPartialInventory.InventoryToUse,0) AS InventoryToUse,
COALESCE(LastPartialInventory.RunningTotal,0) AS RunningTotal,
w.TotalInventory - COALESCE(LastPartialInventory.RunningTotal,0) + COALESCE(LastPartialInventory.InventoryToUse,0) AS UseThisInventory
FROM cteInventorySum AS w
OUTER APPLY (SELECT TOP ( 1 ) z.Fifo_Rank,
z.ThisInventory AS InventoryToUse,
z.RollingInventory AS RunningTotal
FROM cteReverseInSum AS z
WHERE z.Department = w.Department
AND z.Category = w.Category
AND z.Item = w.Item
AND z.Invent_ID = w.Invent_ID
AND z.Locatn_ID = w.Locatn_ID
AND z.RollingInventory >= w.TotalInventory
ORDER BY z.Fifo_Rank DESC) AS LastPartialInventory),
LastCost
AS (SELECT DISTINCT Cogs.department,
Cogs.category,
Cogs.item,
Cogs.invent_id,
LastCost.LastCost
FROM cteWithLastTranDate Cogs
CROSS APPLY dbo.siriusfn_LastCostUpToDate(Cogs.department,Cogs.category,Cogs.item,Cogs.invent_id, Cogs.LastDateTime) LastCost
WHERE Cogs.UseThisInventory IS NULL
OR Cogs.UseThisInventory = 0 OR Cogs.TotalInventory IS NULL OR Cogs.TotalInventory = 0),
cteSource
AS (
SELECT y.Department,
y.Category,
y.Item,
y.Invent_ID,
y.Locatn_ID,
y.TotalInventory as QuantityOnHand,
SUM(CASE WHEN e.Fifo_Rank = y.Fifo_Rank
THEN y.UseThisInventory
ELSE e.Quantity END * Price.Unit_Cost) AS CostOfGoodsOnHand,
LastCost.LastCost
FROM cteWithLastTranDate AS y
LEFT JOIN #Inventory AS e ON e.Department = y.Department
AND e.Category = y.Category
AND e.Item = y.Item
AND e.Invent_ID = y.Invent_ID
AND e.Locatn_ID = y.Locatn_ID
AND e.Fifo_Rank >= y.Fifo_Rank
AND e.Trans_Type IN ('P', 'A', 'T')
LEFT JOIN LastCost
ON y.Department = LastCost.Department
AND y.Category = LastCost.Category
AND y.Item = LastCost.Item
AND y.Invent_ID = LastCost.Invent_ID
OUTER APPLY (
/* Find the Price of the item in */
SELECT TOP (1) p.Unit_Cost
FROM #Inventory AS p
WHERE p.Department = e.Department and
p.Category = e.Category and
p.Item = e.Item and
p.Invent_ID = e.Invent_ID and
p.Locatn_ID = e.Locatn_ID and
p.Fifo_Rank <= e.Fifo_Rank and
p.Trans_Type IN ('P', 'A', 'T')
ORDER BY p.Fifo_Rank DESC
) AS Price
GROUP BY y.Department,
y.Category,
y.Item,
y.Invent_ID,
y.Locatn_ID,
y.TotalInventory,
LastCost.LastCost)
SELECT Department,
Category,
Item,
Invent_ID,
Locatn_ID,
CONVERT(INT,QuantityOnHand) as QuantityOnHand,
COALESCE(CostOfGoodsOnHand,0) AS CostOfGoodsOnHand,
COALESCE(CASE
WHEN QuantityOnHand <> 0
AND CostOfGoodsOnHand <> 0 THEN ABS(CostOfGoodsOnHand / QuantityOnHand)
ELSE LastCost
END, 0) AS AverageCost
FROM cteSource
WHERE @bIncludeZeroes = 1
OR (@bIncludeZeroes = 0
AND CostOfGoodsOnHand <> 0)
ORDER BY Department,
Category,
Item,
Invent_ID,
Locatn_ID;
END
GO
/* Test Cases
CREATE TABLE [dbo].[#Inventory](
[pri_key] [int] IDENTITY(1,1) NOT NULL,
[ref_no] [numeric](17, 0) NOT NULL,
[locatn_id] [int] NOT NULL,
[date_time] [datetime] NOT NULL,
[fifo_rank] [bigint] NULL,
[department] [char](10) 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,
[trans_type] [char](1) COLLATE DATABASE_DEFAULT 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:
- Create a new retail tracking item if necessary
- Receive 20 units of the item at $10.00 each
- Receive another 20 units of the item at $5.00 each
- Sell 30 units of the item
- Sell 10 units of the item the next day (or set carryover 1 day forward)
- Make sure the "Closing Cost Calculation Algorithm" under Retail Preferences in SysManager is set to FIFO. Run the Profit and Loss Report against each day. For the second day (sale of 10 units), COGS correctly shows $50.00. For the first day (sale of 30 units), COGS shows $150.00 (should be $250.00)
So, I decided I am going to try to re-write this procedure again rather than trying to figure out what was that procedure doing and where the bug may be. I also found the following thread in MSDN SQL FIFO Query which I already used in my prior attempts to solve the FIFO Cost of Goods Sold problem. This time I concentrated on the Peter Larsson's solution in that thread (SwePeso).
In the procedure, that is invoked before the FIFO Cost of Goods Sold procedure is called, I am selecting inventory items for the user's selections (say, for the Profit and Loss report the user can select particular department (or department and category), may select specific vendor and also selects a date range interval). So, I select rows into #Inventory temp table up to the end date of the selected dates interval. I again add FIFO_RANK column and also for simplicity I add InvNo numerical column using DENSE_RANK() function with order by Department, Category, Item, Invent_ID, Locatn_ID. This is done in order to use a single integer column to identify each inventory item rather than 5 columns. In my calculations I am also using dbo.Numbers table that has a single number column. In our database that table contains numbers from ~-100K to 100K.
The idea of the new design of this procedure is to calculate the starting point of the inventory in one step (up to Start Date - dtStart parameter) using Peter's idea and then process each individual sale or return (and negative quantity transfers) within the selected date intervals. The final result should have all sales and returns in the selected period (quantity and unit_cost).
So, I decided to introduce yet another temporary table I called #MovingInventory. In this table I have InvNo column (this is artificial Item Id for each inventory item in a location I created in the pre-step), fifo_rank column, quantity - the same quantity as in the #Inventory, CurrentQuantity (this column should reflect the current remaining quantity), Removed (quantity removed) and Returned (quantity returned). If we are to change our current Inventory process, we may create this table as a permanent table in the database and update it on each inventory movement. We can also create InventorySales table. Using these tables will significantly simplify the current calculation process.
Therefore, in the beginning of the procedure I now have this code:
IF OBJECT_ID('TempDB..#MovingInventory', N'U') IS NOT NULL
DROP TABLE #MovingInventory;
CREATE TABLE [dbo].[#MovingInventory] (
InvNo INT NOT NULL
,fifo_rank INT NOT NULL
,quantity INT
,unit_cost MONEY
,Removed INT
,Returned INT
,CurrentQuantity INT
,CONSTRAINT pkMovingInventory PRIMARY KEY (
InvNo
,fifo_rank
)
)
INSERT INTO #MovingInventory (
InvNo
,fifo_rank
,quantity
,unit_cost
,Removed
,Returned
,CurrentQuantity
)
SELECT InvNo
,fifo_rank
,quantity
,unit_cost
,0
,0
,quantity
FROM #Inventory
WHERE trans_type IN (
'P'
,'A'
,'T'
)
AND quantity > 0
ORDER BY InvNo
,fifo_rank;
So, we start with populating this new #MovingInventory temporary table with all positive additions to the inventory with their unit_cost. I set CurrentQuantity to quantity and Returned and Removed to 0.
I have two more temporary tables used in this procedure: #Sales - this table will be used to generate our final result and it will contain all sales and returns in the specified date range with the quantity sold (returned) and unit cost used.
I also have #Removed table. I could have used table variable here instead but I recall I had some problems with the table variable before in my prior version of that procedure so I decided to use temporary table again. This table will be used to hold items removed (or returned) on each iteration and it will be cleaned (truncated) on each iteration.
Here is the definition of these 2 temporary tables at the top of the procedure:
IF OBJECT_ID('TempDB..#Sales', N'U') IS NOT NULL
DROP TABLE #Sales;
CREATE TABLE [dbo].[#Sales] (
InvNo INT NOT NULL
,[trans_no] [numeric](17, 0) NOT NULL
,[locatn_id] [int] NOT NULL
,[date_time] [datetime] NOT NULL
,[department] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
,[category] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
,[item] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
,[invent_id] [int] NOT NULL
,quantity INT
,unit_cost MONEY
)
IF OBJECT_ID('TempDB..#Removed', N'U') IS NOT NULL
DROP TABLE #Removed;
CREATE TABLE [dbo].[#Removed] (
unit_cost MONEY
,Removed INT
)
Now, I decided to use two cursor loops in my procedure - one to iterate through each inventory item and another inner loop to go through each individual sale for that item. We all know well, that cursor based solutions are generally not recommended as they normally perform much worse than set based solutions. However, for solving this problem I simply don't see a set-based solution that's why I decided to use cursors. I may eventually re-design this procedure into CLR based procedure although I am not sure CLR based procedures may work with the temporary tables to start with.
So, my first step is to calculate prior inventory in one step. Here is the code I use for this:
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @fifo_rank = MAX(fifo_rank)
,@Removed = - 1 * SUM(quantity)
FROM #Inventory
WHERE date_time < @dtStart
AND (
trans_type = 'S'
OR quantity < 0
)
AND InvNo = @InvNo;
IF COALESCE(@Removed, 0) > 0 -- what to do when we start with returns - unlikely to happen, though?
BEGIN
IF @Debug = 1
PRINT 'Calculating starting inventory';;
WITH cteSource
AS (
SELECT TOP (@Removed) s.unit_Cost
,s.fifo_rank
,s.quantity
FROM #MovingInventory AS s
CROSS APPLY (
SELECT TOP (CAST(s.Quantity AS INT)) ROW_NUMBER() OVER (
ORDER BY number
) AS n
FROM dbo.numbers n5
WHERE number > 0
) AS f(n)
WHERE s.InvNo = @InvNo
AND s.fifo_rank < @fifo_rank
ORDER BY s.fifo_rank
)
,cteRemoved
AS (
SELECT unit_Cost
,fifo_rank
,quantity
,COUNT(*) AS Removed
FROM cteSource
GROUP BY unit_Cost
,fifo_rank
,quantity
)
UPDATE M
SET Removed = R.Removed
,CurrentQuantity = M.CurrentQuantity - R.Removed
FROM #MovingInventory M
INNER JOIN cteRemoved R ON M.fifo_rank = R.fifo_rank
WHERE M.InvNo = @InvNo;
-- We can also check if Removed = @Removed (if less, we have negative inventory - unlikely situation)
END
Here I am attempting to calculate our current working inventory in one step. I get the total sold quantity and last date (fifo_rank) when it was sold prior to dtStart and then distribute that sold quantity among all prior additions into inventory.
Here I am not considering situations when somehow we already sold more than we had in the inventory originally or when we returned more than sold (so total quantity will be greater than 0). To be honest, I am not 100% sure how to treat these situations, so I assume that possibility of them occurring is very low.
Once we got the inventory up to the starting date (dtStart) I am ready to process each individual sale or return. Here is how I do it for Sales and negative transfers:
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @quantity < 0 -- Sale or transfer
BEGIN
IF @Debug = 1
BEGIN
SET @Message = 'Sale or transfer with quantity = ' + CAST(- 1 * @quantity AS VARCHAR(20))
RAISERROR (
@Message
,10
,1
)
WITH NOWAIT;
END
SELECT @Removed = - 1 * @quantity;
WITH cteSource
AS (
SELECT TOP (@Removed) s.unit_Cost
,s.fifo_rank
,s.CurrentQuantity
FROM #MovingInventory AS s
CROSS APPLY (
SELECT TOP (s.CurrentQuantity) ROW_NUMBER() OVER (
ORDER BY number
) AS n
FROM dbo.numbers n5
WHERE number > 0
) AS f(n)
WHERE s.InvNo = @InvNo
AND s.fifo_rank < @fifo_rank
AND s.CurrentQuantity > 0
ORDER BY s.fifo_rank
)
,cteRemoved
AS (
SELECT unit_Cost
,fifo_rank
,CurrentQuantity
,COUNT(*) AS Removed
FROM cteSource
GROUP BY unit_Cost
,fifo_rank
,CurrentQuantity
)
UPDATE I
SET CurrentQuantity = I.CurrentQuantity - R.Removed
,Removed = I.Removed + R.Removed
OUTPUT Inserted.unit_cost
,Inserted.Removed - deleted.Removed
INTO #Removed(unit_cost, Removed)
FROM #MovingInventory I
INNER JOIN cteRemoved R ON I.fifo_rank = R.fifo_rank
WHERE I.InvNo = @InvNo;
IF @Debug = 1
BEGIN
SELECT *
FROM #MovingInventory I
WHERE I.InvNo = @InvNo;
RAISERROR (
'Current Moving Inventory after Sale or Return'
,10
,1
)
WITH NOWAIT
END
IF @trans_type = 'S'
AND @date_time >= @dtStart
INSERT INTO #Sales (
trans_no
,InvNo
,locatn_id
,date_time
,department
,category
,item
,invent_id
,unit_cost
,quantity
)
SELECT @ref_no
,@InvNo
,@locatn_id
,@date_time
,@department
,@category
,@item
,@invent_id
,unit_cost
,Removed
FROM #Removed;
--- Need to check for situations when we sell more than currently in the inventory (rare cases)
SELECT @Difference = @Removed - COALESCE((
SELECT SUM(Removed)
FROM #Removed
), 0);
IF @Difference > 0 -- Sold more than were in the inventory
BEGIN
IF @Debug = 1
BEGIN
SET @Message = 'Sold more than in the inventory - Difference = ' + CAST(@Difference AS VARCHAR(10))
RAISERROR (
@Message
,10
,1
)
WITH NOWAIT;
END
SET @LastCost = 0;
SELECT @LastCost = LastCost.LastCost
FROM dbo.siriusfn_LastCostUpToDate(@department, @category, @item, @invent_id, @date_time) LastCost;
IF @trans_type = 'S' -- only insert sales/returns
INSERT INTO #Sales (
trans_no
,InvNo
,locatn_id
,date_time
,department
,category
,item
,invent_id
,unit_cost
,quantity
)
SELECT @ref_no
,@InvNo
,@locatn_id
,@date_time
,@department
,@category
,@item
,@invent_id
,@LastCost
,@Difference
So, for each sale (or negative transfer) I use the same idea as in calculating starting inventory. I remove the sold quantity distributing it among rows where current quantity > 0 ordering by date_time (fifo_rank) column. I then update the #MovingInventory table (current quantity and Removed columns) and I output results using OUTPUT keyword for UPDATE into #Removed table. In addition, I populate #Sales table if the Trans_Type is 'S' (sales) to be used in the final select statement.
I also try to consider situations when we sold (or moved out) more than we have in the inventory. In this case we're using Last Cost for the item.
Here lies another problem not currently considered - if we have the negative quantity balance, we need to keep decrementing that difference after we receive that item. This is not currently done in my procedure - so we may get incorrect Cost of Goods Sold in such scenarios. I may need to think more how to handle this problem.
For the returns I am using a similar process to what I use for Sales, but I try to return back what I've already removed in the opposite direction (e.g. last removed - first returned). So, this is how I handle returns:
SELECT @Returned = @quantity;
WITH cteSource
AS (
SELECT TOP (@Returned) s.unit_Cost
,s.fifo_rank
,s.quantity
FROM #MovingInventory AS s
CROSS APPLY (
SELECT TOP (s.Removed - s.Returned) ROW_NUMBER() OVER (
ORDER BY number
) AS n
FROM dbo.numbers n5
WHERE number > 0
) AS f(n)
WHERE s.InvNo = @InvNo
AND s.fifo_rank < @fifo_rank
AND (s.Removed - s.Returned) > 0
ORDER BY s.fifo_rank DESC -- returns in the LIFO order
)
,cteReturned
AS (
SELECT unit_Cost
,fifo_rank
,quantity
,COUNT(*) AS Returned
FROM cteSource
GROUP BY unit_Cost
,fifo_rank
,quantity
)
UPDATE I
SET CurrentQuantity = I.CurrentQuantity + R.Returned
,Returned = I.Returned + R.Returned
OUTPUT Inserted.unit_cost
,Inserted.Returned - deleted.Returned
INTO #Removed(unit_cost, Removed)
FROM #MovingInventory I
INNER JOIN cteReturned R ON I.fifo_rank = R.fifo_rank
WHERE I.InvNo = @InvNo;
IF @Debug = 1
BEGIN
SELECT *
FROM #MovingInventory I
WHERE I.InvNo = @InvNo;
RAISERROR (
'Result after return'
,10
,1
)
WITH NOWAIT;
END
IF @trans_type = 'S'
AND @date_time >= @dtStart
INSERT INTO #Sales (
trans_no
,InvNo
,locatn_id
,date_time
,department
,category
,item
,invent_id
,unit_cost
,quantity
)
SELECT @ref_no
,@InvNo
,@locatn_id
,@date_time
,@department
,@category
,@item
,@invent_id
,unit_cost
,(- 1) * Removed
FROM #Removed;-- handle returns
-- Need to check for situations when we return what we didn't have in the inventory before
IF @Debug = 1
BEGIN
SELECT *
FROM #Sales;
RAISERROR (
'Current Sales after return'
,10
,1
)
WITH NOWAIT;
END
SELECT @Difference = @Returned - COALESCE((
SELECT SUM(Removed)
FROM #Removed
), 0);
IF @Difference > 0 -- Returned more than were in the inventory originally, use Last Cost
BEGIN
IF @Debug = 1
BEGIN
SET @Message = 'Returned more than removed - Difference= ' + CAST(@Difference AS VARCHAR(10)) + ' Last Cost = ' + CAST(@LastCost AS VARCHAR(20));
RAISERROR (
@Message
,10
,1
)
WITH NOWAIT;
END
SET @LastCost = 0;
SELECT @LastCost = LastCost.LastCost
FROM dbo.siriusfn_LastCostUpToDate(@department, @category, @item, @invent_id, @date_time) LastCost;
IF @trans_type = 'S' -- only insert sales/returns
INSERT INTO #Sales (
trans_no
,InvNo
,locatn_id
,date_time
,department
,category
,item
,invent_id
,unit_cost
,quantity
)
SELECT @ref_no
,@InvNo
,@locatn_id
,@date_time
,@department
,@category
,@item
,@invent_id
,@LastCost
,- 1 * @Difference;
END
END
Here again if we returned back more than we originally removed, I am returning using the last known cost for the item.
The Cost of Goods Sold FIFO procedure
Now I will give you the whole procedure code and hopefully you will see my logic. I also will appreciate comments or code corrections as this is still a work in progress and hasn't been tested extensively yet.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
SET NOCOUNT ON;
---------------- #Inventory test object creation so the script below doesn't complain about #Inventory table -----------
IF OBJECT_ID('tempdb..#Inventory', N'U') IS NOT NULL
DROP TABLE #Inventory;
CREATE TABLE [dbo].[#Inventory] (
[ref_no] [numeric](17, 0) NOT NULL
,[locatn_id] [int] NOT NULL
,[date_time] [datetime] NOT NULL
,[fifo_rank] [bigint] NULL
,[InvNo] [bigint] NULL
,[department] [char](10) 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
,[trans_type] [char](1) COLLATE DATABASE_DEFAULT NOT NULL
,[quantity] [numeric](8, 2) NOT NULL
,[unit_cost] [money] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[#Inventory] (
[ref_no]
,[locatn_id]
,[date_time]
,[fifo_rank]
,[InvNo]
,[department]
,[category]
,[item]
,[invent_id]
,[trans_type]
,[quantity]
,[unit_cost]
)
VALUES (
CAST(53 AS NUMERIC(17, 0))
,1
,CAST(0x0000A20000FF6D74 AS DATETIME)
,1
,1
,N'RETAIL '
,N'BK-CHILD '
,N'DSCATTEST '
,0
,N'P'
,CAST(40.00 AS NUMERIC(8, 2))
,10.0000
)
INSERT [dbo].[#Inventory] (
[ref_no]
,[locatn_id]
,[date_time]
,[fifo_rank]
,[InvNo]
,[department]
,[category]
,[item]
,[invent_id]
,[trans_type]
,[quantity]
,[unit_cost]
)
VALUES (
CAST(53 AS NUMERIC(17, 0))
,1
,CAST(0x0000A20000FF6D74 AS DATETIME)
,2
,1
,N'RETAIL '
,N'BK-CHILD '
,N'DSCATTEST '
,0
,N'P'
,CAST(40.00 AS NUMERIC(8, 2))
,5.0000
)
INSERT [dbo].[#Inventory] (
[ref_no]
,[locatn_id]
,[date_time]
,[fifo_rank]
,[InvNo]
,[department]
,[category]
,[item]
,[invent_id]
,[trans_type]
,[quantity]
,[unit_cost]
)
VALUES (
CAST(136005001 AS NUMERIC(17, 0))
,1
,CAST(0x0000A200011967D8 AS DATETIME)
,3
,1
,N'RETAIL '
,N'BK-CHILD '
,N'DSCATTEST '
,0
,N'S'
,CAST(- 50.00 AS NUMERIC(8, 2))
,0.0000
)
INSERT [dbo].[#Inventory] (
[ref_no]
,[locatn_id]
,[date_time]
,[fifo_rank]
,[InvNo]
,[department]
,[category]
,[item]
,[invent_id]
,[trans_type]
,[quantity]
,[unit_cost]
)
VALUES (
CAST(54 AS NUMERIC(17, 0))
,1
,CAST(0x0000A200011967DA AS DATETIME)
,4
,1
,N'RETAIL '
,N'BK-CHILD '
,N'DSCATTEST '
,0
,N'P'
,CAST(40.00 AS NUMERIC(8, 2))
,7.5000
)
INSERT [dbo].[#Inventory] (
[ref_no]
,[locatn_id]
,[date_time]
,[fifo_rank]
,[InvNo]
,[department]
,[category]
,[item]
,[invent_id]
,[trans_type]
,[quantity]
,[unit_cost]
)
VALUES (
CAST(136005002 AS NUMERIC(17, 0))
,1
,CAST(0x0000A200011967DE AS DATETIME)
,5
,1
,N'RETAIL '
,N'BK-CHILD '
,N'DSCATTEST '
,0
,N'S'
,CAST(- 50.00 AS NUMERIC(8, 2))
,0.0000
)
GO
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'siriussp_CostOfGoodsSold_FIFO'
AND ROUTINE_TYPE = 'PROCEDURE'
)
EXECUTE ('CREATE PROCEDURE dbo.siriussp_CostOfGoodsSold_FIFO AS SET NOCOUNT ON;');
GO
ALTER PROCEDURE dbo.siriussp_CostOfGoodsSold_FIFO (
@dtStart DATETIME
,@Debug BIT = 0
)
--=============================================================
/* SP that returns total quantity and cost of goods sold
by department, category, item, invent_id, and locatn_id,
using FIFO (First IN, First OUT) method of cost valuation.
Modified on 07/10/2012
Modified on 07/19/2013 - 7/26/2013
--=============================================================
*/
AS
BEGIN
SET NOCOUNT ON;
--IF CAST(LEFT(CAST(serverproperty('ProductVersion') AS VARCHAR(max)), 2) AS DECIMAL(10, 2)) >= 11
-- AND OBJECT_ID('dbo.siriussp_CostOfGoodsSold_FIFO_2012', 'P') IS NOT NULL
--BEGIN
-- PRINT 'Using 2012 version of the stored procedure'
-- EXECUTE sp_ExecuteSQL N'EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO_2012 @dtStart, @Debug'
-- ,N'@dtStart DATETIME, @Debug BIT'
-- ,@dtStart, @Debug ;
-- RETURN;
--END
--PRINT 'Using cursor based version of the stored procedure'
IF OBJECT_ID('TempDB..#Sales', N'U') IS NOT NULL
DROP TABLE #Sales;
CREATE TABLE [dbo].[#Sales] (
InvNo INT NOT NULL
,[trans_no] [numeric](17, 0) NOT NULL
,[locatn_id] [int] NOT NULL
,[date_time] [datetime] NOT NULL
,[department] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
,[category] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
,[item] [char](10) COLLATE DATABASE_DEFAULT NOT NULL
,[invent_id] [int] NOT NULL
,quantity INT
,unit_cost MONEY
)
IF OBJECT_ID('TempDB..#Removed', N'U') IS NOT NULL
DROP TABLE #Removed;
CREATE TABLE [dbo].[#Removed] (
unit_cost MONEY
,Removed INT
)
IF OBJECT_ID('TempDB..#MovingInventory', N'U') IS NOT NULL
DROP TABLE #MovingInventory;
CREATE TABLE [dbo].[#MovingInventory] (
InvNo INT NOT NULL
,fifo_rank INT NOT NULL
,quantity INT
,unit_cost MONEY
,Removed INT
,Returned INT
,CurrentQuantity INT
,CONSTRAINT pkMovingInventory PRIMARY KEY (
InvNo
,fifo_rank
)
)
INSERT INTO #MovingInventory (
InvNo
,fifo_rank
,quantity
,unit_cost
,Removed
,Returned
,CurrentQuantity
)
SELECT InvNo
,fifo_rank
,quantity
,unit_cost
,0
,0
,quantity
FROM #Inventory
WHERE trans_type IN (
'P'
,'A'
,'T'
)
AND quantity > 0
ORDER BY InvNo
,fifo_rank;
IF NOT EXISTS (
SELECT NAME
FROM TempDB.sys.sysindexes
WHERE NAME = 'idx_Inventory_fifo_rank'
)
CREATE INDEX idx_Inventory_fifo_rank ON #Inventory (
InvNo
,fifo_rank
);
DECLARE @InvNo INT
,@ref_no NUMERIC(17, 0)
,@locatn_id INT
,@date_time DATETIME
,@fifo_rank INT
,@department CHAR(10)
,@category CHAR(10)
,@item CHAR(10)
,@invent_id INT
,@trans_type CHAR(1)
,@quantity INT
,@unit_cost MONEY
,@LastCost MONEY
,@CurInvNo INT
,@Removed INT
,@Returned INT
,@Elapsed INT
,@StartTime DATETIME
,@Message VARCHAR(MAX)
,@Difference INT;
SET @StartTime = CURRENT_TIMESTAMP;
DECLARE curMainProcess CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT DISTINCT InvNo
FROM #Inventory
ORDER BY InvNo;
OPEN curMainProcess;
FETCH NEXT
FROM curMainProcess
INTO @InvNo;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @fifo_rank = MAX(fifo_rank)
,@Removed = - 1 * SUM(quantity)
FROM #Inventory
WHERE date_time < @dtStart
AND (
trans_type = 'S'
OR quantity < 0
)
AND InvNo = @InvNo;
IF COALESCE(@Removed, 0) > 0 -- what to do when we start with returns - unlikely to happen, though?
BEGIN
IF @Debug = 1
PRINT 'Calculating starting inventory';
WITH cteSource
AS (
SELECT TOP (@Removed) s.unit_Cost
,s.fifo_rank
,s.quantity
FROM #MovingInventory AS s
CROSS APPLY (
SELECT TOP (CAST(s.Quantity AS INT)) ROW_NUMBER() OVER (
ORDER BY number
) AS n
FROM dbo.numbers n5
WHERE number > 0
) AS f(n)
WHERE s.InvNo = @InvNo
AND s.fifo_rank < @fifo_rank
ORDER BY s.fifo_rank
)
,cteRemoved
AS (
SELECT unit_Cost
,fifo_rank
,quantity
,COUNT(*) AS Removed
FROM cteSource
GROUP BY unit_Cost
,fifo_rank
,quantity
)
UPDATE M
SET Removed = R.Removed
,CurrentQuantity = M.CurrentQuantity - R.Removed
FROM #MovingInventory M
INNER JOIN cteRemoved R ON M.fifo_rank = R.fifo_rank
WHERE M.InvNo = @InvNo;
-- We can also check if Removed = @Removed (if less, we have negative inventory - unlikely situation)
END
IF @Debug = 1
BEGIN
SELECT *
FROM #MovingInventory
WHERE InvNo = @InvNo;
RAISERROR (
'Done with the prior inventory - starting checking Sales we''re interested in'
,10
,1
)
WITH NOWAIT;
END
DECLARE curProcess CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT InvNo
,ref_no
,date_time
,fifo_rank
,quantity
,unit_cost
,trans_type
,department
,category
,item
,invent_id
,locatn_id
FROM #Inventory
WHERE InvNo = @InvNo
AND (
trans_type = 'S'
OR quantity < 0
)
AND date_time >= @dtStart -- now process only the Sales we're interested in
ORDER BY InvNo
,fifo_rank
OPEN curProcess
FETCH NEXT
FROM curProcess
INTO @InvNo
,@ref_no
,@date_time
,@fifo_rank
,@quantity
,@unit_cost
,@trans_type
,@department
,@category
,@item
,@invent_id
,@locatn_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @quantity < 0 -- Sale or transfer
BEGIN
IF @Debug = 1
BEGIN
SET @Message = 'Sale or transfer with quantity = ' + CAST(- 1 * @quantity AS VARCHAR(20))
RAISERROR (
@Message
,10
,1
)
WITH NOWAIT;
END
SELECT @Removed = - 1 * @quantity;
WITH cteSource
AS (
SELECT TOP (@Removed) s.unit_Cost
,s.fifo_rank
,s.CurrentQuantity
FROM #MovingInventory AS s
CROSS APPLY (
SELECT TOP (s.CurrentQuantity) ROW_NUMBER() OVER (
ORDER BY number
) AS n
FROM dbo.numbers n5
WHERE number > 0
) AS f(n)
WHERE s.InvNo = @InvNo
AND s.fifo_rank < @fifo_rank
AND s.CurrentQuantity > 0
ORDER BY s.fifo_rank
)
,cteRemoved
AS (
SELECT unit_Cost
,fifo_rank
,CurrentQuantity
,COUNT(*) AS Removed
FROM cteSource
GROUP BY unit_Cost
,fifo_rank
,CurrentQuantity
)
UPDATE I
SET CurrentQuantity = I.CurrentQuantity - R.Removed
,Removed = I.Removed + R.Removed
OUTPUT Inserted.unit_cost
,Inserted.Removed - deleted.Removed
INTO #Removed(unit_cost, Removed)
FROM #MovingInventory I
INNER JOIN cteRemoved R ON I.fifo_rank = R.fifo_rank
WHERE I.InvNo = @InvNo;
IF @Debug = 1
BEGIN
SELECT *
FROM #MovingInventory I
WHERE I.InvNo = @InvNo;
RAISERROR (
'Current Moving Inventory after Sale or Return'
,10
,1
)
WITH NOWAIT
END
IF @trans_type = 'S'
AND @date_time >= @dtStart
INSERT INTO #Sales (
trans_no
,InvNo
,locatn_id
,date_time
,department
,category
,item
,invent_id
,unit_cost
,quantity
)
SELECT @ref_no
,@InvNo
,@locatn_id
,@date_time
,@department
,@category
,@item
,@invent_id
,unit_cost
,Removed
FROM #Removed;
--- Need to check for situations when we sell more than currently in the inventory (rare cases)
SELECT @Difference = @Removed - COALESCE((
SELECT SUM(Removed)
FROM #Removed
), 0);
IF @Difference > 0 -- Sold more than were in the inventory
BEGIN
IF @Debug = 1
BEGIN
SET @Message = 'Sold more than in the inventory - Difference = ' + CAST(@Difference AS VARCHAR(10))
RAISERROR (
@Message
,10
,1
)
WITH NOWAIT;
END
SET @LastCost = 0;
SELECT @LastCost = LastCost.LastCost
FROM dbo.siriusfn_LastCostUpToDate(@department, @category, @item, @invent_id, @date_time) LastCost;
IF @trans_type = 'S' -- only insert sales / returns
INSERT INTO #Sales (
trans_no
,InvNo
,locatn_id
,date_time
,department
,category
,item
,invent_id
,unit_cost
,quantity
)
SELECT @ref_no
,@InvNo
,@locatn_id
,@date_time
,@department
,@category
,@item
,@invent_id
,@LastCost
,@Difference
IF @Debug = 1
BEGIN
SET @Message = 'Last Cost = ' + CAST(@LastCost AS VARCHAR(10))
RAISERROR (
@Message
,10
,1
)
WITH NOWAIT;
SELECT *
FROM #Sales
RAISERROR (
'Currently in #Sales'
,10
,1
)
WITH NOWAIT;
END
END
END
ELSE -- Returns
BEGIN
IF @Debug = 1
BEGIN
SET @Message = 'Return with quantity = ' + CAST(@quantity AS VARCHAR(20));
RAISERROR (
@Message
,10
,1
)
WITH NOWAIT;
END
SELECT @Returned = @quantity;
WITH cteSource
AS (
SELECT TOP (@Returned) s.unit_Cost
,s.fifo_rank
,s.quantity
FROM #MovingInventory AS s
CROSS APPLY (
SELECT TOP (s.Removed - s.Returned) ROW_NUMBER() OVER (
ORDER BY number
) AS n
FROM dbo.numbers n5
WHERE number > 0
) AS f(n)
WHERE s.InvNo = @InvNo
AND s.fifo_rank < @fifo_rank
AND (s.Removed - s.Returned) > 0
ORDER BY s.fifo_rank DESC -- returns in the LIFO order
)
,cteReturned
AS (
SELECT unit_Cost
,fifo_rank
,quantity
,COUNT(*) AS Returned
FROM cteSource
GROUP BY unit_Cost
,fifo_rank
,quantity
)
UPDATE I
SET CurrentQuantity = I.CurrentQuantity + R.Returned
,Returned = I.Returned + R.Returned
OUTPUT Inserted.unit_cost
,Inserted.Returned - deleted.Returned
INTO #Removed(unit_cost, Removed)
FROM #MovingInventory I
INNER JOIN cteReturned R ON I.fifo_rank = R.fifo_rank
WHERE I.InvNo = @InvNo;
IF @Debug = 1
BEGIN
SELECT *
FROM #MovingInventory I
WHERE I.InvNo = @InvNo;
RAISERROR (
'Result after return'
,10
,1
)
WITH NOWAIT;
END
IF @trans_type = 'S'
AND @date_time >= @dtStart
INSERT INTO #Sales (
trans_no
,InvNo
,locatn_id
,date_time
,department
,category
,item
,invent_id
,unit_cost
,quantity
)
SELECT @ref_no
,@InvNo
,@locatn_id
,@date_time
,@department
,@category
,@item
,@invent_id
,unit_cost
,(- 1) * Removed
FROM #Removed;-- handle returns
-- Need to check for situations when we return what we didn't have in the inventory before
IF @Debug = 1
BEGIN
SELECT *
FROM #Sales;
RAISERROR (
'Current Sales after return'
,10
,1
)
WITH NOWAIT;
END
SELECT @Difference = @Returned - COALESCE((
SELECT SUM(Removed)
FROM #Removed
), 0);
IF @Difference > 0 -- Returned more than were in the inventory originally, use Last Cost
BEGIN
IF @Debug = 1
BEGIN
SET @Message = 'Returned more than removed - Difference= ' + CAST(@Difference AS VARCHAR(10)) + ' Last Cost = ' + CAST(@LastCost AS VARCHAR(20));
RAISERROR (
@Message
,10
,1
)
WITH NOWAIT;
END
SET @LastCost = 0;
SELECT @LastCost = LastCost.LastCost
FROM dbo.siriusfn_LastCostUpToDate(@department, @category, @item, @invent_id, @date_time) LastCost;
IF @trans_type = 'S' -- only insert sales/returns
INSERT INTO #Sales (
trans_no
,InvNo
,locatn_id
,date_time
,department
,category
,item
,invent_id
,unit_cost
,quantity
)
SELECT @ref_no
,@InvNo
,@locatn_id
,@date_time
,@department
,@category
,@item
,@invent_id
,@LastCost
,- 1 * @Difference;
END
END
TRUNCATE TABLE #Removed;-- done with this table for this iteration
FETCH NEXT
FROM curProcess
INTO @InvNo
,@ref_no
,@date_time
,@fifo_rank
,@quantity
,@unit_cost
,@trans_type
,@department
,@category
,@item
,@invent_id
,@locatn_id
END -- while
CLOSE curProcess
DEALLOCATE curProcess
FETCH NEXT
FROM curMainProcess
INTO @InvNo
END -- while
CLOSE curMainProcess
DEALLOCATE curMainProcess
IF @Debug = 1
BEGIN
SET @Elapsed = datediff(second, @StartTime, CURRENT_TIMESTAMP);
PRINT ' Finished with the creation of #Sales tables using cursor in ' + cast(@Elapsed AS VARCHAR(30)) + ' seconds';
END
SELECT S.trans_no
,S.department
,S.category
,S.item
,S.invent_id
,S.locatn_id
,SUM(S.quantity) AS QuantitySold
,CAST(SUM(S.quantity * S.unit_cost) AS MONEY) AS CostOfGoodsSold
FROM #Sales S
GROUP BY S.trans_no
,S.department
,S.category
,S.item
,S.invent_id
,S.locatn_id;
IF @Debug = 1
BEGIN
SET @Elapsed = datediff(second, @StartTime, CURRENT_TIMESTAMP);
PRINT ' Finished with the final selection in ' + cast(@Elapsed AS VARCHAR(30)) + ' seconds';
END
END
RETURN;
GO
/* Test Cases
IF OBJECT_ID('TempDB..#Inventory',N'U') IS NOT NULL DROP TABLE #Inventory;
CREATE TABLE [dbo].[#Inventory](
[InvNo] [int] NOT NULL,
[ref_no] [numeric](17, 0) NOT NULL,
[locatn_id] [int] NOT NULL,
[date_time] [datetime] NOT NULL,
[fifo_rank] [bigint] NULL,
[department] [char](10) 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,
[trans_type] [char](1) COLLATE DATABASE_DEFAULT NOT NULL,
[quantity] [numeric](8, 2) NOT NULL,
[unit_cost] [money] NOT NULL
)
;with cte as (SELECT N'25' AS [ref_no], N'1' AS [locatn_id], N'2012-06-29 16:48:39.000' AS [date_time], N'1' AS [fifo_rank], N'1' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BATT_TEST' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'100.00' AS [quantity], N'1.00' AS [unit_cost] UNION ALL
SELECT N'133005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-29 17:00:13.000' AS [date_time], N'2' AS [fifo_rank], N'1' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BATT_TEST' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-90.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'25' AS [ref_no], N'1' AS [locatn_id], N'2012-06-29 17:26:47.000' AS [date_time], N'3' AS [fifo_rank], N'1' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BATT_TEST' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'100.00' AS [quantity], N'2.00' AS [unit_cost] UNION ALL
SELECT N'135005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-29 17:28:19.000' AS [date_time], N'4' AS [fifo_rank], N'1' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BATT_TEST' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'10.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 11:58:26.000' AS [date_time], N'1' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'10.00' AS [quantity], N'2.00' AS [unit_cost] UNION ALL
SELECT N'129005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 13:02:57.000' AS [date_time], N'2' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-9.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 13:06:07.000' AS [date_time], N'3' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'10.00' AS [quantity], N'2.6667' AS [unit_cost] UNION ALL
SELECT N'130005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 13:17:46.000' AS [date_time], N'4' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-7.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'131005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-27 13:18:16.000' AS [date_time], N'5' AS [fifo_rank], N'2' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'3.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'24' AS [ref_no], N'3' AS [locatn_id], N'2012-06-27 11:57:17.000' AS [date_time], N'1' AS [fifo_rank], N'3' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'20.00' AS [quantity], N'2.00' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'3' AS [locatn_id], N'2012-06-27 11:58:26.000' AS [date_time], N'2' AS [fifo_rank], N'3' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-10.00' AS [quantity], N'2.00' AS [unit_cost] UNION ALL
SELECT N'24' AS [ref_no], N'3' AS [locatn_id], N'2012-06-27 13:04:29.000' AS [date_time], N'3' AS [fifo_rank], N'3' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'20.00' AS [quantity], N'3.00' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'3' AS [locatn_id], N'2012-06-27 13:06:07.000' AS [date_time], N'4' AS [fifo_rank], N'3' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'BUG_SPRAY' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-10.00' AS [quantity], N'2.6667' AS [unit_cost] UNION ALL
SELECT N'4' AS [ref_no], N'1' AS [locatn_id], N'2011-04-03 18:34:44.000' AS [date_time], N'1' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'24.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
SELECT N'11005001' AS [ref_no], N'1' AS [locatn_id], N'2011-04-07 09:57:51.000' AS [date_time], N'2' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'33005001' AS [ref_no], N'1' AS [locatn_id], N'2011-04-07 10:04:39.000' AS [date_time], N'3' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'103005001' AS [ref_no], N'1' AS [locatn_id], N'2011-07-06 17:55:17.000' AS [date_time], N'4' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'108005001' AS [ref_no], N'1' AS [locatn_id], N'2011-07-06 17:55:47.000' AS [date_time], N'5' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'115005001' AS [ref_no], N'1' AS [locatn_id], N'2011-08-01 17:47:11.000' AS [date_time], N'6' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'41005001' AS [ref_no], N'1' AS [locatn_id], N'2011-09-04 11:24:03.000' AS [date_time], N'7' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-2.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'48005001' AS [ref_no], N'1' AS [locatn_id], N'2011-09-04 11:38:31.000' AS [date_time], N'8' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-3.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'65005001' AS [ref_no], N'1' AS [locatn_id], N'2011-09-04 11:59:59.000' AS [date_time], N'9' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-1.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'1' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 17:02:19.000' AS [date_time], N'10' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'A' AS [trans_type], N'5.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 17:09:46.000' AS [date_time], N'11' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'A' AS [trans_type], N'5.00' AS [quantity], N'0.10' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 17:15:05.000' AS [date_time], N'12' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'5.00' AS [quantity], N'0.5469' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 17:15:47.000' AS [date_time], N'13' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'5.00' AS [quantity], N'0.5469' AS [unit_cost] UNION ALL
SELECT N'125005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 18:00:26.000' AS [date_time], N'14' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-10.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'126005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 18:01:05.000' AS [date_time], N'15' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'5.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'127005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 18:02:07.000' AS [date_time], N'16' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'-50.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'128005001' AS [ref_no], N'1' AS [locatn_id], N'2012-06-26 18:02:51.000' AS [date_time], N'17' AS [fifo_rank], N'4' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'S' AS [trans_type], N'30.00' AS [quantity], N'0.00' AS [unit_cost] UNION ALL
SELECT N'5' AS [ref_no], N'3' AS [locatn_id], N'2011-04-03 16:41:21.000' AS [date_time], N'1' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'60.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
SELECT N'1' AS [ref_no], N'3' AS [locatn_id], N'2011-04-03 17:46:45.000' AS [date_time], N'2' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'A' AS [trans_type], N'-2.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
SELECT N'4' AS [ref_no], N'3' AS [locatn_id], N'2011-04-03 18:34:44.000' AS [date_time], N'3' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-24.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
SELECT N'23' AS [ref_no], N'3' AS [locatn_id], N'2012-06-26 17:00:58.000' AS [date_time], N'4' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'10.00' AS [quantity], N'0.75' AS [unit_cost] UNION ALL
SELECT N'23' AS [ref_no], N'3' AS [locatn_id], N'2012-06-26 17:04:59.000' AS [date_time], N'5' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'P' AS [trans_type], N'20.00' AS [quantity], N'0.10' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'3' AS [locatn_id], N'2012-06-26 17:15:05.000' AS [date_time], N'6' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-5.00' AS [quantity], N'0.5469' AS [unit_cost] UNION ALL
SELECT N'0' AS [ref_no], N'3' AS [locatn_id], N'2012-06-26 17:15:47.000' AS [date_time], N'7' AS [fifo_rank], N'5' AS [InvNo], N'RETAIL' AS [department], N'SUPPLIES' AS [category], N'GRANOLABAR' AS [item], N'0' AS [invent_id], N'T' AS [trans_type], N'-5.00' AS [quantity], N'0.5469' AS [unit_cost] )
insert #Inventory ([ref_no], [locatn_id], [date_time], [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost])
SELECT [ref_no], [locatn_id], [date_time], [fifo_rank], [InvNo], [department], [category], [item], [invent_id], [trans_type], [quantity], [unit_cost]
from cte
--CREATE INDEX idx_Inventory_fifo_rank ON #Inventory (InvNo, fifo_rank)
SELECT * FROM #Inventory
DECLARE @Time datetime, @Elapsed int, @dtStart datetime
set @dtStart = '20120629'
SET @time = GETDATE()
EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO_TEST @dtStart = '20010629'
set @Elapsed = DATEDIFF(second,@time, getdate())
print 'Elapsed for SQL 2005-2008: - cursor version ' + convert(varchar(10),@Elapsed) + ' seconds'
SET @time = GETDATE()
SET @time = GETDATE()
EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO @dtStart= '20010629'
set @Elapsed = DATEDIFF(second,@time, getdate())
print 'Elapsed for SQL 2005-2008: - Prior cursor version ' + convert(varchar(10),@Elapsed) + ' seconds'
--EXECUTE dbo.siriussp_CostOfGoodsSold_FIFO_2012 '20010629'
--SET @time = GETDATE()
--set @Elapsed = DATEDIFF(second,@time, getdate())
--print 'Elapsed for SQL 2012: ' + convert(varchar(10),@Elapsed) + ' seconds'
go
*/
At the top of the script code I provided #Inventory table for the original failing scenario in order to confirm that it works correctly with the new code. I also have a scenario I tested originally in the comments after the stored procedure.
Summary
In this article I described the process of working on a complex problem of Calculating Cost of Goods Sold using FIFO method and gave my current procedure code. I also showed potential problems and flaws in that code. I will appreciate comments and ideas of improving this algorithm.
See Also
- Transact-SQL Portal
- [[T-SQL Useful Links]]
- Set-based Speed Phreakery: The FIFO
This article participated in the TechNet Guru Competition for July 2013 and won the Silver Prize.