Use the SQL Server table-valued function FIFOdet to calculate FIFO (First In, First Out) values in an ordered resultant table. FIFOdet calculates balances for each value from the first value to the last value in the ordered group or partition. FIFOdet returns:

FIFOdet assumes that the quantity i.e., the number of units, of the transaction and the monetary value of the transaction have the same sign. Purchase, or additions to inventory, are positive and sales, or withdrawals from inventory, are negative.

FIFOdet will match the first sale transaction with the first purchase transaction. If the sale quantity is less than the purchased quantity, then the remaining balance on the purchase transaction will be applied to the next sale transaction. If the sale quantity is greater than the purchased quantity then FIFOdet will get the next purchase transaction, repeating this process until the remaining balance on the sales transaction is zero or there is no more inventory.

In the event that amount sold is greater than the amount purchased, then the inventory on hand will continue to be updated, but the inventory cost will be zero.

This is a very simple example showing the movements for one product over the month of June. Since it only involves one product, there is no product identifier in the SQL. The input had been put in date order and then in quantity descending order within date.

This produces the following result.

Note that there were 30 input rows and 44 output rows. You should also make note of the fact that the first purchase transaction occurs after the first sale transaction.

In this example, we calculate the FIFO balances for multiple products. Doing so requires the use of a CROSS APPLY. In addition to the CROSS APPLY we JOIN to the source table to include the product identifier (and any other data that might be of interest) in the resultant table.

This produces the following result.

In this example, there is a separate table (#i) for all the inventory items and a table for the transactions (#t). This SQL will calculate the FIFO values without having to get the distinct product identifiers from the transaction table.

This produces the following result.

In this example we show how to get the ending inventories balances. We put some transaction date in #t and then use FIFOdet to put the inventory calculations in #fifo. We then execute SQL to get the ending inventory on hand and the cost of that inventory for each product in #fifo.

This produces the following result.

Note that 2 of the products have negative inventory balances, meaning that the sales exceeded the purchases. Since the inventory balance is less than zero the inventory cost is zero. In the next two examples we will show how to get the details for inventory balances greater than zero and less than zero.

In this example, using the same tables that were created in Example #4, we show how to get the details of the outstanding inventory balances i.e., positive balances. For this example, those details are the product identifier, the transaction identifier, the transaction date, the original transaction quantity, the remaining transaction quantity, the original transaction amount, and the remaining transaction amount. This information might be used in the next inventory calculation after all the settled transactions have been archived. Additionally, we have included running totals of the inventory quantity and inventory cost which show that the amounts agree with the amounts in Example #4.

This produces the following result.

In this example, using the same tables that were created in Example #4, we show how to get the details of the outstanding unfilled order balances i.e., negative balances. For this example, those details are the product identifier, the transaction identifier, the transaction date, the original transaction quantity, the remaining transaction quantity, the original transaction amount, and the remaining transaction amount. This information might be used in the next inventory calculation after all the settled transactions have been archived. Additionally, we have included running totals of the sale quantity and sale amount which show that the amounts agree with the amounts in Example #4.

This produces the following result.