SQL Server FIFO function

FIFOtvf

Updated: 03 Mar 2017

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

quantity on-hand

inventory cost

cost of goods sold

gross margin on sale

gross margin percentage

average inventory price

last inventory price

cumulative cost of goods sold

cumulative gross margin on sale

cumulative gross margin percentage.

FIFOtvf supports both long and short inventory positions. In other words, if the quantity on hand falls below the zero, FIFOtvf calculates from the last sale or withdrawal transaction, rather than from the last purchases or additions to inventory.

FIFOtvf assumes that the quantity (i.e. the number of units) of the transaction and the monetary value of the transaction have the same sign. FIFOtvf adds NULL values to the inventory at the last price.

In the following examples, we calculate FIFO inventory values for stock trades in the ABC, XYZ, and GHI companies. We will create a temporary table, #c and populate it with some data. We can be either short or long the shares at any point in time.

wct.FIFOtvf('SELECT trn, ROW_NUMBER() OVER (PARTITION by sym ORDER BY sym, tDate, trn),qty,price_extended FROM #c ORDER BY sym, tDate, trn')

--JOIN to the source data to produce the inventory output

SELECT

c.trn

,c.sym

,c.tDate

,c.qty

,c.price_unit

,c.price_extended

,f.[QTY] as [Inventory-On-Hand]

,f.[EB] as [Inventory Cost]

,f.[GM] as [Gross Margin on Sales]

,f.[COGS] as [Cost-of-Goods Sold]

,f.[UP] as [Average Price]

,f.[LP] as [Last Price]

,f.[COGSC] as [Cumulative COGS]

,f.[GMC] as [Cumulative Gross Margin]

,f.[GMP] as [GM Percentage]

,f.[CGMP] as [Cumulative GM Percentage]

FROM

#c c

INNER JOIN

#FIFO f

ON

c.trn = f.[ID]

ORDER BY

c.sym, c.tDate, c.trn

This produces the following result.

Example #2

In this example, we will look at a simple FX blotter. In FX trading, it is not at all unusual to switch from a long currency position to a short currency position during the course of a day. Additionally, if you are trading something like the US dollars against the Euro, if you are long US dollars, you are short the Euro. Thus, it’s critical to pay attention to the sign. We will create the #fx table and populate it with some data.

--Create the temporary table

CREATE TABLE #fx (

trn int,

ccy char(3),

amt_ccy money,

rate float,

ctr char(3),

amt_ctr money,

PRIMARY KEY (trn)

)

--Populate the table with some data

INSERT INTO #fx VALUES (101,'GBP',8000000,1.619,'USD',-12952000)

INSERT INTO #fx VALUES (102,'GBP',-10000000,1.62,'USD',16200000)

INSERT INTO #fx VALUES (103,'GBP',-4000000,1.613,'USD',6452000)

INSERT INTO #fx VALUES (104,'GBP',7000000,1.618,'USD',-11326000)

INSERT INTO #fx VALUES (105,'GBP',6000000,1.623,'USD',-9738000)

INSERT INTO #fx VALUES (106,'GBP',-5000000,1.618,'USD',8090000)

INSERT INTO #fx VALUES (107,'GBP',-10000000,1.602,'USD',16020000)

INSERT INTO #fx VALUES (108,'GBP',2000000,1.608,'USD',-3216000)

INSERT INTO #fx VALUES (109,'GBP',-2000000,1.602,'USD',3204000)

INSERT INTO #fx VALUES (110,'GBP',10000000,1.626,'USD',-16260000)

--Calculate the FIFO Values and store in temp table

SELECT

CAST([ID] asInt) as ID

,[QTY]

,[EB]

,[GM]

,[COGS]

,[UP]

,[LP]

,[COGSC]

,[GMC]

,[GMP]

,[CGMP]

INTO

#fifo

FROM

wct.FIFOtvf('SELECT trn, ROW_NUMBER() OVER (ORDER BY trn),amt_ccy,-amt_ctr FROM #fx ORDER BY trn')

--JOIN to the source data to produce the inventory output

SELECT

c.trn

,c.ccy

,c.amt_ccy

,c.rate

,c.ctr

,c.amt_ctr

,f.[QTY] as [GBP Position]

,f.[EB] as [USD Position]

,f.[GM] as [P/L]

,f.[COGS] as [Cost-of-Goods Sold]

,f.[UP] as [Average Price]

,f.[LP] as [Last Price]

,f.[COGSC] as [Cumulative COGS]

,f.[GMC] as [Cumulative P/L]

,f.[GMP] as [GM Percentage]

,f.[CGMP] as [Cumulative GM Percentage]

FROM

#fx c

INNER JOIN

#FIFO f

ON

c.trn = f.[ID]

ORDER BY

c.trn

This produces the following result.

Example #3

Using the #c temp table created in Example #1, we will insert the necessary input for the FIFOtvf into the #inv temp table containing an IDENTITY column which is then used to link the input data to the output data. This simplifies the @DataQuery SQL.

--put the #c data into #inv

SELECT

IDENTITY(int,1,1) as id

,trn

,ROW_NUMBER() OVER (PARTITION by sym ORDER BY sym, tDate, trn) as rn

,qty

,price_extended

INTO

#inv

FROM

#c

ORDER BY

sym, tDate, trn

--Calculate the FIFO Values and store in temp table

SELECT

CAST([ID] asInt) as ID

,[QTY]

,[EB]

,[GM]

,[COGS]

,[UP]

,[LP]

,[COGSC]

,[GMC]

,[GMP]

,[CGMP]

INTO

#fifo

FROM

wct.FIFOtvf('SELECT id,rn,qty,price_extended FROM #inv ORDER BY id')

--JOIN to the source data to produce the inventory output

SELECT

i.trn

,c.sym

,c.tDate

,i.qty

,c.price_unit

,i.price_extended

,f.[QTY] as [Inventory-On-Hand]

,f.[EB] as [Inventory Cost]

,f.[GM] as [Gross Margin on Sales]

,f.[COGS] as [Cost-of-Goods Sold]

,f.[UP] as [Average Price]

,f.[LP] as [Last Price]

,f.[COGSC] as [Cumulative COGS]

,f.[GMC] as [Cumulative Gross Margin]

,f.[GMP] as [GM Percentage]

,f.[CGMP] as [Cumulative GM Percentage]

FROM

#inv i

INNER JOIN

#FIFO f

ON

i.ID = f.[ID]

INNER JOIN

#c c

ON

i.trn = c.trn

ORDER BY

i.id

This produces the following result.

Example #4

In this example we require multiple columns to uniquely identify the inventory items and there are no unique transaction identifiers.