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 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.

FIFOtvf requires monotonically ascending row numbers within a partition.

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

Syntax

SELECT * FROM [wct].[FIFOtvf] (

<@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	Discount factor for the node calculated using the calibrated rate plus the spread
GMC	Discount factor for the node calculated from the calibrated rates
GMP	Present value of the cash flow at the node
CGMP	Coupon amount at that step

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 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.

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

SELECT

CAST([ID] as Int) as ID

,[QTY]

,[EB]

,[GM]

,[COGS]

,[UP]

,[LP]

,[COGSC]

,[GMC]

,[GMP]

,[CGMP]

INTO

#fifo

FROM

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

c.trn = f.[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                    3077.75                61.23                  -2006.46               12.9317226890756       13.01                  -2006.46               61.23                   0.0296127562642369     0.0296127562642369
32130310    ABC  2013-03-10 -63.00                12.61                 -794.43               175                    2272.61                -10.7100000000004      -805.14                12.9863428571429       13.01                  -2811.6                50.5199999999996        -0.0134813639968284    0.0176512515198523
41130310    ABC  2013-03-10 463.00                13.38                 6194.94               638                    8467.55                0                      0                      13.2720219435737       13.38                  -2811.6                50.5199999999996        NULL                   0.0176512515198523
26130320    ABC  2013-03-20 -92.00                13.64                 -1254.88              546                    7274.77                62.1000000000004       -1192.78               13.3237545787546       13.38                  -4004.38               112.62                  0.0494868035190619     0.027354870051008
25130408    ABC  2013-04-08 298.00                12.98                 3868.04               844                    11142.81               0                      0                      13.2023815165877       12.98                  -4004.38               112.62                  NULL                   0.027354870051008
48130430    ABC  2013-04-30 229.00                13.32                 3050.28               1073                   14193.09               0                      0                      13.2274836905871       13.32                  -4004.38               112.62                  NULL                   0.027354870051008
90130430    ABC  2013-04-30 -191.00               13.49                 -2576.59              882                    11668.22               51.7199999999993       -2524.87               13.2292743764172       13.32                  -6529.25               164.339999999999        0.0200730422768075     0.0245518473644187
49130508    ABC  2013-05-08 238.00                12.79                 3044.02               1120                   14712.24               0                      0                      13.1359285714286       12.79                  -6529.25               164.339999999999        NULL                   0.0245518473644187
20130518    ABC  2013-05-18 298.00                13.23                 3942.54               1418                   18654.78               0                      0                      13.1556981664316       13.23                  -6529.25               164.339999999999        NULL                   0.0245518473644187
89130611    ABC  2013-06-11 130.00                13.02                 1692.60               1548                   20347.38               0                      0                      13.1443023255814       13.02                  -6529.25               164.339999999999        NULL                   0.0245518473644187
2130617     ABC  2013-06-17 313.00                12.93                 4047.09               1861                   24394.47               0                      0                      13.1082590005373       12.93                  -6529.25               164.339999999999        NULL                   0.0245518473644187
12130621    ABC  2013-06-21 -326.00               12.73                 -4149.98              1535                   20032.59               -211.899999999998      -4361.88               13.0505472312704       12.93                  -10891.13              -47.5599999999985       -0.0510604870384912    -0.00438600940465165
93130622    ABC  2013-06-22 -227.00               13.47                 -3057.69              1308                   17074.53               99.6299999999987       -2958.06               13.0539220183486       12.93                  -13849.19              52.0700000000003        0.0325834208176757     0.0037457036268655
7130722     ABC  2013-07-22 599.00                13.65                 8176.35               1907                   25250.88               0                      0                      13.2411536444677       13.65                  -13849.19              52.0700000000003        NULL                   0.0037457036268655
71130731    ABC  2013-07-31 79.00                 13.55                 1070.45               1986                   26321.33               0                      0                      13.2534390735146       13.55                  -13849.19              52.0700000000003        NULL                   0.0037457036268655
10130908    ABC  2013-09-08 -386.00               13.65                 -5268.90              1600                   21244.02               191.590000000006       -5077.30999999999      13.2775125             13.55                  -18926.5               243.660000000006        0.0363624285904089     0.0127103790474365
39130908    ABC  2013-09-08 490.00                12.69                 6218.10               2090                   27462.12               0                      0                      13.1397703349282       12.69                  -18926.5               243.660000000006        NULL                   0.0127103790474365
61130916    ABC  2013-09-16 -202.00               12.94                 -2613.88              1888                   24869.3                21.0599999999968       -2592.82               13.1722987288136       12.69                  -21519.32              264.720000000003        0.00805698807902305    0.0121520158795156
76131009    ABC  2013-10-09 249.00                13.47                 3354.03               2137                   28223.33               0                      0                      13.2069864295742       13.47                  -21519.32              264.720000000003        NULL                   0.0121520158795156
88131009    ABC  2013-10-09 187.00                13.36                 2498.32               2324                   30721.65               0                      0                      13.2192986230637       13.36                  -21519.32              264.720000000003        NULL                   0.0121520158795156
16131107    ABC  2013-11-07 27.00                 12.68                 342.36                2351                   31064.01               0                      0                      13.2131050616759       12.68                  -21519.32              264.720000000003        NULL                   0.0121520158795156
86131114    ABC  2013-11-14 -386.00               12.40                 -4786.40              1965                   25980.12               -297.49                -5083.89               13.2214351145038       12.68                  -26603.21              -32.7699999999971       -0.0621531840213939    -0.00123332545490391
8131231     ABC  2013-12-31 -145.00               13.19                 -1912.55              1820                   24103.38               35.810000000002        -1876.74               13.2436153846154       12.68                  -28479.95              3.04000000000485        0.0187236934982102     0.000106730367844277
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                    28091.3                -175.740000000004      -6126.54               34.6806172839506       34.48                  -11443.25              -485.290000000004       -0.0295321637426908    -0.044286527784369
70130702    GHI  2013-07-02 -162.00               35.48                 -5747.76              648                    22387.28               43.7399999999998       -5704.02               34.5482716049383       34.48                  -17147.27              -441.550000000004       0.00760992108229985    -0.0264310667244515
96130716    GHI  2013-07-16 -347.00               35.29                 -12245.63             301                    10375.66               234.01                 -12011.62              34.4706312292359       34.48                  -29158.89              -207.540000000004       0.0191096742266425     -0.00716857763109505
63130806    GHI  2013-08-06 445.00                33.50                 14907.50              746                    25283.16               0                      0                      33.89163538874         33.5                   -29158.89              -207.540000000004       NULL                   -0.00716857763109505
77130825    GHI  2013-08-25 272.00                33.86                 9209.92               1018                   34493.08               0                      0                      33.8831827111984       33.86                  -29158.89              -207.540000000004       NULL                   -0.00716857763109505
95130908    GHI  2013-09-08 103.00                35.42                 3648.26               1121                   38141.34               0                      0                      34.0243889384478       35.42                  -29158.89              -207.540000000004       NULL                   -0.00716857763109505
6130924     GHI  2013-09-24 328.00                34.95                 11463.60              1449                   49604.94               0                      0                      34.2339130434783       34.95                  -29158.89              -207.540000000004       NULL                   -0.00716857763109505
17130924    GHI  2013-09-24 -291.00               35.69                 -10385.79             1158                   39574.08               354.93                 -10030.86              34.1745077720207       34.95                  -39189.75              147.389999999997        0.0341745789198511     0.00374684077185064
85131002    GHI  2013-10-02 -295.00               32.51                 -9590.45              863                    29681.78               -301.850000000002      -9892.3                34.3937195828505       34.95                  -49082.05              -154.460000000005       -0.0314740184245788    -0.00315691003787445
52131014    GHI  2013-10-14 -91.00                33.12                 -3013.92              772                    26633.28               -34.5799999999999      -3048.5                34.4990673575129       34.95                  -52130.55              -189.040000000005       -0.0114734299516908    -0.00363947832860472
73131028    GHI  2013-10-28 -46.00                34.65                 -1593.90              726                    25092.28               52.9000000000001       -1541                  34.5623691460055       34.95                  -53671.55              -136.140000000005       0.0331890331890332     -0.00254298977069579
50131103    GHI  2013-11-03 -246.00               35.61                 -8760.06              480                    16771                  438.780000000001       -8321.28               34.9395833333333       34.95                  -61992.83              302.639999999995        0.0500886980226164     0.00485813815996565
87131116    GHI  2013-11-16 -320.00               34.16                 -10931.20             160                    5592                   -247.799999999999      -11179                 34.95                  34.95                  -73171.83              54.8399999999961        -0.0226690573770491    0.000748907467729941
94131117    GHI  2013-11-17 -92.00                32.65                 -3003.80              68                     2376.6                 -211.6                 -3215.4                34.95                  34.95                  -76387.23              -156.760000000004       -0.0704441041347626    -0.0020563955594135
81131123    GHI  2013-11-23 187.00                33.86                 6331.82               255                    8708.42                0                      0                      34.1506666666667       33.86                  -76387.23              -156.760000000004       NULL                   -0.0020563955594135
51131206    GHI  2013-12-06 85.00                 33.64                 2859.40               340                    11567.82               0                      0                      34.023                 33.64                  -76387.23              -156.760000000004       NULL                   -0.0020563955594135
75131216    GHI  2013-12-16 500.00                33.55                 16775.00              840                    28342.82               0                      0                      33.7414523809524       33.55                  -76387.23              -156.760000000004       NULL                   -0.0020563955594135
13131221    GHI  2013-12-21 72.00                 34.38                 2475.36               912                    30818.18               0                      0                      33.7918640350877       34.38                  -76387.23              -156.760000000004       NULL                   -0.0020563955594135
65131227    GHI  2013-12-27 376.00                33.49                 12592.24              1288                   43410.42               0                      0                      33.7037422360248       33.49                  -76387.23              -156.760000000004       NULL                   -0.0020563955594135
53140102    GHI  2014-01-02 396.00                35.52                 14065.92              1684                   57476.34               0                      0                      34.1308432304038       35.52                  -76387.23              -156.760000000004       NULL                   -0.0020563955594135
18140125    GHI  2014-01-25 -78.00                35.46                 -2765.88              1606                   54761.14               50.6800000000103       -2715.19999999999      34.0978455790785       35.52                  -79102.43              -106.079999999994       0.0183232822826769     -0.00134284685305072
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                    16263.28               262.480000000002       -4543.22               23.2001141226819       24.04                  -4543.22               262.480000000002        0.0546184738955828     0.0546184738955828
59130320    XYZ  2013-03-20 -20.00                23.59                 -471.80               681                    15792.48               0.99999999999892       -470.800000000001      23.1901321585903       24.04                  -5014.02               263.480000000001        0.00211954217888707    0.0499251539554716
35130321    XYZ  2013-03-21 275.00                24.83                 6828.25               956                    22620.73               0                      0                      23.6618514644351       24.83                  -5014.02               263.480000000001        NULL                   0.0499251539554716
98130413    XYZ  2013-04-13 243.00                24.25                 5892.75               1199                   28513.48               0                      0                      23.7810508757298       24.25                  -5014.02               263.480000000001        NULL                   0.0499251539554716
24130418    XYZ  2013-04-18 275.00                24.83                 6828.25               1474                   35341.73               0                      0                      23.976750339213        24.83                  -5014.02               263.480000000001        NULL                   0.0499251539554716
60130419    XYZ  2013-04-19 277.00                22.83                 6323.91               1751                   41665.64               0                      0                      23.7953398058252       22.83                  -5014.02               263.480000000001        NULL                   0.0499251539554716
83130428    XYZ  2013-04-28 142.00                23.13                 3284.46               1893                   44950.1                0                      0                      23.7454305335446       23.13                  -5014.02               263.480000000001        NULL                   0.0499251539554716
34130507    XYZ  2013-05-07 55.00                 23.43                 1288.65               1948                   46238.75               0                      0                      23.7365246406571       23.43                  -5014.02               263.480000000001        NULL                   0.0499251539554716
99130515    XYZ  2013-05-15 97.00                 25.13                 2437.61               2045                   48676.36               0                      0                      23.8026210268949       25.13                  -5014.02               263.480000000001        NULL                   0.0499251539554716
19130516    XYZ  2013-05-16 315.00                23.57                 7424.55               2360                   56100.91               0                      0                      23.7715720338983       23.57                  -5014.02               263.480000000001        NULL                   0.0499251539554716
30130519    XYZ  2013-05-19 467.00                23.15                 10811.05              2827                   66911.96               0                      0                      23.668892819243        23.15                  -5014.02               263.480000000001        NULL                   0.0499251539554716
29130602    XYZ  2013-06-02 114.00                24.29                 2769.06               2941                   69681.02               0                      0                      23.6929683781027       24.29                  -5014.02               263.480000000001        NULL                   0.0499251539554716
91130615    XYZ  2013-06-15 545.00                23.88                 13014.60              3486                   82695.62               0                      0                      23.7222088353414       23.88                  -5014.02               263.480000000001        NULL                   0.0499251539554716
23130619    XYZ  2013-06-19 296.00                23.46                 6944.16               3782                   89639.78               0                      0                      23.701686938128        23.46                  -5014.02               263.480000000001        NULL                   0.0499251539554716
47130619    XYZ  2013-06-19 413.00                25.18                 10399.34              4195                   100039.12              0                      0                      23.8472276519666       25.18                  -5014.02               263.480000000001        NULL                   0.0499251539554716
74130619    XYZ  2013-06-19 202.00                22.95                 4635.90               4397                   104675.02              0                      0                      23.8060086422561       22.95                  -5014.02               263.480000000001        NULL                   0.0499251539554716
55130630    XYZ  2013-06-30 -272.00               23.45                 -6378.40              4125                   98272.14               -24.4799999999905      -6402.87999999999      23.8235490909091       22.95                  -11416.9               239.000000000011        -0.00383795309168294   0.0205046371365584
14130705    XYZ  2013-07-05 -277.00               25.01                 -6927.77              3848                   91910.68               566.309999999994       -6361.46000000001      23.8853118503119       22.95                  -17778.36              805.310000000005        0.0817449193607747     0.0433342821950672
5130706     XYZ  2013-07-06 -170.00               23.46                 -3988.20              3678                   87939                  16.5200000000068       -3971.67999999999      23.9094616639478       22.95                  -21750.04              821.830000000012        0.00414221954766732    0.0364094778146433
58130722    XYZ  2013-07-22 -88.00                24.64                 -2168.32              3590                   85753.96               -16.720000000008       -2185.04000000001      23.8868969359331       22.95                  -23935.08              805.110000000004        -0.00771103896104264   0.0325425956712541
57130813    XYZ  2013-08-13 -163.00               23.65                 -3854.95              3427                   81714.79               -184.219999999998      -4039.17               23.8444091041727       22.95                  -27974.25              620.890000000005        -0.0477879090519977    0.0217131302731865
54130831    XYZ  2013-08-31 -61.00                23.23                 -1417.03              3366                   80235.54               -62.22                 -1479.25               23.8370588235294       22.95                  -29453.5               558.670000000005        -0.043908738699957     0.0186147819367945
11130906    XYZ  2013-09-06 -13.00                23.97                 -311.61               3353                   79920.29               -3.63999999999999      -315.25                23.835457798986        22.95                  -29768.75              555.030000000005        -0.0116812682519816    0.0183034568909287
27130906    XYZ  2013-09-06 -147.00               23.81                 -3500.07              3206                   76355.54               -64.6799999999998      -3564.75               23.8164504054897       22.95                  -33333.5               490.350000000005        -0.0184796304073918    0.0144971669398961
36130917    XYZ  2013-09-17 92.00                 24.60                 2263.20               3298                   78618.74               0                      0                      23.8383080654942       24.6                   -33333.5               490.350000000005        NULL                   0.0144971669398961
92130924    XYZ  2013-09-24 -248.00               24.58                 -6095.84              3050                   72465.54               -57.3599999999969      -6153.2                23.759193442623        24.6                   -39486.7               432.990000000008        -0.00940969579253999   0.010846527114815
42131011    XYZ  2013-10-11 -250.00               23.12                 -5780.00              2800                   66688.04               2.5                    -5777.5                23.8171571428571       24.6                   -45264.2               435.490000000008        0.000432525951557093   0.00952938630437117
22131012    XYZ  2013-10-12 596.00                23.16                 13803.36              3396                   80491.4                0                      0                      23.7018256772674       23.16                  -45264.2               435.490000000008        NULL                   0.00952938630437117
40131013    XYZ  2013-10-13 359.00                23.91                 8583.69               3755                   89075.09               0                      0                      23.7217283621838       23.91                  -45264.2               435.490000000008        NULL                   0.00952938630437117
1131019     XYZ  2013-10-19 424.00                25.13                 10655.12              4179                   99730.21               0                      0                      23.8646111509931       25.13                  -45264.2               435.490000000008        NULL                   0.00952938630437117
9131025     XYZ  2013-10-25 -153.00               24.31                 -3719.43              4026                   96209.92               199.140000000006       -3520.28999999999      23.8971485345256       25.13                  -48784.49              634.630000000015        0.0535404618449618     0.0128417907886667
62131027    XYZ  2013-10-27 -248.00               24.71                 -6128.08              3778                   90243.38               161.539999999992       -5966.54000000001      23.8865484383272       25.13                  -54751.03              796.170000000006        0.0263606219239944     0.0143332157156437
45131030    XYZ  2013-10-30 -350.00               23.36                 -8176.00              3428                   82027.48               -39.8999999999942      -8215.89999999999      23.9286697782964       25.13                  -62966.93              756.270000000012        -0.00488013698630066   0.0118680480578504
21131103    XYZ  2013-11-03 -326.00               23.24                 -7576.24              3102                   74480.58               29.3399999999911       -7546.90000000001      24.010502901354        25.13                  -70513.83              785.610000000003        0.0038726333907045     0.0110184596120251
97131125    XYZ  2013-11-25 -278.00               23.18                 -6444.04              2824                   67839.73               -196.809999999991      -6640.84999999999      24.0225672804533       25.13                  -77154.68              588.800000000012        -0.0305413994947256    0.0075736254667274
28131209    XYZ  2013-12-09 315.00                24.46                 7704.90               3139                   75544.63               0                      0                      24.0664638419879       24.46                  -77154.68              588.800000000012        NULL                   0.0075736254667274
79131223    XYZ  2013-12-23 -376.00               25.12                 -9445.12              2763                   66565.75               466.239999999996       -8978.88               24.0918385812523       24.46                  -86133.56              1055.04000000001        0.0493630573248404     0.0121006645364188
33140102    XYZ  2014-01-02 -196.00               22.98                 -4504.08              2567                   61939.87               -121.800000000005      -4625.88               24.1292832099727       24.46                  -90759.44              933.240000000003        -0.0270421484520712    0.0101779116937143
84140104    XYZ  2014-01-04 261.00                24.46                 6384.06               2828                   68323.93               0                      0                      24.1598055162659       24.46                  -90759.44              933.240000000003        NULL                   0.0101779116937143
64140109    XYZ  2014-01-09 -253.00               24.46                 -6188.38              2575                   62238.91               103.360000000011       -6085.01999999999      24.1704504854369       24.46                  -96844.46              1036.60000000001        0.0167022710305461     0.0105904043131533
78140112    XYZ  2014-01-12 332.00                24.28                 8060.96               2907                   70299.87               0                      0                      24.1829618163055       24.28                  -96844.46              1036.60000000001        NULL                   0.0105904043131533
80140126    XYZ  2014-01-26 404.00                24.23                 9788.92               3311                   80088.79               0                      0                      24.1887012987013       24.23                  -96844.46              1036.60000000001        NULL                   0.0105904043131533
82140131    XYZ  2014-01-31 548.00                23.65                 12960.20              3859                   93048.99               0                      0                      24.1122026431718       23.65                  -96844.46              1036.60000000001        NULL                   0.0105904043131533
66140203    XYZ  2014-02-03 459.00                23.70                 10878.30              4318                   103927.29              0                      0                      24.0683858267717       23.7                   -96844.46              1036.60000000001        NULL                   0.0105904043131533
72140204    XYZ  2014-02-04 -208.00               24.36                 -5066.88              4110                   98689.85               -170.560000000017      -5237.44000000002      24.0121289537713       23.7                   -102081.9              866.039999999997        -0.0336617405582956    0.00841240728080618
31140205    XYZ  2014-02-05 42.00                 24.39                 1024.38               4152                   99714.23               0                      0                      24.0159513487476       24.39                  -102081.9              866.039999999997        NULL                   0.00841240728080618

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] as Int) 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

c.trn = f.[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                3246000                -20000                 -8110000               1.623                  1.623                  -11370000              -28000                 -0.00247218788627936   -0.00246870040557221
107         GBP  -10000000.00          1.602                  USD  16020000.00           -8000000               -12816000              -42000                 -3246000               1.602                  1.602                  -14616000              -70000                 -0.0131086142322097    -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 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

ORDER BY

sym, tDate, trn

--Calculate the FIFO Values and store in temp table

SELECT

CAST([ID] as Int) 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

i.ID = f.[ID]

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 FIFO 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 #fifo table

SELECT

CAST([ID] as Int) as ID

,[QTY]

,[EB]

,[GM]

,[COGS]

,[UP]

,[LP]

,[COGSC]

,[GMC]

,[GMP]

,[CGMP]

INTO

#fifo

FROM

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

,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

#p2

INNER JOIN

#FIFO f

#p2.ID = f.[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.18000000000001       -2.66999999999999      0.89                   0.89                   -9.79                  22.66                   0.698305084745763      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                     80.64                  12.36                  -5.34                  1.06105263157895       1.15                   -21.36                 49.44                   0.698305084745762      0.698305084745763
ZYX          Coffee Mug  Plastic  8 oz  White 2017-02-22 -2          2.95                                    -5.90                                   74                     78.86                  4.12                   -1.78                  1.06567567567568       1.15                   -23.14                 53.56                   0.698305084745763      0.698305084745763

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 FIFO function

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 FIFO function

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