FIFO

Updated 2023-11-13 16:34:27.177000

Syntax

SELECT [westclintech].[wct].[FIFO](
  <@Qty, float,>
 ,<@Cost, float,>
 ,<@RV, nvarchar(4000),>
 ,<@Round, int,>
 ,<@RowNum, int,>
 ,<@Id, tinyint,>)

Description

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.

Arguments

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

@RV

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

{
    "columns": [
        {
            "field": "column 1",
            "minWidth": 169
        },
        {
            "field": "column 2",
            "minWidth": 421
        }
    ],
    "rows": [
        {
            "column 1": "'Q' , 'QTY'",
            "column 2": "Quantity on hand"
        },
        {
            "column 1": "'B' , 'EV' , 'EB'",
            "column 2": "Inventory value"
        },
        {
            "column 1": "'G' , 'GM'",
            "column 2": "Gross margin"
        },
        {
            "column 1": "'C' , 'COG' , 'COGS'",
            "column 2": "Cost of goods sold"
        },
        {
            "column 1": "'U' , 'UP'",
            "column 2": "(Average) Unit price"
        },
        {
            "column 1": "'L' , 'LP'",
            "column 2": "Last price"
        },
        {
            "column 1": "'CC' , 'COGC' , 'COGSC'",
            "column 2": "Cumulative cost of goods sold"
        },
        {
            "column 1": "'GC' , 'GMC'",
            "column 2": "Cumulative gross margin"
        },
        {
            "column 1": "'GP' , 'GMP'",
            "column 2": "Gross margin percentage"
        },
        {
            "column 1": "'CGP' , 'CGMP'",
            "column 2": "Cumulative gross margin percentage"
        }
    ]
}

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

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

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

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

Return Type

float

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.

Examples

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.

{"columns":[{"field":"trn","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"sym"},{"field":"tDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"qty","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"price_unit","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"price_extended","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"On Hand","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Value","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"trn":"68130223","sym":"ABC","tDate":"2013-02-23","qty":"238","price_unit":"12.78","price_extended":"3041.64","On Hand":"238","Value":"3041.64"},{"trn":"46130301","sym":"ABC","tDate":"2013-03-01","qty":"157","price_unit":"13.01","price_extended":"2042.57","On Hand":"395","Value":"5084.21"},{"trn":"3130308","sym":"ABC","tDate":"2013-03-08","qty":"-157","price_unit":"13.17","price_extended":"-2067.69","On Hand":"238","Value":"3077.75"},{"trn":"32130310","sym":"ABC","tDate":"2013-03-10","qty":"-63","price_unit":"12.61","price_extended":"-794.43","On Hand":"175","Value":"2272.61"},{"trn":"41130310","sym":"ABC","tDate":"2013-03-10","qty":"463","price_unit":"13.38","price_extended":"6194.94","On Hand":"638","Value":"8467.55"},{"trn":"26130320","sym":"ABC","tDate":"2013-03-20","qty":"-92","price_unit":"13.64","price_extended":"-1254.88","On Hand":"546","Value":"7274.77"},{"trn":"25130408","sym":"ABC","tDate":"2013-04-08","qty":"298","price_unit":"12.98","price_extended":"3868.04","On Hand":"844","Value":"11142.81"},{"trn":"48130430","sym":"ABC","tDate":"2013-04-30","qty":"229","price_unit":"13.32","price_extended":"3050.28","On Hand":"1073","Value":"14193.09"},{"trn":"90130430","sym":"ABC","tDate":"2013-04-30","qty":"-191","price_unit":"13.49","price_extended":"-2576.59","On Hand":"882","Value":"11668.22"},{"trn":"49130508","sym":"ABC","tDate":"2013-05-08","qty":"238","price_unit":"12.79","price_extended":"3044.02","On Hand":"1120","Value":"14712.24"},{"trn":"20130518","sym":"ABC","tDate":"2013-05-18","qty":"298","price_unit":"13.23","price_extended":"3942.54","On Hand":"1418","Value":"18654.78"},{"trn":"89130611","sym":"ABC","tDate":"2013-06-11","qty":"130","price_unit":"13.02","price_extended":"1692.60","On Hand":"1548","Value":"20347.38"},{"trn":"2130617","sym":"ABC","tDate":"2013-06-17","qty":"313","price_unit":"12.93","price_extended":"4047.09","On Hand":"1861","Value":"24394.47"},{"trn":"12130621","sym":"ABC","tDate":"2013-06-21","qty":"-326","price_unit":"12.73","price_extended":"-4149.98","On Hand":"1535","Value":"20032.59"},{"trn":"93130622","sym":"ABC","tDate":"2013-06-22","qty":"-227","price_unit":"13.47","price_extended":"-3057.69","On Hand":"1308","Value":"17074.53"},{"trn":"7130722","sym":"ABC","tDate":"2013-07-22","qty":"599","price_unit":"13.65","price_extended":"8176.35","On Hand":"1907","Value":"25250.88"},{"trn":"71130731","sym":"ABC","tDate":"2013-07-31","qty":"79","price_unit":"13.55","price_extended":"1070.45","On Hand":"1986","Value":"26321.33"},{"trn":"10130908","sym":"ABC","tDate":"2013-09-08","qty":"-386","price_unit":"13.65","price_extended":"-5268.90","On Hand":"1600","Value":"21244.02"},{"trn":"39130908","sym":"ABC","tDate":"2013-09-08","qty":"490","price_unit":"12.69","price_extended":"6218.10","On Hand":"2090","Value":"27462.12"},{"trn":"61130916","sym":"ABC","tDate":"2013-09-16","qty":"-202","price_unit":"12.94","price_extended":"-2613.88","On Hand":"1888","Value":"24869.30"},{"trn":"76131009","sym":"ABC","tDate":"2013-10-09","qty":"249","price_unit":"13.47","price_extended":"3354.03","On Hand":"2137","Value":"28223.33"},{"trn":"88131009","sym":"ABC","tDate":"2013-10-09","qty":"187","price_unit":"13.36","price_extended":"2498.32","On Hand":"2324","Value":"30721.65"},{"trn":"16131107","sym":"ABC","tDate":"2013-11-07","qty":"27","price_unit":"12.68","price_extended":"342.36","On Hand":"2351","Value":"31064.01"},{"trn":"86131114","sym":"ABC","tDate":"2013-11-14","qty":"-386","price_unit":"12.40","price_extended":"-4786.40","On Hand":"1965","Value":"25980.12"},{"trn":"8131231","sym":"ABC","tDate":"2013-12-31","qty":"-145","price_unit":"13.19","price_extended":"-1912.55","On Hand":"1820","Value":"24103.38"},{"trn":"15130307","sym":"GHI","tDate":"2013-03-07","qty":"559","price_unit":"35.21","price_extended":"19682.39","On Hand":"559","Value":"19682.39"},{"trn":"69130403","sym":"GHI","tDate":"2013-04-03","qty":"-151","price_unit":"33.16","price_extended":"-5007.16","On Hand":"408","Value":"14365.68"},{"trn":"56130419","sym":"GHI","tDate":"2013-04-19","qty":"416","price_unit":"34.46","price_extended":"14335.36","On Hand":"824","Value":"28701.04"},{"trn":"4130516","sym":"GHI","tDate":"2013-05-16","qty":"160","price_unit":"34.48","price_extended":"5516.80","On Hand":"984","Value":"34217.84"},{"trn":"43130521","sym":"GHI","tDate":"2013-05-21","qty":"-174","price_unit":"34.20","price_extended":"-5950.80","On Hand":"810","Value":"28091.30"},{"trn":"70130702","sym":"GHI","tDate":"2013-07-02","qty":"-162","price_unit":"35.48","price_extended":"-5747.76","On Hand":"648","Value":"22387.28"},{"trn":"96130716","sym":"GHI","tDate":"2013-07-16","qty":"-347","price_unit":"35.29","price_extended":"-12245.63","On Hand":"301","Value":"10375.66"},{"trn":"63130806","sym":"GHI","tDate":"2013-08-06","qty":"445","price_unit":"33.50","price_extended":"14907.50","On Hand":"746","Value":"25283.16"},{"trn":"77130825","sym":"GHI","tDate":"2013-08-25","qty":"272","price_unit":"33.86","price_extended":"9209.92","On Hand":"1018","Value":"34493.08"},{"trn":"95130908","sym":"GHI","tDate":"2013-09-08","qty":"103","price_unit":"35.42","price_extended":"3648.26","On Hand":"1121","Value":"38141.34"},{"trn":"6130924","sym":"GHI","tDate":"2013-09-24","qty":"328","price_unit":"34.95","price_extended":"11463.60","On Hand":"1449","Value":"49604.94"},{"trn":"17130924","sym":"GHI","tDate":"2013-09-24","qty":"-291","price_unit":"35.69","price_extended":"-10385.79","On Hand":"1158","Value":"39574.08"},{"trn":"85131002","sym":"GHI","tDate":"2013-10-02","qty":"-295","price_unit":"32.51","price_extended":"-9590.45","On Hand":"863","Value":"29681.78"},{"trn":"52131014","sym":"GHI","tDate":"2013-10-14","qty":"-91","price_unit":"33.12","price_extended":"-3013.92","On Hand":"772","Value":"26633.28"},{"trn":"73131028","sym":"GHI","tDate":"2013-10-28","qty":"-46","price_unit":"34.65","price_extended":"-1593.90","On Hand":"726","Value":"25092.28"},{"trn":"50131103","sym":"GHI","tDate":"2013-11-03","qty":"-246","price_unit":"35.61","price_extended":"-8760.06","On Hand":"480","Value":"16771.00"},{"trn":"87131116","sym":"GHI","tDate":"2013-11-16","qty":"-320","price_unit":"34.16","price_extended":"-10931.20","On Hand":"160","Value":"5592.00"},{"trn":"94131117","sym":"GHI","tDate":"2013-11-17","qty":"-92","price_unit":"32.65","price_extended":"-3003.80","On Hand":"68","Value":"2376.60"},{"trn":"81131123","sym":"GHI","tDate":"2013-11-23","qty":"187","price_unit":"33.86","price_extended":"6331.82","On Hand":"255","Value":"8708.42"},{"trn":"51131206","sym":"GHI","tDate":"2013-12-06","qty":"85","price_unit":"33.64","price_extended":"2859.40","On Hand":"340","Value":"11567.82"},{"trn":"75131216","sym":"GHI","tDate":"2013-12-16","qty":"500","price_unit":"33.55","price_extended":"16775.00","On Hand":"840","Value":"28342.82"},{"trn":"13131221","sym":"GHI","tDate":"2013-12-21","qty":"72","price_unit":"34.38","price_extended":"2475.36","On Hand":"912","Value":"30818.18"},{"trn":"65131227","sym":"GHI","tDate":"2013-12-27","qty":"376","price_unit":"33.49","price_extended":"12592.24","On Hand":"1288","Value":"43410.42"},{"trn":"53140102","sym":"GHI","tDate":"2014-01-02","qty":"396","price_unit":"35.52","price_extended":"14065.92","On Hand":"1684","Value":"57476.34"},{"trn":"18140125","sym":"GHI","tDate":"2014-01-25","qty":"-78","price_unit":"35.46","price_extended":"-2765.88","On Hand":"1606","Value":"54761.14"},{"trn":"37130220","sym":"XYZ","tDate":"2013-02-20","qty":"528","price_unit":"23.54","price_extended":"12429.12","On Hand":"528","Value":"12429.12"},{"trn":"44130227","sym":"XYZ","tDate":"2013-02-27","qty":"357","price_unit":"22.86","price_extended":"8161.02","On Hand":"885","Value":"20590.14"},{"trn":"67130302","sym":"XYZ","tDate":"2013-03-02","qty":"9","price_unit":"24.04","price_extended":"216.36","On Hand":"894","Value":"20806.50"},{"trn":"38130311","sym":"XYZ","tDate":"2013-03-11","qty":"-193","price_unit":"24.90","price_extended":"-4805.70","On Hand":"701","Value":"16263.28"},{"trn":"59130320","sym":"XYZ","tDate":"2013-03-20","qty":"-20","price_unit":"23.59","price_extended":"-471.80","On Hand":"681","Value":"15792.48"},{"trn":"35130321","sym":"XYZ","tDate":"2013-03-21","qty":"275","price_unit":"24.83","price_extended":"6828.25","On Hand":"956","Value":"22620.73"},{"trn":"98130413","sym":"XYZ","tDate":"2013-04-13","qty":"243","price_unit":"24.25","price_extended":"5892.75","On Hand":"1199","Value":"28513.48"},{"trn":"24130418","sym":"XYZ","tDate":"2013-04-18","qty":"275","price_unit":"24.83","price_extended":"6828.25","On Hand":"1474","Value":"35341.73"},{"trn":"60130419","sym":"XYZ","tDate":"2013-04-19","qty":"277","price_unit":"22.83","price_extended":"6323.91","On Hand":"1751","Value":"41665.64"},{"trn":"83130428","sym":"XYZ","tDate":"2013-04-28","qty":"142","price_unit":"23.13","price_extended":"3284.46","On Hand":"1893","Value":"44950.10"},{"trn":"34130507","sym":"XYZ","tDate":"2013-05-07","qty":"55","price_unit":"23.43","price_extended":"1288.65","On Hand":"1948","Value":"46238.75"},{"trn":"99130515","sym":"XYZ","tDate":"2013-05-15","qty":"97","price_unit":"25.13","price_extended":"2437.61","On Hand":"2045","Value":"48676.36"},{"trn":"19130516","sym":"XYZ","tDate":"2013-05-16","qty":"315","price_unit":"23.57","price_extended":"7424.55","On Hand":"2360","Value":"56100.91"},{"trn":"30130519","sym":"XYZ","tDate":"2013-05-19","qty":"467","price_unit":"23.15","price_extended":"10811.05","On Hand":"2827","Value":"66911.96"},{"trn":"29130602","sym":"XYZ","tDate":"2013-06-02","qty":"114","price_unit":"24.29","price_extended":"2769.06","On Hand":"2941","Value":"69681.02"},{"trn":"91130615","sym":"XYZ","tDate":"2013-06-15","qty":"545","price_unit":"23.88","price_extended":"13014.60","On Hand":"3486","Value":"82695.62"},{"trn":"23130619","sym":"XYZ","tDate":"2013-06-19","qty":"296","price_unit":"23.46","price_extended":"6944.16","On Hand":"3782","Value":"89639.78"},{"trn":"47130619","sym":"XYZ","tDate":"2013-06-19","qty":"413","price_unit":"25.18","price_extended":"10399.34","On Hand":"4195","Value":"100039.1"},{"trn":"74130619","sym":"XYZ","tDate":"2013-06-19","qty":"202","price_unit":"22.95","price_extended":"4635.90","On Hand":"4397","Value":"104675.0"},{"trn":"55130630","sym":"XYZ","tDate":"2013-06-30","qty":"-272","price_unit":"23.45","price_extended":"-6378.40","On Hand":"4125","Value":"98272.14"},{"trn":"14130705","sym":"XYZ","tDate":"2013-07-05","qty":"-277","price_unit":"25.01","price_extended":"-6927.77","On Hand":"3848","Value":"91910.68"},{"trn":"5130706","sym":"XYZ","tDate":"2013-07-06","qty":"-170","price_unit":"23.46","price_extended":"-3988.20","On Hand":"3678","Value":"87939.00"},{"trn":"58130722","sym":"XYZ","tDate":"2013-07-22","qty":"-88","price_unit":"24.64","price_extended":"-2168.32","On Hand":"3590","Value":"85753.96"},{"trn":"57130813","sym":"XYZ","tDate":"2013-08-13","qty":"-163","price_unit":"23.65","price_extended":"-3854.95","On Hand":"3427","Value":"81714.79"},{"trn":"54130831","sym":"XYZ","tDate":"2013-08-31","qty":"-61","price_unit":"23.23","price_extended":"-1417.03","On Hand":"3366","Value":"80235.54"},{"trn":"11130906","sym":"XYZ","tDate":"2013-09-06","qty":"-13","price_unit":"23.97","price_extended":"-311.61","On Hand":"3353","Value":"79920.29"},{"trn":"27130906","sym":"XYZ","tDate":"2013-09-06","qty":"-147","price_unit":"23.81","price_extended":"-3500.07","On Hand":"3206","Value":"76355.54"},{"trn":"36130917","sym":"XYZ","tDate":"2013-09-17","qty":"92","price_unit":"24.60","price_extended":"2263.20","On Hand":"3298","Value":"78618.74"},{"trn":"92130924","sym":"XYZ","tDate":"2013-09-24","qty":"-248","price_unit":"24.58","price_extended":"-6095.84","On Hand":"3050","Value":"72465.54"},{"trn":"42131011","sym":"XYZ","tDate":"2013-10-11","qty":"-250","price_unit":"23.12","price_extended":"-5780.00","On Hand":"2800","Value":"66688.04"},{"trn":"22131012","sym":"XYZ","tDate":"2013-10-12","qty":"596","price_unit":"23.16","price_extended":"13803.36","On Hand":"3396","Value":"80491.40"},{"trn":"40131013","sym":"XYZ","tDate":"2013-10-13","qty":"359","price_unit":"23.91","price_extended":"8583.69","On Hand":"3755","Value":"89075.09"},{"trn":"1131019","sym":"XYZ","tDate":"2013-10-19","qty":"424","price_unit":"25.13","price_extended":"10655.12","On Hand":"4179","Value":"99730.21"},{"trn":"9131025","sym":"XYZ","tDate":"2013-10-25","qty":"-153","price_unit":"24.31","price_extended":"-3719.43","On Hand":"4026","Value":"96209.92"},{"trn":"62131027","sym":"XYZ","tDate":"2013-10-27","qty":"-248","price_unit":"24.71","price_extended":"-6128.08","On Hand":"3778","Value":"90243.38"},{"trn":"45131030","sym":"XYZ","tDate":"2013-10-30","qty":"-350","price_unit":"23.36","price_extended":"-8176.00","On Hand":"3428","Value":"82027.48"},{"trn":"21131103","sym":"XYZ","tDate":"2013-11-03","qty":"-326","price_unit":"23.24","price_extended":"-7576.24","On Hand":"3102","Value":"74480.58"},{"trn":"97131125","sym":"XYZ","tDate":"2013-11-25","qty":"-278","price_unit":"23.18","price_extended":"-6444.04","On Hand":"2824","Value":"67839.73"},{"trn":"28131209","sym":"XYZ","tDate":"2013-12-09","qty":"315","price_unit":"24.46","price_extended":"7704.90","On Hand":"3139","Value":"75544.63"},{"trn":"79131223","sym":"XYZ","tDate":"2013-12-23","qty":"-376","price_unit":"25.12","price_extended":"-9445.12","On Hand":"2763","Value":"66565.75"},{"trn":"33140102","sym":"XYZ","tDate":"2014-01-02","qty":"-196","price_unit":"22.98","price_extended":"-4504.08","On Hand":"2567","Value":"61939.87"},{"trn":"84140104","sym":"XYZ","tDate":"2014-01-04","qty":"261","price_unit":"24.46","price_extended":"6384.06","On Hand":"2828","Value":"68323.93"},{"trn":"64140109","sym":"XYZ","tDate":"2014-01-09","qty":"-253","price_unit":"24.46","price_extended":"-6188.38","On Hand":"2575","Value":"62238.91"},{"trn":"78140112","sym":"XYZ","tDate":"2014-01-12","qty":"332","price_unit":"24.28","price_extended":"8060.96","On Hand":"2907","Value":"70299.87"},{"trn":"80140126","sym":"XYZ","tDate":"2014-01-26","qty":"404","price_unit":"24.23","price_extended":"9788.92","On Hand":"3311","Value":"80088.79"},{"trn":"82140131","sym":"XYZ","tDate":"2014-01-31","qty":"548","price_unit":"23.65","price_extended":"12960.20","On Hand":"3859","Value":"93048.99"},{"trn":"66140203","sym":"XYZ","tDate":"2014-02-03","qty":"459","price_unit":"23.70","price_extended":"10878.30","On Hand":"4318","Value":"103927.2"},{"trn":"72140204","sym":"XYZ","tDate":"2014-02-04","qty":"-208","price_unit":"24.36","price_extended":"-5066.88","On Hand":"4110","Value":"98689.85"},{"trn":"31140205","sym":"XYZ","tDate":"2014-02-05","qty":"42","price_unit":"24.39","price_extended":"1024.38","On Hand":"4152","Value":"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.

{
    "columns": [
        {
            "field": "sym"
        },
        {
            "field": "tDate"
        },
        {
            "field": "qty"
        },
        {
            "field": "price_extended"
        },
        {
            "field": "Gross Margin"
        },
        {
            "field": "COGS"
        }
    ],
    "rows": [
   {
        "sym": "ABC",
        "tDate": "2013-02-23",
        "qty": 238.0000,
        "price_extended": 3041.6400,
        "Gross Margin": 0.000000000000000e+000,
        "COGS": 0.000000000000000e+000
    },
    {
        "sym": "ABC",
        "tDate": "2013-03-01",
        "qty": 157.0000,
        "price_extended": 2042.5700,
        "Gross Margin": 0.000000000000000e+000,
        "COGS": 0.000000000000000e+000
    },
    {
        "sym": "ABC",
        "tDate": "2013-03-08",
        "qty": -157.0000,
        "price_extended": -2067.6900,
        "Gross Margin": 6.123000000000000e+001,
        "COGS": -2.006460000000000e+003
    },
    {
        "sym": "ABC",
        "tDate": "2013-03-10",
        "qty": -63.0000,
        "price_extended": -794.4300,
        "Gross Margin": -1.071000000000000e+001,
        "COGS": -8.051400000000000e+002
    },
    {
        "sym": "ABC",
        "tDate": "2013-03-10",
        "qty": 463.0000,
        "price_extended": 6194.9400,
        "Gross Margin": 0.000000000000000e+000,
        "COGS": 0.000000000000000e+000
    },
    {
        "sym": "ABC",
        "tDate": "2013-03-20",
        "qty": -92.0000,
        "price_extended": -1254.8800,
        "Gross Margin": 6.210000000000000e+001,
        "COGS": -1.192780000000000e+003
    },
    {
        "sym": "ABC",
        "tDate": "2013-04-08",
        "qty": 298.0000,
        "price_extended": 3868.0400,
        "Gross Margin": 0.000000000000000e+000,
        "COGS": 0.000000000000000e+000
    },
    {
        "sym": "ABC",
        "tDate": "2013-04-30",
        "qty": 229.0000,
        "price_extended": 3050.2800,
        "Gross Margin": -0.000000000000000e+000,
        "COGS": 0.000000000000000e+000
    },
    {
        "sym": "ABC",
        "tDate": "2013-04-30",
        "qty": -191.0000,
        "price_extended": -2576.5900,
        "Gross Margin": 5.172000000000000e+001,
        "COGS": -2.524870000000000e+003
    },
    {
        "sym": "ABC",
        "tDate": "2013-05-08",
        "qty": 238.0000,
        "price_extended": 3044.0200,
        "Gross Margin": 0.000000000000000e+000,
        "COGS": 0.000000000000000e+000
    },
    {
        "sym": "ABC",
        "tDate": "2013-05-18",
        "qty": 298.0000,
        "price_extended": 3942.5400,
        "Gross Margin": -0.000000000000000e+000,
        "COGS": 0.000000000000000e+000
    },
    {
        "sym": "ABC",
        "tDate": "2013-06-11",
        "qty": 130.0000,
        "price_extended": 1692.6000,
        "Gross Margin": -0.000000000000000e+000,
        "COGS": 0.000000000000000e+000
    },
    {
        "sym": "ABC",
        "tDate": "2013-06-17",
        "qty": 313.0000,
        "price_extended": 4047.0900,
        "Gross Margin": 0.000000000000000e+000,
        "COGS": 0.000000000000000e+000
    },
    {
        "sym": "ABC",
        "tDate": "2013-06-21",
        "qty": -326.0000,
        "price_extended": -4149.9800,
        "Gross Margin": -2.119000000000000e+002,
        "COGS": -4.3618800
    }
]
}

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.

{
    "columns": [
        {
            "field": "sym"
        },
        {
            "field": "tDate"
        },
        {
            "field": "qty"
        },
        {
            "field": "price_extended"
        },
        {
            "field": "Gross Margin"
        },
        {
            "field": "COGS"
        }
    ],
    "rows": [
            {
                "sym": "ABC",
                "tDate": "2013-02-23",
                "qty": 238.0000,
                "price_extended": 3041.6400,
                "Gross Margin": 0.000000000000000e+000,
                "COGS": 0.000000000000000e+000
            },
            {
                "sym": "ABC",
                "tDate": "2013-03-01",
                "qty": 157.0000,
                "price_extended": 2042.5700,
                "Gross Margin": 0.000000000000000e+000,
                "COGS": 0.000000000000000e+000
            },
            {
                "sym": "ABC",
                "tDate": "2013-03-08",
                "qty": -157.0000,
                "price_extended": -2067.6900,
                "Gross Margin": 6.123000000000000e+001,
                "COGS": -2.006460000000000e+003
            },
            {
                "sym": "ABC",
                "tDate": "2013-03-10",
                "qty": -63.0000,
                "price_extended": -794.4300,
                "Gross Margin": 5.052000000000000e+001,
                "COGS": -2.811600000000000e+003
            },
            {
                "sym": "ABC",
                "tDate": "2013-03-10",
                "qty": 463.0000,
                "price_extended": 6194.9400,
                "Gross Margin": 5.052000000000000e+001,
                "COGS": -2.811600000000000e+003
            },
            {
                "sym": "ABC",
                "tDate": "2013-03-20",
                "qty": -92.0000,
                "price_extended": -1254.8800,
                "Gross Margin": 1.126200000000000e+002,
                "COGS": -4.004380000000000e+003
            },
            {
                "sym": "ABC",
                "tDate": "2013-04-08",
                "qty": 298.0000,
                "price_extended": 3868.0400,
                "Gross Margin": 1.126200000000000e+002,
                "COGS": -4.004380000000000e+003
            },
            {
                "sym": "ABC",
                "tDate": "2013-04-30",
                "qty": 229.0000,
                "price_extended": 3050.2800,
                "Gross Margin": 1.126200000000000e+002,
                "COGS": -4.004380000000000e+003
            },
            {
                "sym": "ABC",
                "tDate": "2013-04-30",
                "qty": -191.0000,
                "price_extended": -2576.5900,
                "Gross Margin": 1.643400000000000e+002,
                "COGS": -6.529250000000000e+003
            },
            {
                "sym": "ABC",
                "tDate": "2013-05-08",
                "qty": 238.0000,
                "price_extended": 3044.0200,
                "Gross Margin": 1.643400000000000e+002,
                "COGS": -6.529250000000000e+003
            },
            {
                "sym": "ABC",
                "tDate": "2013-05-18",
                "qty": 298.0000,
                "price_extended": 3942.5400,
                "Gross Margin": 1.643400000000000e+002,
                "COGS": -6.529250000000000e+003
            },
            {
                "sym": "ABC",
                "tDate": "2013-06-11",
                "qty": 130.0000,
                "price_extended": 1692.6000,
                "Gross Margin": 1.643400000000000e+002,
                "COGS": -6.529250000000000e+003
            },
            {
                "sym": "ABC",
                "tDate": "2013-06-17",
                "qty": 313.0000,
                "price_extended": 4047.0900,
                "Gross Margin": 1.643400000000000e+002,
                "COGS": -6.529250000000000e+003
            },
            {
                "sym": "ABC",
                "tDate": "2013-06-21",
                "qty": -326.0000,
                "price_extended": -4149.9800,
                "Gross Margin": -4.756000000000000e+001,
                "COGS": -1.0891     
            }        
        ]        
     }

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.

{"columns":[{"field":"rn","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ccy"},{"field":"amt_ccy","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"tr"},{"field":"amt_ctr","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"BP Position","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"SD Position","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"P/L","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"rn":"1","ccy":"GBP","amt_ccy":"8000000.00","rate":"1.619","tr":"USD","amt_ctr":"-12952000.00","BP Position":"8000000.00","SD Position":"12952000.00","P/L":".00"},{"rn":"2","ccy":"GBP","amt_ccy":"-10000000.00","rate":"1.620","tr":"USD","amt_ctr":"16200000.00","BP Position":"-2000000.00","SD Position":"-3240000.00","P/L":"8000.00"},{"rn":"3","ccy":"GBP","amt_ccy":"-4000000.00","rate":"1.613","tr":"USD","amt_ctr":"6452000.00","BP Position":"-6000000.00","SD Position":"-9692000.00","P/L":".00"},{"rn":"4","ccy":"GBP","amt_ccy":"7000000.00","rate":"1.618","tr":"USD","amt_ctr":"-11326000.00","BP Position":"1000000.00","SD Position":"1618000.00","P/L":"-16000.00"},{"rn":"5","ccy":"GBP","amt_ccy":"6000000.00","rate":"1.623","tr":"USD","amt_ctr":"-9738000.00","BP Position":"7000000.00","SD Position":"11356000.00","P/L":".00"},{"rn":"6","ccy":"GBP","amt_ccy":"-5000000.00","rate":"1.618","tr":"USD","amt_ctr":"8090000.00","BP Position":"2000000.00","SD Position":"3246000.00","P/L":"-20000.00"},{"rn":"7","ccy":"GBP","amt_ccy":"-10000000.00","rate":"1.602","tr":"USD","amt_ctr":"16020000.00","BP Position":"-8000000.00","SD Position":"-12816000.00","P/L":"-42000.00"},{"rn":"8","ccy":"GBP","amt_ccy":"2000000.00","rate":"1.608","tr":"USD","amt_ctr":"-3216000.00","BP Position":"-6000000.00","SD Position":"-9612000.00","P/L":"-12000.00"},{"rn":"9","ccy":"GBP","amt_ccy":"-2000000.00","rate":"1.602","tr":"USD","amt_ctr":"3204000.00","BP Position":"-8000000.00","SD Position":"-12816000.00","P/L":".00"},{"rn":"10","ccy":"GBP","amt_ccy":"10000000.00","rate":"1.626","tr":"USD","amt_ctr":"-16260000.00","BP Position":"2000000.00","SD Position":"3252000.00","P/L":"-192000.00"}]}