SQL Server LIFO function

LIFO

Updated: 31 Dec 2013

Use LIFO to calculate running LIFO (last in, first out) values in an ordered resultant table, without the need for a self-join. LIFO calculates balances for each value from the first value to the last value in the ordered group or partition. LIFO can return the quantity on-hand, the inventory value, the gross margin on sale, the gross margin percentage, the cost of goods sold, the average inventory price, the last inventory price, the cumulative cost of goods sold, the cumulative gross margin on sale, and the cumulative gross margin percentage.

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

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

the number of units being added to or subtracted from inventory. @Qty is an expression of type float or of a type that can be implicitly converted to float.

@Cost

the value associated with @Qty. Additions to inventory should have a @Cost > 0; withdrawals from inventory should have a cost <= 0. If you are not interested in calculating the gross margin on sales or if the quantity on hand will never be less than zero, then just enter zero for the @Cost when the @Qty is less than zero. @Cost is an expression of type float or of a type that can be implicitly converted to float.

@RV

the calculated value returned by the function. Permissible values are:

'Q','QTY'

Quantity on hand

'B','EV','EB'

Inventory value

'G','GM'

Gross margin

'C','COG','COGS'

Cost of goods sold

'U','UP'

(Average) Unit price

'L','LP'

Last price

'CC','COGC','COGSC'

Cumulative cost of goods sold

'GC','GMC'

Cumulative gross margin

'GP','GMP'

Gross margin percentage

'CGP','CGMP'

Cumulative gross margin percentage

@Round

the number of decimals places to store the result. @Round is only used in the calculation of the gross margin. @Round is an expression of type int or of a type that can be implicitly converted to int.

@RowNum

the number of the row within the group for which the sum is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.

@Id

a unique identifier for the LIFO calculation. @Id allows you to specify multiple moving sums within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.

·There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem.

Example

In the following examples, we calculate LIFO 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.

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 another table, #fx, and populate it with some data.

CREATETABLE #fx(

rn int,

ccy char(3),

amt_ccy money,

rate float,

ctr char(3),

amt_ctr money,

PRIMARYKEY (rn)

)

--Populate the table with some data

INSERTINTO #fx VALUES (1,'GBP',8000000,1.619,'USD',-12952000)

INSERTINTO #fx VALUES (2,'GBP',-10000000,1.62,'USD',16200000)

INSERTINTO #fx VALUES (3,'GBP',-4000000,1.613,'USD',6452000)

INSERTINTO #fx VALUES (4,'GBP',7000000,1.618,'USD',-11326000)

INSERTINTO #fx VALUES (5,'GBP',6000000,1.623,'USD',-9738000)

INSERTINTO #fx VALUES (6,'GBP',-5000000,1.618,'USD',8090000)

INSERTINTO #fx VALUES (7,'GBP',-10000000,1.602,'USD',16020000)

INSERTINTO #fx VALUES (8,'GBP',2000000,1.608,'USD',-3216000)

INSERTINTO #fx VALUES (9,'GBP',-2000000,1.602,'USD',3204000)

INSERTINTO #fx VALUES (10,'GBP',10000000,1.626,'USD',-16260000)

In this SQL we will keep track of GBP position, the USD position, and the P/L.