Home

XLeratorDB function packages for SQL Server
financial view documentation pricing
statistics view documentation pricing
math view documentation pricing
engineering view documentation pricing
strings view documentation pricing
financial-options view documentation pricing
windowing view documentation pricing

XLeratorDB Compilation packages for SQL Server
Suite incl: financial, statistics, math, engineering & strings pricing
Suite (Developer) requires SQL Server Developer Edition pricing
Suite (Subscription) One-year non-recurring license pricing

SuitePLUS incl: all Suite packages PLUS financial-options pricing
SuitePLUS (Developer) requires SQL Server Developer Edition, also incl: financial-options pricing
SuitePLUS (Subscription) One-year non-recurring license, also incl: financial-options pricing

XLeratorDLL function packages Microsoft .NET API Library
financial (DLL) view documentation pricing SQL Server not required

View All Product Pricing ...

Download Free 15 Day Trial ...

Documentation

Purchase

XLeratorDB function packages for SQL Server (2008 & later)
financial
statistics
math
engineering
strings
financial-options
windowing

XLeratorDB Compilation packages for SQL Server (2008 & later)
Suite
Suite (Developer)
Suite (Subscription)

SuitePLUS
SuitePLUS (Developer)
SuitePLUS (Subscription)

XLeratorDLL function packages Microsoft .NET API Library
financial (DLL)

Legacy XLeratorDB Packages for SQL Server 2005
financial for SQL Server 2005 only
statistics for SQL Server 2005 only
math for SQL Server 2005 only

Suite for SQL Server 2005 only
Suite (Developer) for SQL Server 2005 only
SuitePLUS for SQL Server 2005 only
SuitePLUS (Developer) for SQL Server 2005 only

Download Trial
Case Studies
Blog
Support

XLeratorDB/financial Documentation

SQL Server LIFO running balances

LIFOtvf

Updated: 17 Apr 2017

Use the SQL Server table-valued function LIFOtvf to calculate running LIFO (Last In, First Out) values in an ordered resultant table. LIFOtvf calculates balances for each value from the first value to the last value in the ordered group or partition. LIFOtvf 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.

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

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

LIFOtvf requires monotonically ascending row numbers within a partition.

LIFOtvf resets all calculated values when the row number value is 1.

Syntax

SELECT * FROM [wct].[LIFOtvf] (

<@DataQuery, nvarchar(max),>)

Arguments

Input Name	Description
@DataQuery	An SQL statement which returns a resultant table containing the unique transaction identifier, ROW_NUMBER(), quantity and amount.

Return Type

RETURNS TABLE (

[ID] [sql_variant] NULL,

[QTY] [float] NULL,

[EB] [float] NULL,

[GM] [float] NULL,

[COGS] [float] NULL,

[UP] [float] NULL,

[LP] [float] NULL,

[COGSC] [float] NULL,

[GMC] [float] NULL,

[GMP] [float] NULL,

[CGMP] [float] NULL

)

Column	Description
ID	unique transaction identifier
QTY	inventory quantity
EB	inventory value
GM	gross margin on sale
COGS	cost of goods sold
UP	average inventory price per unit
LP	last item-added price
COGSC	cumulative cost-of-goods-sold
GMC	cumulative gross margin
GMP	gross margin percentage
CGMP	cumulative gross margin percentage

Remarks

If the 3rd column (quantity) of the resultant table from @DataQuery contains NULL an error will be generated.
This function relies on the SQL Server ROW_NUMBER() function and expects results to be returned in ascending ROW_NUMBER() order within a PARTITION.
When ROW_NUMBER()= 1 all inventory values are re-initialized.
Available in XLeratorDB / financial 2008 only

Examples

Example #1

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.

--Create the temporary table

CREATE TABLE #c (

trn int,

sym char(3),

tDate date,

qty money,

price_unit money,

price_extended money,

PRIMARY KEY (trn)

)

--Populate the table with some data

INSERT INTO #c VALUES (01131019,'XYZ','2013-10-19',424,25.13,10655.12)

INSERT INTO #c VALUES (02130617,'ABC','2013-06-17',313,12.93,4047.09)

INSERT INTO #c VALUES (03130308,'ABC','2013-03-08',-157,13.17,-2067.69)

INSERT INTO #c VALUES (04130516,'GHI','2013-05-16',160,34.48,5516.8)

INSERT INTO #c VALUES (05130706,'XYZ','2013-07-06',-170,23.46,-3988.2)

INSERT INTO #c VALUES (06130924,'GHI','2013-09-24',328,34.95,11463.6)

INSERT INTO #c VALUES (07130722,'ABC','2013-07-22',599,13.65,8176.35)

INSERT INTO #c VALUES (08131231,'ABC','2013-12-31',-145,13.19,-1912.55)

INSERT INTO #c VALUES (09131025,'XYZ','2013-10-25',-153,24.31,-3719.43)

INSERT INTO #c VALUES (10130908,'ABC','2013-09-08',-386,13.65,-5268.9)

INSERT INTO #c VALUES (11130906,'XYZ','2013-09-06',-13,23.97,-311.61)

INSERT INTO #c VALUES (12130621,'ABC','2013-06-21',-326,12.73,-4149.98)

INSERT INTO #c VALUES (13131221,'GHI','2013-12-21',72,34.38,2475.36)

INSERT INTO #c VALUES (14130705,'XYZ','2013-07-05',-277,25.01,-6927.77)

INSERT INTO #c VALUES (15130307,'GHI','2013-03-07',559,35.21,19682.39)

INSERT INTO #c VALUES (16131107,'ABC','2013-11-07',27,12.68,342.36)

INSERT INTO #c VALUES (17130924,'GHI','2013-09-24',-291,35.69,-10385.79)

INSERT INTO #c VALUES (18140125,'GHI','2014-01-25',-78,35.46,-2765.88)

INSERT INTO #c VALUES (19130516,'XYZ','2013-05-16',315,23.57,7424.55)

INSERT INTO #c VALUES (20130518,'ABC','2013-05-18',298,13.23,3942.54)

INSERT INTO #c VALUES (21131103,'XYZ','2013-11-03',-326,23.24,-7576.24)

INSERT INTO #c VALUES (22131012,'XYZ','2013-10-12',596,23.16,13803.36)

INSERT INTO #c VALUES (23130619,'XYZ','2013-06-19',296,23.46,6944.16)

INSERT INTO #c VALUES (24130418,'XYZ','2013-04-18',275,24.83,6828.25)

INSERT INTO #c VALUES (25130408,'ABC','2013-04-08',298,12.98,3868.04)

INSERT INTO #c VALUES (26130320,'ABC','2013-03-20',-92,13.64,-1254.88)

INSERT INTO #c VALUES (27130906,'XYZ','2013-09-06',-147,23.81,-3500.07)

INSERT INTO #c VALUES (28131209,'XYZ','2013-12-09',315,24.46,7704.9)

INSERT INTO #c VALUES (29130602,'XYZ','2013-06-02',114,24.29,2769.06)

INSERT INTO #c VALUES (30130519,'XYZ','2013-05-19',467,23.15,10811.05)

INSERT INTO #c VALUES (31140205,'XYZ','2014-02-05',42,24.39,1024.38)

INSERT INTO #c VALUES (32130310,'ABC','2013-03-10',-63,12.61,-794.43)

INSERT INTO #c VALUES (33140102,'XYZ','2014-01-02',-196,22.98,-4504.08)

INSERT INTO #c VALUES (34130507,'XYZ','2013-05-07',55,23.43,1288.65)

INSERT INTO #c VALUES (35130321,'XYZ','2013-03-21',275,24.83,6828.25)

INSERT INTO #c VALUES (36130917,'XYZ','2013-09-17',92,24.6,2263.2)

INSERT INTO #c VALUES (37130220,'XYZ','2013-02-20',528,23.54,12429.12)

INSERT INTO #c VALUES (38130311,'XYZ','2013-03-11',-193,24.9,-4805.7)

INSERT INTO #c VALUES (39130908,'ABC','2013-09-08',490,12.69,6218.1)

INSERT INTO #c VALUES (40131013,'XYZ','2013-10-13',359,23.91,8583.69)

INSERT INTO #c VALUES (41130310,'ABC','2013-03-10',463,13.38,6194.94)

INSERT INTO #c VALUES (42131011,'XYZ','2013-10-11',-250,23.12,-5780)

INSERT INTO #c VALUES (43130521,'GHI','2013-05-21',-174,34.2,-5950.8)

INSERT INTO #c VALUES (44130227,'XYZ','2013-02-27',357,22.86,8161.02)

INSERT INTO #c VALUES (45131030,'XYZ','2013-10-30',-350,23.36,-8176)

INSERT INTO #c VALUES (46130301,'ABC','2013-03-01',157,13.01,2042.57)

INSERT INTO #c VALUES (47130619,'XYZ','2013-06-19',413,25.18,10399.34)

INSERT INTO #c VALUES (48130430,'ABC','2013-04-30',229,13.32,3050.28)

INSERT INTO #c VALUES (49130508,'ABC','2013-05-08',238,12.79,3044.02)

INSERT INTO #c VALUES (50131103,'GHI','2013-11-03',-246,35.61,-8760.06)

INSERT INTO #c VALUES (51131206,'GHI','2013-12-06',85,33.64,2859.4)

INSERT INTO #c VALUES (52131014,'GHI','2013-10-14',-91,33.12,-3013.92)

INSERT INTO #c VALUES (53140102,'GHI','2014-01-02',396,35.52,14065.92)

INSERT INTO #c VALUES (54130831,'XYZ','2013-08-31',-61,23.23,-1417.03)

INSERT INTO #c VALUES (55130630,'XYZ','2013-06-30',-272,23.45,-6378.4)

INSERT INTO #c VALUES (56130419,'GHI','2013-04-19',416,34.46,14335.36)

INSERT INTO #c VALUES (57130813,'XYZ','2013-08-13',-163,23.65,-3854.95)

INSERT INTO #c VALUES (58130722,'XYZ','2013-07-22',-88,24.64,-2168.32)

INSERT INTO #c VALUES (59130320,'XYZ','2013-03-20',-20,23.59,-471.8)

INSERT INTO #c VALUES (60130419,'XYZ','2013-04-19',277,22.83,6323.91)

INSERT INTO #c VALUES (61130916,'ABC','2013-09-16',-202,12.94,-2613.88)

INSERT INTO #c VALUES (62131027,'XYZ','2013-10-27',-248,24.71,-6128.08)

INSERT INTO #c VALUES (63130806,'GHI','2013-08-06',445,33.5,14907.5)

INSERT INTO #c VALUES (64140109,'XYZ','2014-01-09',-253,24.46,-6188.38)

INSERT INTO #c VALUES (65131227,'GHI','2013-12-27',376,33.49,12592.24)

INSERT INTO #c VALUES (66140203,'XYZ','2014-02-03',459,23.7,10878.3)

INSERT INTO #c VALUES (67130302,'XYZ','2013-03-02',9,24.04,216.36)

INSERT INTO #c VALUES (68130223,'ABC','2013-02-23',238,12.78,3041.64)

INSERT INTO #c VALUES (69130403,'GHI','2013-04-03',-151,33.16,-5007.16)

INSERT INTO #c VALUES (70130702,'GHI','2013-07-02',-162,35.48,-5747.76)

INSERT INTO #c VALUES (71130731,'ABC','2013-07-31',79,13.55,1070.45)

INSERT INTO #c VALUES (72140204,'XYZ','2014-02-04',-208,24.36,-5066.88)

INSERT INTO #c VALUES (73131028,'GHI','2013-10-28',-46,34.65,-1593.9)

INSERT INTO #c VALUES (74130619,'XYZ','2013-06-19',202,22.95,4635.9)

INSERT INTO #c VALUES (75131216,'GHI','2013-12-16',500,33.55,16775)

INSERT INTO #c VALUES (76131009,'ABC','2013-10-09',249,13.47,3354.03)

INSERT INTO #c VALUES (77130825,'GHI','2013-08-25',272,33.86,9209.92)

INSERT INTO #c VALUES (78140112,'XYZ','2014-01-12',332,24.28,8060.96)

INSERT INTO #c VALUES (79131223,'XYZ','2013-12-23',-376,25.12,-9445.12)

INSERT INTO #c VALUES (80140126,'XYZ','2014-01-26',404,24.23,9788.92)

INSERT INTO #c VALUES (81131123,'GHI','2013-11-23',187,33.86,6331.82)

INSERT INTO #c VALUES (82140131,'XYZ','2014-01-31',548,23.65,12960.2)

INSERT INTO #c VALUES (83130428,'XYZ','2013-04-28',142,23.13,3284.46)

INSERT INTO #c VALUES (84140104,'XYZ','2014-01-04',261,24.46,6384.06)

INSERT INTO #c VALUES (85131002,'GHI','2013-10-02',-295,32.51,-9590.45)

INSERT INTO #c VALUES (86131114,'ABC','2013-11-14',-386,12.4,-4786.4)

INSERT INTO #c VALUES (87131116,'GHI','2013-11-16',-320,34.16,-10931.2)

INSERT INTO #c VALUES (88131009,'ABC','2013-10-09',187,13.36,2498.32)

INSERT INTO #c VALUES (89130611,'ABC','2013-06-11',130,13.02,1692.6)

INSERT INTO #c VALUES (90130430,'ABC','2013-04-30',-191,13.49,-2576.59)

INSERT INTO #c VALUES (91130615,'XYZ','2013-06-15',545,23.88,13014.6)

INSERT INTO #c VALUES (92130924,'XYZ','2013-09-24',-248,24.58,-6095.84)

INSERT INTO #c VALUES (93130622,'ABC','2013-06-22',-227,13.47,-3057.69)

INSERT INTO #c VALUES (94131117,'GHI','2013-11-17',-92,32.65,-3003.8)

INSERT INTO #c VALUES (95130908,'GHI','2013-09-08',103,35.42,3648.26)

INSERT INTO #c VALUES (96130716,'GHI','2013-07-16',-347,35.29,-12245.63)

INSERT INTO #c VALUES (97131125,'XYZ','2013-11-25',-278,23.18,-6444.04)

INSERT INTO #c VALUES (98130413,'XYZ','2013-04-13',243,24.25,5892.75)

INSERT INTO #c VALUES (99130515,'XYZ','2013-05-15',97,25.13,2437.61)

--Calculate the LIFO Values and store in temp table

SELECT

CAST([ID] as Int) as ID

,[QTY]

,[EB]

,[GM]

,[COGS]

,[UP]

,[LP]

,[COGSC]

,[GMC]

,[GMP]

,[CGMP]

INTO

#lifo

FROM

wct.LIFOtvf('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

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

,l.[EB] as [Inventory Cost]

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

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

,l.[UP] as [Average Price]

,l.[LP] as [Last Price]

,l.[COGSC] as [Cumulative COGS]

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

,l.[GMP] as [GM Percentage]

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

FROM

#c c

null">INNER JOIN

#LIFO l

c.trn = l.[ID]

ORDER BY

c.sym, c.tDate, c.trn

This produces the following result.

trn sym  tDate                        qty            price_unit        price_extended      Inventory-On-Hand         Inventory Cost  Gross Margin on Sales     Cost-of-Goods Sold          Average Price             Last Price        Cumulative COGS Cumulative Gross Margin          GM Percentage Cumulative GM Percentage
----------- ---- ---------- --------------------- --------------------- --------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------- ---------------------- ------------------------
   68130223 ABC  2013-02-23                238.00                 12.78               3041.64                    238                3041.64                      0                      0                  12.78                  12.78                      0                       0                   NULL                     NULL
   46130301 ABC  2013-03-01                157.00                 13.01               2042.57                    395                5084.21                      0                      0        12.871417721519                  13.01                      0                       0                   NULL                     NULL
    3130308 ABC  2013-03-08               -157.00                 13.17              -2067.69                    238                3041.64       25.1199999999999               -2042.57                  12.78                  13.01               -2042.57        25.1199999999999     0.0121488230827638       0.0121488230827638
   32130310 ABC  2013-03-10                -63.00                 12.61               -794.43                    175                 2236.5      -10.7099999999999                -805.14                  12.78                  13.01               -2847.71                   14.41    -0.0134813639968278      0.00503472950120888
   41130310 ABC  2013-03-10                463.00                 13.38               6194.94                    638                8431.44                      0                      0       13.2154231974922                  13.38               -2847.71                   14.41                   NULL      0.00503472950120888
   26130320 ABC  2013-03-20                -92.00                 13.64              -1254.88                    546                7200.48        23.920000000001               -1230.96       13.1876923076923                  13.38               -4078.67         38.330000000001     0.0190615835777134      0.00931017731357808
   25130408 ABC  2013-04-08                298.00                 12.98               3868.04                    844               11068.52                      0                      0       13.1143601895735                  12.98               -4078.67         38.330000000001                   NULL      0.00931017731357808
   48130430 ABC  2013-04-30                229.00                 13.32               3050.28                   1073                14118.8                      0                      0       13.1582479030755                  13.32               -4078.67         38.330000000001                   NULL      0.00931017731357808
   90130430 ABC  2013-04-30               -191.00                 13.49              -2576.59                    882               11574.68       32.4699999999993               -2544.12       13.1232199546485                  13.32               -6622.79        70.8000000000003      0.012601927353595       0.0105772836400198
   49130508 ABC  2013-05-08                238.00                 12.79               3044.02                   1120                14618.7                      0                      0       13.0524107142857                  12.79               -6622.79        70.8000000000003                   NULL       0.0105772836400198
   20130518 ABC  2013-05-18                298.00                 13.23               3942.54                   1418               18561.24                      0                      0       13.0897320169252                  13.23               -6622.79        70.8000000000003                   NULL       0.0105772836400198
   89130611 ABC  2013-06-11                130.00                 13.02               1692.60                   1548               20253.84                      0                      0       13.0838759689922                  13.02               -6622.79        70.8000000000003                   NULL       0.0105772836400198
    2130617 ABC  2013-06-17                313.00                 12.93               4047.09                   1861               24300.93                      0                      0       13.0579957012359                  12.93               -6622.79        70.8000000000003                   NULL       0.0105772836400198
   12130621 ABC  2013-06-21               -326.00                 12.73              -4149.98                   1535               20084.58       -66.369999999999               -4216.35       13.0844169381108                  12.93              -10839.14        4.43000000000131    -0.0159928481583041     0.000408537040845525
   93130622 ABC  2013-06-22               -227.00                 13.47              -3057.69                   1308               17105.94       79.0500000000006               -2978.64       13.0779357798165                  12.93              -13817.78         83.480000000002     0.0258528497002641        0.006005211038424
    7130722 ABC  2013-07-22                599.00                 13.65               8176.35                   1907               25282.29                      0                      0       13.2576245411641                  13.65              -13817.78         83.480000000002                   NULL        0.006005211038424
   71130731 ABC  2013-07-31                 79.00                 13.55               1070.45                   1986               26352.74                      0                      0       13.2692547834844                  13.55              -13817.78         83.480000000002                   NULL        0.006005211038424
   10130908 ABC  2013-09-08               -386.00                 13.65              -5268.90                   1600               21091.74       7.89999999999964                  -5261             13.1823375                  13.55              -19078.78        91.3800000000016    0.00149936419366464      0.00476678337583002
   39130908 ABC  2013-09-08                490.00                 12.69               6218.10                   2090               27309.84                      0                      0       13.0669090909091                  12.69              -19078.78        91.3800000000016                   NULL      0.00476678337583002
   61130916 ABC  2013-09-16               -202.00                 12.94              -2613.88                   1888               24746.46       50.4999999999991               -2563.38       13.1072351694915                  12.69              -21642.16        141.880000000001     0.0193199381761975      0.00651302513216101
   76131009 ABC  2013-10-09                249.00                 13.47               3354.03                   2137               28100.49                      0                      0       13.1495039775386                  13.47              -21642.16        141.880000000001                   NULL      0.00651302513216101
   88131009 ABC  2013-10-09                187.00                 13.36               2498.32                   2324               30598.81                      0                      0       13.1664414802065                  13.36              -21642.16        141.880000000001                   NULL      0.00651302513216101
   16131107 ABC  2013-11-07                 27.00                 12.68                342.36                   2351               30941.17                      0                      0       13.1608549553382                  12.68              -21642.16        141.880000000001                   NULL      0.00651302513216101
   86131114 ABC  2013-11-14               -386.00                 12.40              -4786.40                   1965               25783.65      -371.120000000001               -5157.52       13.1214503816794                  12.68              -26799.68                 -229.24    -0.0775363530001673     -0.00862763281300574
    8131231 ABC  2013-12-31               -145.00                 13.19              -1912.55                   1820               23883.54       12.4399999999994               -1900.11       13.1228241758242                  12.68              -28699.79       -216.800000000001    0.00650440511359147     -0.00761156044361918
   15130307 GHI  2013-03-07                559.00                 35.21              19682.39                    559               19682.39                      0                      0                  35.21                  35.21                      0                       0                   NULL                     NULL
   69130403 GHI  2013-04-03               -151.00                 33.16              -5007.16                    408               14365.68      -309.549999999999               -5316.71                  35.21                  35.21               -5316.71       -309.549999999999    -0.0618214716525933      -0.0618214716525933
   56130419 GHI  2013-04-19                416.00                 34.46              14335.36                    824               28701.04                      0                      0        34.831359223301                  34.46               -5316.71       -309.549999999999                   NULL      -0.0618214716525933
    4130516 GHI  2013-05-16                160.00                 34.48               5516.80                    984               34217.84                      0                      0       34.7742276422764                  34.48               -5316.71       -309.549999999999                   NULL      -0.0618214716525933
   43130521 GHI  2013-05-21               -174.00                 34.20              -5950.80                    810                28218.6      -48.4400000000051      -5999.24000000001       34.8377777777778                  34.48              -11315.95       -357.990000000004   -0.00814008200578159        -0.03266940196898
   70130702 GHI  2013-07-02               -162.00                 35.48              -5747.76                    648               22636.08       165.240000000003               -5582.52       34.9322222222222                  34.48              -16898.47       -192.750000000001     0.0287485907553557      -0.0115379642421878
   96130716 GHI  2013-07-16               -347.00                 35.29             -12245.63                    301               10598.21       207.759999999998              -12037.87                  35.21                  34.48              -28936.34        15.0099999999975     0.0169660523795018     0.000518455961466304
   63130806 GHI  2013-08-06                445.00                 33.50              14907.50                    746               25505.71                      0                      0       34.1899597855228                   33.5              -28936.34        15.0099999999975                   NULL     0.000518455961466304
   77130825 GHI  2013-08-25                272.00                 33.86               9209.92                   1018               34715.63                      0                      0       34.1017976424361                  33.86              -28936.34        15.0099999999975                   NULL     0.000518455961466304
   95130908 GHI  2013-09-08                103.00                 35.42               3648.26                   1121               38363.89                      0                      0       34.2229170383586                  35.42              -28936.34        15.0099999999975                   NULL     0.000518455961466304
    6130924 GHI  2013-09-24                328.00                 34.95              11463.60                   1449               49827.49                      0                      0       34.3875017253278                  34.95              -28936.34        15.0099999999975                   NULL     0.000518455961466304
   17130924 GHI  2013-09-24               -291.00                 35.69             -10385.79                   1158               39657.04       215.340000000004              -10170.45       34.2461485319516                  34.95              -39106.79        230.350000000001     0.0207340991874478       0.0058557892109086
   85131002 GHI  2013-10-02               -295.00                 32.51              -9590.45                    863               29467.33      -599.260000000002              -10189.71       34.1452259559676                  34.95               -49296.5       -368.910000000001     -0.062485076299861     -0.00753991766199808
   52131014 GHI  2013-10-14                -91.00                 33.12              -3013.92                    772               26386.07      -67.3399999999983               -3081.26       34.1788471502591                  34.95              -52377.76       -436.249999999999    -0.0223429951690816     -0.00839887019072028
   73131028 GHI  2013-10-28                -46.00                 34.65              -1593.90                    726               24835.71       43.5399999999995               -1550.36       34.2089669421488                  34.95              -53928.12                 -392.71     0.0273166447079487     -0.00733551867819822
   50131103 GHI  2013-11-03               -246.00                 35.61              -8760.06                    480               16594.71       519.059999999999                  -8241             34.5723125                  34.95              -62169.12                  126.35     0.0592530188149396      0.00202823736621619
   87131116 GHI  2013-11-16               -320.00                 34.16             -10931.20                    160                 5633.6       -29.909999999998              -10961.11                  35.21                  34.95              -73130.23        96.4400000000019   -0.00273620462529256      0.00131700649503797
   94131117 GHI  2013-11-17                -92.00                 32.65              -3003.80                     68                2394.28                -235.52               -3239.32                  35.21                  34.95              -76369.55       -139.079999999999    -0.0784073506891272     -0.00182446730290393
   81131123 GHI  2013-11-23                187.00                 33.86               6331.82                    255                 8726.1                      0                      0                  34.22                  33.86              -76369.55       -139.079999999999                   NULL     -0.00182446730290393
   51131206 GHI  2013-12-06                 85.00                 33.64               2859.40                    340                11585.5                      0                      0                 34.075                  33.64              -76369.55       -139.079999999999                   NULL     -0.00182446730290393
   75131216 GHI  2013-12-16                500.00                 33.55              16775.00                    840                28360.5                      0                      0                33.7625                  33.55              -76369.55       -139.079999999999                   NULL     -0.00182446730290393
   13131221 GHI  2013-12-21                 72.00                 34.38               2475.36                    912               30835.86                      0                      0               33.81125                  34.38              -76369.55       -139.079999999999                   NULL     -0.00182446730290393
   65131227 GHI  2013-12-27                376.00                 33.49              12592.24                   1288                43428.1                      0                      0       33.7174689440994                  33.49              -76369.55       -139.079999999999                   NULL     -0.00182446730290393
   53140102 GHI  2014-01-02                396.00                 35.52              14065.92                   1684               57494.02                      0                      0       34.1413420427553                  35.52              -76369.55       -139.079999999999                   NULL     -0.00182446730290393
   18140125 GHI  2014-01-25                -78.00                 35.46              -2765.88                   1606               54723.46      -4.67999999999756               -2770.56       34.0743835616438                  35.52              -79140.11       -143.759999999996   -0.00169204737732568     -0.00181983091623849
   37130220 XYZ  2013-02-20                528.00                 23.54              12429.12                    528               12429.12                      0                      0                  23.54                  23.54                      0                       0                   NULL                     NULL
   44130227 XYZ  2013-02-27                357.00                 22.86               8161.02                    885               20590.14                      0                      0       23.2656949152542                  22.86                      0                       0                   NULL                     NULL
   67130302 XYZ  2013-03-02                  9.00                 24.04                216.36                    894                20806.5                      0                      0       23.2734899328859                  24.04                      0                       0                   NULL                     NULL
   38130311 XYZ  2013-03-11               -193.00                 24.90              -4805.70                    701                16383.9       383.100000000001                -4422.6        23.372182596291                  24.04                -4422.6        383.100000000001     0.0797178350708536       0.0797178350708536
   59130320 XYZ  2013-03-20                -20.00                 23.59               -471.80                    681                15926.7       14.5999999999993      -457.200000000001       23.3872246696035                  24.04                -4879.8        397.700000000001     0.0309453158117831       0.0753576504026529
   35130321 XYZ  2013-03-21                275.00                 24.83               6828.25                    956               22754.95                      0                      0       23.8022489539749                  24.83                -4879.8        397.700000000001                   NULL       0.0753576504026529
   98130413 XYZ  2013-04-13                243.00                 24.25               5892.75                   1199                28647.7                      0                      0       23.8929941618015                  24.25                -4879.8        397.700000000001                   NULL       0.0753576504026529
   24130418 XYZ  2013-04-18                275.00                 24.83               6828.25                   1474               35475.95                      0                      0       24.0678086838535                  24.83                -4879.8        397.700000000001                   NULL       0.0753576504026529
   60130419 XYZ  2013-04-19                277.00                 22.83               6323.91                   1751               41799.86                      0                      0       23.8719931467733                  22.83                -4879.8        397.700000000001                   NULL       0.0753576504026529
   83130428 XYZ  2013-04-28                142.00                 23.13               3284.46                   1893               45084.32                      0                      0       23.8163338615954                  23.13                -4879.8        397.700000000001                   NULL       0.0753576504026529
   34130507 XYZ  2013-05-07                 55.00                 23.43               1288.65                   1948               46372.97                      0                      0       23.8054260780287                  23.43                -4879.8        397.700000000001                   NULL       0.0753576504026529
   99130515 XYZ  2013-05-15                 97.00                 25.13               2437.61                   2045               48810.58                      0                      0       23.8682542787286                  25.13                -4879.8        397.700000000001                   NULL       0.0753576504026529
   19130516 XYZ  2013-05-16                315.00                 23.57               7424.55                   2360               56235.13                      0                      0       23.8284449152542                  23.57                -4879.8        397.700000000001                   NULL       0.0753576504026529
   30130519 XYZ  2013-05-19                467.00                 23.15              10811.05                   2827               67046.18                      0                      0       23.7163707110011                  23.15                -4879.8        397.700000000001                   NULL       0.0753576504026529
   29130602 XYZ  2013-06-02                114.00                 24.29               2769.06                   2941               69815.24                      0                      0        23.738605916355                  24.29                -4879.8        397.700000000001                   NULL       0.0753576504026529
   91130615 XYZ  2013-06-15                545.00                 23.88              13014.60                   3486               82829.84                      0                      0        23.760711417097                  23.88                -4879.8        397.700000000001                   NULL       0.0753576504026529
   23130619 XYZ  2013-06-19                296.00                 23.46               6944.16                   3782                  89774                      0                      0        23.737176097303                  23.46                -4879.8        397.700000000001                   NULL       0.0753576504026529
   47130619 XYZ  2013-06-19                413.00                 25.18              10399.34                   4195              100173.34                      0                      0       23.8792228843862                  25.18                -4879.8        397.700000000001                   NULL       0.0753576504026529
   74130619 XYZ  2013-06-19                202.00                 22.95               4635.90                   4397              104809.24                      0                      0       23.8365340004549                  22.95                -4879.8        397.700000000001                   NULL       0.0753576504026529
   55130630 XYZ  2013-06-30               -272.00                 23.45              -6378.40                   4125               98410.74      -20.0999999999858      -6398.49999999999       23.8571490909091                  22.95               -11278.3        377.600000000015   -0.00315126050419946       0.0323956108065456
   14130705 XYZ  2013-07-05               -277.00                 25.01              -6927.77                   3848               91435.88      -47.0900000000001               -6974.86       23.7619230769231                  22.95              -18253.16        330.510000000015   -0.00679728108756499       0.0177849692767906
    5130706 XYZ  2013-07-06               -170.00                 23.46              -3988.20                   3678               87334.16      -113.520000000016      -4101.72000000002       23.7450135943448                  22.95              -22354.88        216.989999999999    -0.0284639687076917      0.00961329300585192
   58130722 XYZ  2013-07-22                -88.00                 24.64              -2168.32                   3590               85269.68       103.840000000004               -2064.48                 23.752                  22.95              -24419.36        320.830000000003     0.0478896103896123       0.0129679683139056
   57130813 XYZ  2013-08-13               -163.00                 23.65              -3854.95                   3427               81420.92       6.19000000000506      -3848.75999999999       23.7586577181208                  22.95              -28268.12        327.020000000008     0.0016057277007497       0.0114362090900764
   54130831 XYZ  2013-08-31                -61.00                 23.23              -1417.03                   3366               79964.24      -39.6500000000076      -1456.68000000001        23.756458704694                  22.95               -29724.8                  287.37    -0.0279810589754681      0.00957511569473318
   11130906 XYZ  2013-09-06                -13.00                 23.97               -311.61                   3353                79653.8       1.16999999999769      -310.440000000002        23.755979719654                  22.95              -30035.24        288.539999999998    0.00375469336670096      0.00951530449040318
   27130906 XYZ  2013-09-06               -147.00                 23.81              -3500.07                   3206               76143.44      -10.2900000000004               -3510.36       23.7502932002495                  22.95               -33545.6        278.249999999998    -0.0029399412011761      0.00822644376675031
   36130917 XYZ  2013-09-17                 92.00                 24.60               2263.20                   3298               78406.64                      0                      0       23.7739963614312                   24.6               -33545.6        278.249999999998                   NULL      0.00822644376675031
   92130924 XYZ  2013-09-24               -248.00                 24.58              -6095.84                   3050               72418.16       107.360000000004               -5988.48       23.7436590163934                   24.6              -39534.08        385.610000000002     0.0176120108139328       0.0096596441505433
   42131011 XYZ  2013-10-11               -250.00                 23.12              -5780.00                   2800               66421.13      -217.029999999999               -5997.03       23.7218321428571                   24.6              -45531.11        168.580000000003    -0.0375484429065742      0.00368886528551951
   22131012 XYZ  2013-10-12                596.00                 23.16              13803.36                   3396               80224.49                      0                      0        23.623230270907                  23.16              -45531.11        168.580000000003                   NULL      0.00368886528551951
   40131013 XYZ  2013-10-13                359.00                 23.91               8583.69                   3755               88808.18                      0                      0       23.6506471371505                  23.91              -45531.11        168.580000000003                   NULL      0.00368886528551951
    1131019 XYZ  2013-10-19                424.00                 25.13              10655.12                   4179                99463.3                      0                      0       23.8007418042594                  25.13              -45531.11        168.580000000003                   NULL      0.00368886528551951
    9131025 XYZ  2013-10-25               -153.00                 24.31              -3719.43                   4026               95618.41                -125.46               -3844.89       23.7502260307998                  25.13                 -49376        43.1200000000035    -0.0337309749074454     0.000872536783334132
   62131027 XYZ  2013-10-27               -248.00                 24.71              -6128.08                   3778               89386.17      -104.159999999991      -6232.23999999999       23.6596532556908                  25.13              -55608.24       -61.0399999999873    -0.0169971671388087     -0.00109888527234473
   45131030 XYZ  2013-10-30               -350.00                 23.36              -8176.00                   3428               80989.61      -220.560000000012      -8396.56000000001       23.6259072345391                  25.13               -64004.8                  -281.6    -0.0269765166340524     -0.00441911266226429
   21131103 XYZ  2013-11-03               -326.00                 23.24              -7576.24                   3102               73415.45       2.08000000001084      -7574.15999999999       23.6671341070277                  25.13              -71578.96       -279.519999999989    0.00027454251713394     -0.00392036739699482
   97131125 XYZ  2013-11-25               -278.00                 23.18              -6444.04                   2824               66976.97       5.55999999998949      -6438.48000000001       23.7170573654391                  25.13              -78017.44       -273.959999999999   0.000862812769627359     -0.00352389679494665
   28131209 XYZ  2013-12-09                315.00                 24.46               7704.90                   3139               74681.87                      0                      0        23.791611978337                  24.46              -78017.44       -273.959999999999                   NULL     -0.00352389679494665
   79131223 XYZ  2013-12-23               -376.00                 25.12              -9445.12                   2763               65564.58       327.830000000007      -9117.28999999999       23.7294896851249                  24.46              -87134.73         53.870000000008     0.0347089290554283     0.000617856004110721
   33140102 XYZ  2014-01-02               -196.00                 22.98              -4504.08                   2567               61027.18      -33.3199999999943      -4537.39999999999       23.7737358784573                  24.46              -91672.13        20.5500000000138   -0.00739773716274894     0.000224118217506717
   84140104 XYZ  2014-01-04                261.00                 24.46               6384.06                   2828               67411.24                      0                      0        23.837072135785                  24.46              -91672.13        20.5500000000138                   NULL     0.000224118217506717
   64140109 XYZ  2014-01-09               -253.00                 24.46              -6188.38                   2575               61222.86   2.72848410531878E-12               -6188.38       23.7758679611651                  24.46              -97860.51        20.5500000000165   4.40904421725683E-16     0.000209948686702172
   78140112 XYZ  2014-01-12                332.00                 24.28               8060.96                   2907               69283.82                      0                      0       23.8334434124527                  24.28              -97860.51        20.5500000000165                   NULL     0.000209948686702172
   80140126 XYZ  2014-01-26                404.00                 24.23               9788.92                   3311               79072.74                      0                      0       23.8818302627605                  24.23              -97860.51        20.5500000000165                   NULL     0.000209948686702172
   82140131 XYZ  2014-01-31                548.00                 23.65              12960.20                   3859               92032.94                      0                      0       23.8489090437937                  23.65              -97860.51        20.5500000000165                   NULL     0.000209948686702172
   66140203 XYZ  2014-02-03                459.00                 23.70              10878.30                   4318              102911.24                      0                      0       23.8330801296897                   23.7              -97860.51        20.5500000000165                   NULL     0.000209948686702172
   72140204 XYZ  2014-02-04               -208.00                 24.36              -5066.88                   4110               97981.64       137.279999999994      -4929.60000000001       23.8398150851582                   23.7             -102790.11        157.830000000011     0.0270935960591122      0.00153310498490801
   31140205 XYZ  2014-02-05                 42.00                 24.39               1024.38                   4152               99006.02                      0                      0        23.845380539499                  24.39             -102790.11        157.830000000011                   NULL      0.00153310498490801

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 LIFO Values and store in temp table

SELECT

CAST([ID] as Int) as ID

,[QTY]

,[EB]

,[GM]

,[COGS]

,[UP]

,[LP]

,[COGSC]

,[GMC]

,[GMP]

,[CGMP]

INTO

#lifo

FROM

wct.LIFOtvf('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

,l.[QTY] as [GBP Position]

,l.[EB] as [USD Position]

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

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

,l.[UP] as [Average Price]

,l.[LP] as [Last Price]

,l.[COGSC] as [Cumulative COGS]

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

,l.[GMP] as [GM Percentage]

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

FROM

#fx c

null">INNER JOIN

#LIFO l

c.trn = l.[ID]

ORDER BY

c.trn

This produces the following result.

trn ccy                amt_ccy                   rate ctr                amt_ctr           GBP Position           USD Position                    P/L     Cost-of-Goods Sold          Average Price             Last Price        Cumulative COGS         Cumulative P/L          GM Percentage Cumulative GM Percentage
----------- ---- --------------------- ---------------------- ---- --------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ------------------------
        101 GBP             8000000.00                  1.619 USD           -12952000.00                8000000               12952000                      0                      0                  1.619                  1.619                      0                      0                   NULL                     NULL
        102 GBP           -10000000.00                   1.62 USD            16200000.00               -2000000               -3240000                   8000              -12952000                   1.62                   1.62              -12952000                   8000   0.000617283950617284     0.000617283950617284
        103 GBP            -4000000.00                  1.613 USD             6452000.00               -6000000               -9692000                      0                      0       1.61533333333333                  1.613              -12952000                   8000                   NULL     0.000617283950617284
        104 GBP             7000000.00                  1.618 USD           -11326000.00                1000000                1618000                 -16000                9692000                  1.618                  1.618               -3260000                  -8000    0.00164812525751957       -0.002460024600246
        105 GBP             6000000.00                  1.623 USD            -9738000.00                7000000               11356000                      0                      0       1.62228571428571                  1.623               -3260000                  -8000                   NULL       -0.002460024600246
        106 GBP            -5000000.00                  1.618 USD             8090000.00                2000000                3241000                 -25000               -8115000                 1.6205                  1.623              -11375000                 -33000    -0.0030902348578492      -0.0029095397637101
        107 GBP           -10000000.00                  1.602 USD            16020000.00               -8000000              -12816000                 -37000               -3241000                  1.602                  1.602              -14616000                 -70000    -0.0115480649188514     -0.00481231953801732
        108 GBP             2000000.00                  1.608 USD            -3216000.00               -6000000               -9612000                 -12000                3204000                  1.602                  1.602              -11412000                 -82000    0.00373134328358209     -0.00723742277140335
        109 GBP            -2000000.00                  1.602 USD             3204000.00               -8000000              -12816000                      0                      0                  1.602                  1.602              -11412000                 -82000                   NULL     -0.00723742277140335
        110 GBP            10000000.00                  1.626 USD           -16260000.00                2000000                3252000                -192000               12816000                  1.626                  1.626                1404000                -274000      0.014760147601476        0.163289630512515

Example #3

Using the #c temp table created in Example #1, we will insert the necessary input for the LIFOtvf 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

ORDER BY

sym, tDate, trn

--Calculate the LIFO Values and store in temp table

SELECT

CAST([ID] as Int) as ID

,[QTY]

,[EB]

,[GM]

,[COGS]

,[UP]

,[LP]

,[COGSC]

,[GMC]

,[GMP]

,[CGMP]

INTO

#LIFO

FROM

wct.LIFOtvf('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

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

,l.[EB] as [Inventory Cost]

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

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

,l.[UP] as [Average Price]

,l.[LP] as [Last Price]

,l.[COGSC] as [Cumulative COGS]

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

,l.[GMP] as [GM Percentage]

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

FROM

#inv i

null">INNER JOIN

#LIFO l

i.ID = l.[ID]

null">INNER JOIN

#c c

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.

--Put some data into #p

SELECT

INTO

FROM (VALUES

('ZYX','Coffee Mug','Ceramic','8 oz','Black','2017-01-15',50,1.12,56)

,('ZYX','Coffee Mug','Ceramic','12 oz','Black','2017-01-15',50,1.12,56)

,('ZYX','Coffee Mug','Ceramic','16 oz','Black','2017-01-15',50,1.12,56)

,('ZYX','Coffee Mug','Ceramic','20 oz','Black','2017-01-15',50,1.12,56)

,('ZYX','Coffee Mug','Ceramic','8 oz','White','2017-01-15',50,1.12,56)

,('ZYX','Coffee Mug','Ceramic','12 oz','White','2017-01-15',50,1.12,56)

,('ZYX','Coffee Mug','Ceramic','16 oz','White','2017-01-15',50,1.12,56)

,('ZYX','Coffee Mug','Ceramic','20 oz','White','2017-01-15',50,1.12,56)

,('ZYX','Coffee Mug','Plastic','8 oz','Black','2017-01-15',50,0.89,44.5)

,('ZYX','Coffee Mug','Plastic','12 oz','Black','2017-01-15',50,0.89,44.5)

,('ZYX','Coffee Mug','Plastic','16 oz','Black','2017-01-15',50,0.89,44.5)

,('ZYX','Coffee Mug','Plastic','20 oz','Black','2017-01-15',50,0.89,44.5)

,('ZYX','Coffee Mug','Plastic','8 oz','White','2017-01-15',50,0.89,44.5)

,('ZYX','Coffee Mug','Plastic','12 oz','White','2017-01-15',50,0.89,44.5)

,('ZYX','Coffee Mug','Plastic','16 oz','White','2017-01-15',50,0.89,44.5)

,('ZYX','Coffee Mug','Plastic','20 oz','White','2017-01-15',50,0.89,44.5)

,('ZYX','Coffee Mug','Ceramic','8 oz','Black','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Ceramic','12 oz','Black','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Ceramic','16 oz','Black','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Ceramic','20 oz','Black','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Ceramic','8 oz','White','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Ceramic','12 oz','White','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Ceramic','16 oz','White','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Ceramic','20 oz','White','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Plastic','8 oz','Black','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Plastic','12 oz','Black','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Plastic','16 oz','Black','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Plastic','20 oz','Black','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Plastic','8 oz','White','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Plastic','12 oz','White','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Plastic','16 oz','White','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Plastic','20 oz','White','2017-02-15',50,1.15,57.5)

,('ZYX','Coffee Mug','Plastic','8 oz','White','2017-01-16',-5,2.95,-14.75)

,('ZYX','Coffee Mug','Plastic','8 oz','White','2017-01-23',-2,2.95,-5.9)

,('ZYX','Coffee Mug','Plastic','8 oz','White','2017-01-24',-1,2.95,-2.95)

,('ZYX','Coffee Mug','Plastic','8 oz','White','2017-02-01',-3,2.95,-8.85)

,('ZYX','Coffee Mug','Plastic','8 oz','White','2017-02-08',-7,2.95,-20.65)

,('ZYX','Coffee Mug','Plastic','8 oz','White','2017-02-15',-6,2.95,-17.7)

,('ZYX','Coffee Mug','Plastic','8 oz','White','2017-02-22',-2,2.95,-5.9)

)n([Manufacturer],[Description],[Material],[Size],[Color],[Date],[qty],[unit price],[extended price])

--Copy the #p data into #p2 and put it in order for LIFO processing

SELECT

IDENTITY(int,1,1) as ID

,ROW_NUMBER() OVER (PARTITION BY [Manufacturer],[Description],[Material],[Size],[Color] ORDER BY [Manufacturer],[Description],[Material],[Size],[Color],[Date],qty DESC) as rn

,#p.*

INTO

#p2

FROM

ORDER BY

[Manufacturer]

,[Description]

,[Material]

,[Size]

,[Color]

,[Date]

,qty DESC

--Put the tvf results into the #LIFO table

SELECT

CAST([ID] as Int) as ID

,[QTY]

,[EB]

,[GM]

,[COGS]

,[UP]

,[LP]

,[COGSC]

,[GMC]

,[GMP]

,[CGMP]

INTO

#LIFO

FROM

wct.LIFOtvf('SELECT ID, rn,qty,[extended price] FROM #p2 ORDER BY ID')

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

SELECT

#p2.[Manufacturer]

,#p2.[Description]

,#p2.[Material]

,#p2.[Size]

,#p2.[Color]

,#p2.[Date]

,#p2.[qty]

,#p2.[unit price]

,#p2.[extended price]

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

,l.[EB] as [Inventory Cost]

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

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

,l.[UP] as [Average Price]

,l.[LP] as [Last Price]

,l.[COGSC] as [Cumulative COGS]

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

,l.[GMP] as [GM Percentage]

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

FROM

#p2

null">INNER JOIN

#LIFO l

#p2.ID = l.[ID]

ORDER BY

#p2.id

This produces the following result.

Manufacturer Description Material Size  Color Date               qty                              unit price                          extended price      Inventory-On-Hand         Inventory Cost  Gross Margin on Sales     Cost-of-Goods Sold          Average Price             Last Price        Cumulative COGS Cumulative Gross Margin          GM Percentage Cumulative GM Percentage
------------ ----------- -------- ----- ----- ---------- ----------- --------------------------------------- --------------------------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------- ---------------------- ------------------------
ZYX          Coffee Mug  Ceramic  12 oz Black 2017-01-15          50                                    1.12                                   56.00                     50                     56                      0                      0                   1.12                   1.12                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  12 oz Black 2017-02-15          50                                    1.15                                   57.50                    100                  113.5                      0                      0                  1.135                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  12 oz White 2017-01-15          50                                    1.12                                   56.00                     50                     56                      0                      0                   1.12                   1.12                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  12 oz White 2017-02-15          50                                    1.15                                   57.50                    100                  113.5                      0                      0                  1.135                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  16 oz Black 2017-01-15          50                                    1.12                                   56.00                     50                     56                      0                      0                   1.12                   1.12                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  16 oz Black 2017-02-15          50                                    1.15                                   57.50                    100                  113.5                      0                      0                  1.135                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  16 oz White 2017-01-15          50                                    1.12                                   56.00                     50                     56                      0                      0                   1.12                   1.12                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  16 oz White 2017-02-15          50                                    1.15                                   57.50                    100                  113.5                      0                      0                  1.135                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  20 oz Black 2017-01-15          50                                    1.12                                   56.00                     50                     56                      0                      0                   1.12                   1.12                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  20 oz Black 2017-02-15          50                                    1.15                                   57.50                    100                  113.5                      0                      0                  1.135                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  20 oz White 2017-01-15          50                                    1.12                                   56.00                     50                     56                      0                      0                   1.12                   1.12                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  20 oz White 2017-02-15          50                                    1.15                                   57.50                    100                  113.5                      0                      0                  1.135                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  8 oz  Black 2017-01-15          50                                    1.12                                   56.00                     50                     56                      0                      0                   1.12                   1.12                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  8 oz  Black 2017-02-15          50                                    1.15                                   57.50                    100                  113.5                      0                      0                  1.135                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  8 oz  White 2017-01-15          50                                    1.12                                   56.00                     50                     56                      0                      0                   1.12                   1.12                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Ceramic  8 oz  White 2017-02-15          50                                    1.15                                   57.50                    100                  113.5                      0                      0                  1.135                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  12 oz Black 2017-01-15          50                                    0.89                                   44.50                     50                   44.5                      0                      0                   0.89                   0.89                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  12 oz Black 2017-02-15          50                                    1.15                                   57.50                    100                    102                      0                      0                   1.02                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  12 oz White 2017-01-15          50                                    0.89                                   44.50                     50                   44.5                      0                      0                   0.89                   0.89                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  12 oz White 2017-02-15          50                                    1.15                                   57.50                    100                    102                      0                      0                   1.02                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  16 oz Black 2017-01-15          50                                    0.89                                   44.50                     50                   44.5                      0                      0                   0.89                   0.89                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  16 oz Black 2017-02-15          50                                    1.15                                   57.50                    100                    102                      0                      0                   1.02                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  16 oz White 2017-01-15          50                                    0.89                                   44.50                     50                   44.5                      0                      0                   0.89                   0.89                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  16 oz White 2017-02-15          50                                    1.15                                   57.50                    100                    102                      0                      0                   1.02                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  20 oz Black 2017-01-15          50                                    0.89                                   44.50                     50                   44.5                      0                      0                   0.89                   0.89                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  20 oz Black 2017-02-15          50                                    1.15                                   57.50                    100                    102                      0                      0                   1.02                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  20 oz White 2017-01-15          50                                    0.89                                   44.50                     50                   44.5                      0                      0                   0.89                   0.89                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  20 oz White 2017-02-15          50                                    1.15                                   57.50                    100                    102                      0                      0                   1.02                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  8 oz  Black 2017-01-15          50                                    0.89                                   44.50                     50                   44.5                      0                      0                   0.89                   0.89                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  8 oz  Black 2017-02-15          50                                    1.15                                   57.50                    100                    102                      0                      0                   1.02                   1.15                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  8 oz  White 2017-01-15          50                                    0.89                                   44.50                     50                   44.5                      0                      0                   0.89                   0.89                      0                       0                   NULL                     NULL
ZYX          Coffee Mug  Plastic  8 oz  White 2017-01-16          -5                                    2.95                                  -14.75                     45                  40.05                   10.3                  -4.45                   0.89                   0.89                  -4.45                    10.3      0.698305084745763        0.698305084745763
ZYX          Coffee Mug  Plastic  8 oz  White 2017-01-23          -2                                    2.95                                   -5.90                     43                  38.27       4.11999999999999      -1.78000000000001                   0.89                   0.89                  -6.23                   14.42      0.698305084745761        0.698305084745763
ZYX          Coffee Mug  Plastic  8 oz  White 2017-01-24          -1                                    2.95                                   -2.95                     42                  37.38                   2.06     -0.890000000000001                   0.89                   0.89                  -7.12                   16.48      0.698305084745763        0.698305084745763
ZYX          Coffee Mug  Plastic  8 oz  White 2017-02-01          -3                                    2.95                                   -8.85                     39                  34.71                   6.18                  -2.67                   0.89                   0.89      -9.79000000000001                   22.66      0.698305084745762        0.698305084745763
ZYX          Coffee Mug  Plastic  8 oz  White 2017-02-08          -7                                    2.95                                  -20.65                     32                  28.48                  14.42                  -6.23                   0.89                   0.89                 -16.02                   37.08      0.698305084745763        0.698305084745763
ZYX          Coffee Mug  Plastic  8 oz  White 2017-02-15          50                                    1.15                                   57.50                     82                  85.98                      0                      0       1.04853658536585                   1.15                 -16.02                   37.08                   NULL        0.698305084745763
ZYX          Coffee Mug  Plastic  8 oz  White 2017-02-15          -6                                    2.95                                  -17.70                     76                  79.08                   10.8      -6.89999999999999       1.04052631578947                   1.15                 -22.92                   47.88      0.610169491525424        0.676271186440678
ZYX          Coffee Mug  Plastic  8 oz  White 2017-02-22          -2                                    2.95                                   -5.90                     74                  76.78                    3.6                   -2.3       1.03756756756757                   1.15                 -25.22                   51.48      0.610169491525424        0.671186440677966

Products

Support

Contact Us
FAQ’s
Blog
XLeratorDB Documentation
- Financial
- Financial-Options
- Statistics
- Math
- Engineering
- Strings
- Windowing
XLeratorDLL Documentation
- Financial-DLL
XLeratorDB Installation Guide

XLeratorDB function packages
for SQL Server

XLeratorDB Compilation packages
for SQL Server

XLeratorDLL function packages
Microsoft .NET API Library

XLeratorDB function packages for
SQL Server (2008 & later)

XLeratorDB Compilation packages for
SQL Server (2008 & later)

XLeratorDLL function packages
Microsoft .NET API Library

Legacy XLeratorDB Packages for
SQL Server 2005

XLeratorDB/financial Documentation

SQL Server LIFO running balances

Products

Support

About

Pricing

XLeratorDB function packagesfor SQL Server

XLeratorDB Compilation packagesfor SQL Server

XLeratorDLL function packagesMicrosoft .NET API Library

XLeratorDB function packages for SQL Server (2008 & later)

XLeratorDB Compilation packages for SQL Server (2008 & later)

XLeratorDLL function packagesMicrosoft .NET API Library

Legacy XLeratorDB Packages for SQL Server 2005

XLeratorDB/financial Documentation

SQL Server LIFO running balances

Products

Support

About

Pricing

XLeratorDB function packages
for SQL Server

XLeratorDB Compilation packages
for SQL Server

XLeratorDLL function packages
Microsoft .NET API Library

XLeratorDB function packages for
SQL Server (2008 & later)

XLeratorDB Compilation packages for
SQL Server (2008 & later)

XLeratorDLL function packages
Microsoft .NET API Library

Legacy XLeratorDB Packages for
SQL Server 2005