FIFO
Updated: 31 Dec 2013
Use FIFO to calculate running FIFO (first in, first out) values in an ordered resultant table, without the need for a self-join. FIFO calculates balances for each value from the first value to the last value in the ordered group or partition. FIFO can return the quantity on-hand, the inventory value, the gross margin on sale, the gross margin percentage, the cost of goods sold, the average inventory price, the last inventory price, the cumulative cost of goods sold, the cumulative gross margin on sale, and the cumulative gross margin percentage.
FIFO supports both long and short inventory positions. In other words, if the quantity on hand falls below the zero, FIFO calculates from the last sale or withdrawal transaction, rather than from the last purchases or additions to inventory.
FIFO assumes that the quantity (i.e. the number of units) of the transaction and the monetary value of the transaction have the same sign. FIFO adds NULL values to the inventory at the last price.
FIFO requires monotonically ascending row numbers within a partition; otherwise the function returns an error message
FIFO resets all calculated values when the row number value is 1.
Syntax
SELECT [westclintech].[wct].[FIFO](
<@Qty, float,>
,<@Cost, float,>
,<@RV, nvarchar(4000),>
,<@Round, int,>
,<@RowNum, int,>
,<@Id, tinyint,>)
Arguments
@Qty
the number of units being added to or subtracted from inventory. @Qty is an expression of type float or of a type that can be implicitly converted to float.
@Cost
the value associated with @Qty. Additions to inventory should have a @Cost > 0; withdrawals from inventory should have a cost <= 0. If you are not interested in calculating the gross margin on sales or if the quantity on hand will never be less than zero, then just enter zero for the @Cost when the @Qty is less than zero. @Cost is an expression of type float or of a type that can be implicitly converted to float.
@RV
the calculated value returned by the function. Permissible values are:
|
'Q','QTY'
|
Quantity on hand
|
|
'B','EV','EB'
|
Inventory value
|
|
'G','GM'
|
Gross margin
|
|
'C','COG','COGS'
|
Cost of goods sold
|
|
'U','UP'
|
(Average) Unit price
|
|
'L','LP'
|
Last price
|
|
'CC','COGC','COGSC'
|
Cumulative cost of goods sold
|
|
'GC','GMC'
|
Cumulative gross margin
|
|
'GP','GMP'
|
Gross margin percentage
|
|
'CGP','CGMP'
|
Cumulative gross margin percentage
|
@Round
the number of decimals places to store the result. @Round is only used in the calculation of the gross margin. @Round is an expression of type int or of a type that can be implicitly converted to int.
@RowNum
the number of the row within the group for which the sum is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.
@Id
a unique identifier for the FIFO calculation. @Id allows you to specify multiple moving sums within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
Remarks
· If @Id is NULL then @Id = 0.
· To calculate LIFO values, use the LIFO function.
· To calculate weighted-average cost values, use the WAC function.
· If @RowNum is equal to 1, then cost of goods sold = 0, gross margin = 0, quantity on hand = @Qty, and inventory balance = @Cost.
· @RowNum must be in ascending order.
· There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem.
Example
In the following examples, we calculate 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
SELECT *
,wct.FIFO(
qty --@Qty
,price_extended --@Cost
,'QTY' --@RV
,2 --@Round
,ROW_NUMBER() OVER (PARTITION BY sym ORDER BY sym, tDate, trn) --@RowNum
,0 --@Id
) as [On Hand]
,wct.FIFO(
qty --@Qty
,price_extended --@Cost
,'EV' --@RV
,2 --@Round
,ROW_NUMBER() OVER (PARTITION BY sym ORDER BY sym, tDate, trn) --@RowNum
,1 --@Id
) as [Value]
FROM #c
ORDER BY sym, tDate, trn
DROP TABLE #c
This produces the following result.
trn sym tDate qty price_unit price_extended On Hand Value
----------- ---- ---------- ---- ---------- -------------- ------- --------
68130223 ABC 2013-02-23 238 12.78 3041.64 238 3041.64
46130301 ABC 2013-03-01 157 13.01 2042.57 395 5084.21
3130308 ABC 2013-03-08 -157 13.17 -2067.69 238 3077.75
32130310 ABC 2013-03-10 -63 12.61 -794.43 175 2272.61
41130310 ABC 2013-03-10 463 13.38 6194.94 638 8467.55
26130320 ABC 2013-03-20 -92 13.64 -1254.88 546 7274.77
25130408 ABC 2013-04-08 298 12.98 3868.04 844 11142.81
48130430 ABC 2013-04-30 229 13.32 3050.28 1073 14193.09
90130430 ABC 2013-04-30 -191 13.49 -2576.59 882 11668.22
49130508 ABC 2013-05-08 238 12.79 3044.02 1120 14712.24
20130518 ABC 2013-05-18 298 13.23 3942.54 1418 18654.78
89130611 ABC 2013-06-11 130 13.02 1692.60 1548 20347.38
2130617 ABC 2013-06-17 313 12.93 4047.09 1861 24394.47
12130621 ABC 2013-06-21 -326 12.73 -4149.98 1535 20032.59
93130622 ABC 2013-06-22 -227 13.47 -3057.69 1308 17074.53
7130722 ABC 2013-07-22 599 13.65 8176.35 1907 25250.88
71130731 ABC 2013-07-31 79 13.55 1070.45 1986 26321.33
10130908 ABC 2013-09-08 -386 13.65 -5268.90 1600 21244.02
39130908 ABC 2013-09-08 490 12.69 6218.10 2090 27462.12
61130916 ABC 2013-09-16 -202 12.94 -2613.88 1888 24869.30
76131009 ABC 2013-10-09 249 13.47 3354.03 2137 28223.33
88131009 ABC 2013-10-09 187 13.36 2498.32 2324 30721.65
16131107 ABC 2013-11-07 27 12.68 342.36 2351 31064.01
86131114 ABC 2013-11-14 -386 12.40 -4786.40 1965 25980.12
8131231 ABC 2013-12-31 -145 13.19 -1912.55 1820 24103.38
15130307 GHI 2013-03-07 559 35.21 19682.39 559 19682.39
69130403 GHI 2013-04-03 -151 33.16 -5007.16 408 14365.68
56130419 GHI 2013-04-19 416 34.46 14335.36 824 28701.04
4130516 GHI 2013-05-16 160 34.48 5516.80 984 34217.84
43130521 GHI 2013-05-21 -174 34.20 -5950.80 810 28091.30
70130702 GHI 2013-07-02 -162 35.48 -5747.76 648 22387.28
96130716 GHI 2013-07-16 -347 35.29 -12245.63 301 10375.66
63130806 GHI 2013-08-06 445 33.50 14907.50 746 25283.16
77130825 GHI 2013-08-25 272 33.86 9209.92 1018 34493.08
95130908 GHI 2013-09-08 103 35.42 3648.26 1121 38141.34
6130924 GHI 2013-09-24 328 34.95 11463.60 1449 49604.94
17130924 GHI 2013-09-24 -291 35.69 -10385.79 1158 39574.08
85131002 GHI 2013-10-02 -295 32.51 -9590.45 863 29681.78
52131014 GHI 2013-10-14 -91 33.12 -3013.92 772 26633.28
73131028 GHI 2013-10-28 -46 34.65 -1593.90 726 25092.28
50131103 GHI 2013-11-03 -246 35.61 -8760.06 480 16771.00
87131116 GHI 2013-11-16 -320 34.16 -10931.20 160 5592.00
94131117 GHI 2013-11-17 -92 32.65 -3003.80 68 2376.60
81131123 GHI 2013-11-23 187 33.86 6331.82 255 8708.42
51131206 GHI 2013-12-06 85 33.64 2859.40 340 11567.82
75131216 GHI 2013-12-16 500 33.55 16775.00 840 28342.82
13131221 GHI 2013-12-21 72 34.38 2475.36 912 30818.18
65131227 GHI 2013-12-27 376 33.49 12592.24 1288 43410.42
53140102 GHI 2014-01-02 396 35.52 14065.92 1684 57476.34
18140125 GHI 2014-01-25 -78 35.46 -2765.88 1606 54761.14
37130220 XYZ 2013-02-20 528 23.54 12429.12 528 12429.12
44130227 XYZ 2013-02-27 357 22.86 8161.02 885 20590.14
67130302 XYZ 2013-03-02 9 24.04 216.36 894 20806.50
38130311 XYZ 2013-03-11 -193 24.90 -4805.70 701 16263.28
59130320 XYZ 2013-03-20 -20 23.59 -471.80 681 15792.48
35130321 XYZ 2013-03-21 275 24.83 6828.25 956 22620.73
98130413 XYZ 2013-04-13 243 24.25 5892.75 1199 28513.48
24130418 XYZ 2013-04-18 275 24.83 6828.25 1474 35341.73
60130419 XYZ 2013-04-19 277 22.83 6323.91 1751 41665.64
83130428 XYZ 2013-04-28 142 23.13 3284.46 1893 44950.10
34130507 XYZ 2013-05-07 55 23.43 1288.65 1948 46238.75
99130515 XYZ 2013-05-15 97 25.13 2437.61 2045 48676.36
19130516 XYZ 2013-05-16 315 23.57 7424.55 2360 56100.91
30130519 XYZ 2013-05-19 467 23.15 10811.05 2827 66911.96
29130602 XYZ 2013-06-02 114 24.29 2769.06 2941 69681.02
91130615 XYZ 2013-06-15 545 23.88 13014.60 3486 82695.62
23130619 XYZ 2013-06-19 296 23.46 6944.16 3782 89639.78
47130619 XYZ 2013-06-19 413 25.18 10399.34 4195 100039.1
74130619 XYZ 2013-06-19 202 22.95 4635.90 4397 104675.0
55130630 XYZ 2013-06-30 -272 23.45 -6378.40 4125 98272.14
14130705 XYZ 2013-07-05 -277 25.01 -6927.77 3848 91910.68
5130706 XYZ 2013-07-06 -170 23.46 -3988.20 3678 87939.00
58130722 XYZ 2013-07-22 -88 24.64 -2168.32 3590 85753.96
57130813 XYZ 2013-08-13 -163 23.65 -3854.95 3427 81714.79
54130831 XYZ 2013-08-31 -61 23.23 -1417.03 3366 80235.54
11130906 XYZ 2013-09-06 -13 23.97 -311.61 3353 79920.29
27130906 XYZ 2013-09-06 -147 23.81 -3500.07 3206 76355.54
36130917 XYZ 2013-09-17 92 24.60 2263.20 3298 78618.74
92130924 XYZ 2013-09-24 -248 24.58 -6095.84 3050 72465.54
42131011 XYZ 2013-10-11 -250 23.12 -5780.00 2800 66688.04
22131012 XYZ 2013-10-12 596 23.16 13803.36 3396 80491.40
40131013 XYZ 2013-10-13 359 23.91 8583.69 3755 89075.09
1131019 XYZ 2013-10-19 424 25.13 10655.12 4179 99730.21
9131025 XYZ 2013-10-25 -153 24.31 -3719.43 4026 96209.92
62131027 XYZ 2013-10-27 -248 24.71 -6128.08 3778 90243.38
45131030 XYZ 2013-10-30 -350 23.36 -8176.00 3428 82027.48
21131103 XYZ 2013-11-03 -326 23.24 -7576.24 3102 74480.58
97131125 XYZ 2013-11-25 -278 23.18 -6444.04 2824 67839.73
28131209 XYZ 2013-12-09 315 24.46 7704.90 3139 75544.63
79131223 XYZ 2013-12-23 -376 25.12 -9445.12 2763 66565.75
33140102 XYZ 2014-01-02 -196 22.98 -4504.08 2567 61939.87
84140104 XYZ 2014-01-04 261 24.46 6384.06 2828 68323.93
64140109 XYZ 2014-01-09 -253 24.46 -6188.38 2575 62238.91
78140112 XYZ 2014-01-12 332 24.28 8060.96 2907 70299.87
80140126 XYZ 2014-01-26 404 24.23 9788.92 3311 80088.79
82140131 XYZ 2014-01-31 548 23.65 12960.20 3859 93048.99
66140203 XYZ 2014-02-03 459 23.70 10878.30 4318 103927.2
72140204 XYZ 2014-02-04 -208 24.36 -5066.88 4110 98689.85
31140205 XYZ 2014-02-05 42 24.39 1024.38 4152 99714.23
Using the #c table, we can generate a report showing the gross margin on sales and cost of goods sold
SELECT sym
,tDate
,qty
,price_extended
,wct.FIFO(qty,price_extended,'GM',2,ROW_NUMBER() OVER (PARTITION BY sym ORDER BY sym, tDate, trn),0) as [Gross Margin]
,wct.FIFO(qty,price_extended,'COGS',2,ROW_NUMBER() OVER (PARTITION BY sym ORDER BY sym, tDate, trn),1) as [COGS]
FROM #c
ORDER BY sym, tDate, trn
This produces the following result.
sym tDate qty price_unit price_extended Gross Margin COGS
---- ---------- ---- ---------- -------------- ------------ --------
ABC 2013-02-23 238 12.78 3041.64 .00 .00
ABC 2013-03-01 157 13.01 2042.57 .00 .00
ABC 2013-03-08 -157 13.17 -2067.69 61.23 -2006.46
ABC 2013-03-10 -63 12.61 -794.43 -10.71 -805.14
ABC 2013-03-10 463 13.38 6194.94 .00 .00
ABC 2013-03-20 -92 13.64 -1254.88 62.10 -1192.78
ABC 2013-04-08 298 12.98 3868.04 .00 .00
ABC 2013-04-30 229 13.32 3050.28 .00 .00
ABC 2013-04-30 -191 13.49 -2576.59 51.72 -2524.87
ABC 2013-05-08 238 12.79 3044.02 .00 .00
ABC 2013-05-18 298 13.23 3942.54 .00 .00
ABC 2013-06-11 130 13.02 1692.60 .00 .00
ABC 2013-06-17 313 12.93 4047.09 .00 .00
ABC 2013-06-21 -326 12.73 -4149.98 -211.90 -4361.88
ABC 2013-06-22 -227 13.47 -3057.69 99.63 -2958.06
ABC 2013-07-22 599 13.65 8176.35 .00 .00
ABC 2013-07-31 79 13.55 1070.45 .00 .00
ABC 2013-09-08 -386 13.65 -5268.90 191.59 -5077.31
ABC 2013-09-08 490 12.69 6218.10 .00 .00
ABC 2013-09-16 -202 12.94 -2613.88 21.06 -2592.82
ABC 2013-10-09 249 13.47 3354.03 .00 .00
ABC 2013-10-09 187 13.36 2498.32 .00 .00
ABC 2013-11-07 27 12.68 342.36 .00 .00
ABC 2013-11-14 -386 12.40 -4786.40 -297.49 -5083.89
ABC 2013-12-31 -145 13.19 -1912.55 35.81 -1876.74
GHI 2013-03-07 559 35.21 19682.39 .00 .00
GHI 2013-04-03 -151 33.16 -5007.16 -309.55 -5316.71
GHI 2013-04-19 416 34.46 14335.36 .00 .00
GHI 2013-05-16 160 34.48 5516.80 .00 .00
GHI 2013-05-21 -174 34.20 -5950.80 -175.74 -6126.54
GHI 2013-07-02 -162 35.48 -5747.76 43.74 -5704.02
GHI 2013-07-16 -347 35.29 -12245.63 234.01 -12011.6
GHI 2013-08-06 445 33.50 14907.50 .00 .00
GHI 2013-08-25 272 33.86 9209.92 .00 .00
GHI 2013-09-08 103 35.42 3648.26 .00 .00
GHI 2013-09-24 328 34.95 11463.60 .00 .00
GHI 2013-09-24 -291 35.69 -10385.79 354.93 -10030.8
GHI 2013-10-02 -295 32.51 -9590.45 -301.85 -9892.30
GHI 2013-10-14 -91 33.12 -3013.92 -34.58 -3048.50
GHI 2013-10-28 -46 34.65 -1593.90 52.90 -1541.00
GHI 2013-11-03 -246 35.61 -8760.06 438.78 -8321.28
GHI 2013-11-16 -320 34.16 -10931.20 -247.80 -11179.0
GHI 2013-11-17 -92 32.65 -3003.80 -211.60 -3215.40
GHI 2013-11-23 187 33.86 6331.82 .00 .00
GHI 2013-12-06 85 33.64 2859.40 .00 .00
GHI 2013-12-16 500 33.55 16775.00 .00 .00
GHI 2013-12-21 72 34.38 2475.36 .00 .00
GHI 2013-12-27 376 33.49 12592.24 .00 .00
GHI 2014-01-02 396 35.52 14065.92 .00 .00
GHI 2014-01-25 -78 35.46 -2765.88 50.68 -2715.20
XYZ 2013-02-20 528 23.54 12429.12 .00 .00
XYZ 2013-02-27 357 22.86 8161.02 .00 .00
XYZ 2013-03-02 9 24.04 216.36 .00 .00
XYZ 2013-03-11 -193 24.90 -4805.70 262.48 -4543.22
XYZ 2013-03-20 -20 23.59 -471.80 1.00 -470.80
XYZ 2013-03-21 275 24.83 6828.25 .00 .00
XYZ 2013-04-13 243 24.25 5892.75 .00 .00
XYZ 2013-04-18 275 24.83 6828.25 .00 .00
XYZ 2013-04-19 277 22.83 6323.91 .00 .00
XYZ 2013-04-28 142 23.13 3284.46 .00 .00
XYZ 2013-05-07 55 23.43 1288.65 .00 .00
XYZ 2013-05-15 97 25.13 2437.61 .00 .00
XYZ 2013-05-16 315 23.57 7424.55 .00 .00
XYZ 2013-05-19 467 23.15 10811.05 .00 .00
XYZ 2013-06-02 114 24.29 2769.06 .00 .00
XYZ 2013-06-15 545 23.88 13014.60 .00 .00
XYZ 2013-06-19 296 23.46 6944.16 .00 .00
XYZ 2013-06-19 413 25.18 10399.34 .00 .00
XYZ 2013-06-19 202 22.95 4635.90 .00 .00
XYZ 2013-06-30 -272 23.45 -6378.40 -24.48 -6402.88
XYZ 2013-07-05 -277 25.01 -6927.77 566.31 -6361.46
XYZ 2013-07-06 -170 23.46 -3988.20 16.52 -3971.68
XYZ 2013-07-22 -88 24.64 -2168.32 -16.72 -2185.04
XYZ 2013-08-13 -163 23.65 -3854.95 -184.22 -4039.17
XYZ 2013-08-31 -61 23.23 -1417.03 -62.22 -1479.25
XYZ 2013-09-06 -13 23.97 -311.61 -3.64 -315.25
XYZ 2013-09-06 -147 23.81 -3500.07 -64.68 -3564.75
XYZ 2013-09-17 92 24.60 2263.20 .00 .00
XYZ 2013-09-24 -248 24.58 -6095.84 -57.36 -6153.20
XYZ 2013-10-11 -250 23.12 -5780.00 2.50 -5777.50
XYZ 2013-10-12 596 23.16 13803.36 .00 .00
XYZ 2013-10-13 359 23.91 8583.69 .00 .00
XYZ 2013-10-19 424 25.13 10655.12 .00 .00
XYZ 2013-10-25 -153 24.31 -3719.43 199.14 -3520.29
XYZ 2013-10-27 -248 24.71 -6128.08 161.54 -5966.54
XYZ 2013-10-30 -350 23.36 -8176.00 -39.90 -8215.90
XYZ 2013-11-03 -326 23.24 -7576.24 29.34 -7546.90
XYZ 2013-11-25 -278 23.18 -6444.04 -196.81 -6640.85
XYZ 2013-12-09 315 24.46 7704.90 .00 .00
XYZ 2013-12-23 -376 25.12 -9445.12 466.24 -8978.88
XYZ 2014-01-02 -196 22.98 -4504.08 -121.80 -4625.88
XYZ 2014-01-04 261 24.46 6384.06 .00 .00
XYZ 2014-01-09 -253 24.46 -6188.38 103.36 -6085.02
XYZ 2014-01-12 332 24.28 8060.96 .00 .00
XYZ 2014-01-26 404 24.23 9788.92 .00 .00
XYZ 2014-01-31 548 23.65 12960.20 .00 .00
XYZ 2014-02-03 459 23.70 10878.30 .00 .00
XYZ 2014-02-04 -208 24.36 -5066.88 -170.56 -5237.44
XYZ 2014-02-05 42 24.39 1024.38 .00 .00
If we had wanted to calculate the cumulative values instead we only need the change the return value (@RV) passed into the function.
SELECT sym
,tDate
,qty
,price_extended
,wct.FIFO(qty,price_extended,'GMC',2,ROW_NUMBER() OVER (PARTITION BY sym ORDER BY sym, tDate, trn),0) as [Gross Margin]
,wct.FIFO(qty,price_extended,'COGSC',2,ROW_NUMBER() OVER (PARTITION BY sym ORDER BY sym, tDate, trn),1) as [COGS]
FROM #c
ORDER BY sym, tDate, trn
This produces the following result.
sym tDate qty price_unit price_extended Gross Margin COGS
---- ---------- ---- ---------- -------------- ------------ ------------
ABC 2013-02-23 238 12.78 3041.64 .00 .00
ABC 2013-03-01 157 13.01 2042.57 .00 .00
ABC 2013-03-08 -157 13.17 -2067.69 61.23 -2006.46
ABC 2013-03-10 -63 12.61 -794.43 50.52 -2811.60
ABC 2013-03-10 463 13.38 6194.94 50.52 -2811.60
ABC 2013-03-20 -92 13.64 -1254.88 112.62 -4004.38
ABC 2013-04-08 298 12.98 3868.04 112.62 -4004.38
ABC 2013-04-30 229 13.32 3050.28 112.62 -4004.38
ABC 2013-04-30 -191 13.49 -2576.59 164.34 -6529.25
ABC 2013-05-08 238 12.79 3044.02 164.34 -6529.25
ABC 2013-05-18 298 13.23 3942.54 164.34 -6529.25
ABC 2013-06-11 130 13.02 1692.60 164.34 -6529.25
ABC 2013-06-17 313 12.93 4047.09 164.34 -6529.25
ABC 2013-06-21 -326 12.73 -4149.98 -47.56 -10891.13
ABC 2013-06-22 -227 13.47 -3057.69 52.07 -13849.19
ABC 2013-07-22 599 13.65 8176.35 52.07 -13849.19
ABC 2013-07-31 79 13.55 1070.45 52.07 -13849.19
ABC 2013-09-08 -386 13.65 -5268.90 243.66 -18926.50
ABC 2013-09-08 490 12.69 6218.10 243.66 -18926.50
ABC 2013-09-16 -202 12.94 -2613.88 264.72 -21519.32
ABC 2013-10-09 249 13.47 3354.03 264.72 -21519.32
ABC 2013-10-09 187 13.36 2498.32 264.72 -21519.32
ABC 2013-11-07 27 12.68 342.36 264.72 -21519.32
ABC 2013-11-14 -386 12.40 -4786.40 -32.77 -26603.21
ABC 2013-12-31 -145 13.19 -1912.55 3.04 -28479.95
GHI 2013-03-07 559 35.21 19682.39 .00 .00
GHI 2013-04-03 -151 33.16 -5007.16 -309.55 -5316.71
GHI 2013-04-19 416 34.46 14335.36 -309.55 -5316.71
GHI 2013-05-16 160 34.48 5516.80 -309.55 -5316.71
GHI 2013-05-21 -174 34.20 -5950.80 -485.29 -11443.25
GHI 2013-07-02 -162 35.48 -5747.76 -441.55 -17147.27
GHI 2013-07-16 -347 35.29 -12245.63 -207.54 -29158.89
GHI 2013-08-06 445 33.50 14907.50 -207.54 -29158.89
GHI 2013-08-25 272 33.86 9209.92 -207.54 -29158.89
GHI 2013-09-08 103 35.42 3648.26 -207.54 -29158.89
GHI 2013-09-24 328 34.95 11463.60 -207.54 -29158.89
GHI 2013-09-24 -291 35.69 -10385.79 147.39 -39189.75
GHI 2013-10-02 -295 32.51 -9590.45 -154.46 -49082.05
GHI 2013-10-14 -91 33.12 -3013.92 -189.04 -52130.55
GHI 2013-10-28 -46 34.65 -1593.90 -136.14 -53671.55
GHI 2013-11-03 -246 35.61 -8760.06 302.64 -61992.83
GHI 2013-11-16 -320 34.16 -10931.20 54.84 -73171.83
GHI 2013-11-17 -92 32.65 -3003.80 -156.76 -76387.23
GHI 2013-11-23 187 33.86 6331.82 -156.76 -76387.23
GHI 2013-12-06 85 33.64 2859.40 -156.76 -76387.23
GHI 2013-12-16 500 33.55 16775.00 -156.76 -76387.23
GHI 2013-12-21 72 34.38 2475.36 -156.76 -76387.23
GHI 2013-12-27 376 33.49 12592.24 -156.76 -76387.23
GHI 2014-01-02 396 35.52 14065.92 -156.76 -76387.23
GHI 2014-01-25 -78 35.46 -2765.88 -106.08 -79102.43
XYZ 2013-02-20 528 23.54 12429.12 .00 .00
XYZ 2013-02-27 357 22.86 8161.02 .00 .00
XYZ 2013-03-02 9 24.04 216.36 .00 .00
XYZ 2013-03-11 -193 24.90 -4805.70 262.48 -4543.22
XYZ 2013-03-20 -20 23.59 -471.80 263.48 -5014.02
XYZ 2013-03-21 275 24.83 6828.25 263.48 -5014.02
XYZ 2013-04-13 243 24.25 5892.75 263.48 -5014.02
XYZ 2013-04-18 275 24.83 6828.25 263.48 -5014.02
XYZ 2013-04-19 277 22.83 6323.91 263.48 -5014.02
XYZ 2013-04-28 142 23.13 3284.46 263.48 -5014.02
XYZ 2013-05-07 55 23.43 1288.65 263.48 -5014.02
XYZ 2013-05-15 97 25.13 2437.61 263.48 -5014.02
XYZ 2013-05-16 315 23.57 7424.55 263.48 -5014.02
XYZ 2013-05-19 467 23.15 10811.05 263.48 -5014.02
XYZ 2013-06-02 114 24.29 2769.06 263.48 -5014.02
XYZ 2013-06-15 545 23.88 13014.60 263.48 -5014.02
XYZ 2013-06-19 296 23.46 6944.16 263.48 -5014.02
XYZ 2013-06-19 413 25.18 10399.34 263.48 -5014.02
XYZ 2013-06-19 202 22.95 4635.90 263.48 -5014.02
XYZ 2013-06-30 -272 23.45 -6378.40 239.00 -11416.90
XYZ 2013-07-05 -277 25.01 -6927.77 805.31 -17778.36
XYZ 2013-07-06 -170 23.46 -3988.20 821.83 -21750.04
XYZ 2013-07-22 -88 24.64 -2168.32 805.11 -23935.08
XYZ 2013-08-13 -163 23.65 -3854.95 620.89 -27974.25
XYZ 2013-08-31 -61 23.23 -1417.03 558.67 -29453.50
XYZ 2013-09-06 -13 23.97 -311.61 555.03 -29768.75
XYZ 2013-09-06 -147 23.81 -3500.07 490.35 -33333.50
XYZ 2013-09-17 92 24.60 2263.20 490.35 -33333.50
XYZ 2013-09-24 -248 24.58 -6095.84 432.99 -39486.70
XYZ 2013-10-11 -250 23.12 -5780.00 435.49 -45264.20
XYZ 2013-10-12 596 23.16 13803.36 435.49 -45264.20
XYZ 2013-10-13 359 23.91 8583.69 435.49 -45264.20
XYZ 2013-10-19 424 25.13 10655.12 435.49 -45264.20
XYZ 2013-10-25 -153 24.31 -3719.43 634.63 -48784.49
XYZ 2013-10-27 -248 24.71 -6128.08 796.17 -54751.03
XYZ 2013-10-30 -350 23.36 -8176.00 756.27 -62966.93
XYZ 2013-11-03 -326 23.24 -7576.24 785.61 -70513.83
XYZ 2013-11-25 -278 23.18 -6444.04 588.80 -77154.68
XYZ 2013-12-09 315 24.46 7704.90 588.80 -77154.68
XYZ 2013-12-23 -376 25.12 -9445.12 1055.04 -86133.56
XYZ 2014-01-02 -196 22.98 -4504.08 933.24 -90759.44
XYZ 2014-01-04 261 24.46 6384.06 933.24 -90759.44
XYZ 2014-01-09 -253 24.46 -6188.38 1036.60 -96844.46
XYZ 2014-01-12 332 24.28 8060.96 1036.60 -96844.46
XYZ 2014-01-26 404 24.23 9788.92 1036.60 -96844.46
XYZ 2014-01-31 548 23.65 12960.20 1036.60 -96844.46
XYZ 2014-02-03 459 23.70 10878.30 1036.60 -96844.46
XYZ 2014-02-04 -208 24.36 -5066.88 866.04 -102081.90
XYZ 2014-02-05 42 24.39 1024.38 866.04 -102081.90
In this example, we will look at a simple FX blotter. In FX trading, it is not at all unusual to switch from a long currency position to a short currency position during the course of a day. Additionally, if you are trading something like the US dollars against the Euro, if you are long US dollars, you are short the Euro. Thus, it’s critical to pay attention to the sign. We will create another table, #fx, and populate it with some data.
CREATE TABLE #fx (
rn int,
ccy char(3),
amt_ccy money,
rate float,
ctr char(3),
amt_ctr money,
PRIMARY KEY (rn)
)
--Populate the table with some data
INSERT INTO #fx VALUES (1,'GBP',8000000,1.619,'USD',-12952000)
INSERT INTO #fx VALUES (2,'GBP',-10000000,1.62,'USD',16200000)
INSERT INTO #fx VALUES (3,'GBP',-4000000,1.613,'USD',6452000)
INSERT INTO #fx VALUES (4,'GBP',7000000,1.618,'USD',-11326000)
INSERT INTO #fx VALUES (5,'GBP',6000000,1.623,'USD',-9738000)
INSERT INTO #fx VALUES (6,'GBP',-5000000,1.618,'USD',8090000)
INSERT INTO #fx VALUES (7,'GBP',-10000000,1.602,'USD',16020000)
INSERT INTO #fx VALUES (8,'GBP',2000000,1.608,'USD',-3216000)
INSERT INTO #fx VALUES (9,'GBP',-2000000,1.602,'USD',3204000)
INSERT INTO #fx VALUES (10,'GBP',10000000,1.626,'USD',-16260000)
In this SQL we will keep track of GBP position, the USD position, and the P/L.
SELECT rn
,ccy
,amt_ccy
,rate
,ctr
,amt_ctr
,wct.FIFO(amt_ccy,-amt_ctr,'QTY',2,ROW_NUMBER() OVER (ORDER BY rn), 0) as [GBP Position]
,wct.FIFO(amt_ccy,-amt_ctr,'EV',2,ROW_NUMBER() OVER (ORDER BY rn), 1) as [USD Position]
,wct.FIFO(amt_ccy,-amt_ctr,'GM',2,ROW_NUMBER() OVER (ORDER BY rn), 2) as [P/L]
FROM #fx
This produces the following result.
rn ccy amt_ccy rate ctr amt_ctr GBP Position USD Position P/L
---- ---- ------------ ----- ---- ------------ ------------ ------------ ------------
1 GBP 8000000.00 1.619 USD -12952000.00 8000000.00 12952000.00 .00
2 GBP -10000000.00 1.620 USD 16200000.00 -2000000.00 -3240000.00 8000.00
3 GBP -4000000.00 1.613 USD 6452000.00 -6000000.00 -9692000.00 .00
4 GBP 7000000.00 1.618 USD -11326000.00 1000000.00 1618000.00 -16000.00
5 GBP 6000000.00 1.623 USD -9738000.00 7000000.00 11356000.00 .00
6 GBP -5000000.00 1.618 USD 8090000.00 2000000.00 3246000.00 -20000.00
7 GBP -10000000.00 1.602 USD 16020000.00 -8000000.00 -12816000.00 -42000.00
8 GBP 2000000.00 1.608 USD -3216000.00 -6000000.00 -9612000.00 -12000.00
9 GBP -2000000.00 1.602 USD 3204000.00 -8000000.00 -12816000.00 .00
10 GBP 10000000.00 1.626 USD -16260000.00 2000000.00 3252000.00 -192000.00