Logo

WACtvf

Updated 2023-10-12 14:35:27.103000

Syntax

SELECT * FROM [westclintech].[wct].[WACtvf] (
   <@DataQuery, nvarchar(max),>)

Description

Use the SQL Server table-valued function WACtvf to calculate running weighted-average cost values in an ordered resultant table. WACtvf calculates balances for each value from the first value to the last value in the ordered group or partition. WACtvf 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.

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

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

WACtvf requires monotonically ascending row numbers within a partition.

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

Arguments

@DataQuery

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

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "38e5bf5a-a2c1-48c0-b344-243b9d0eb019", "colName": "ID", "colDatatype": "sql_variant", "colDesc": "unique transaction identifier"}, {"id": "297c1f6e-b495-4e1e-9b0e-c6bea404f428", "colName": "QTY", "colDatatype": "float", "colDesc": "inventory quantity"}, {"id": "db5368b5-7f7e-4374-a8ef-5e4b69f17b8c", "colName": "EB", "colDatatype": "float", "colDesc": "inventory value"}, {"id": "9c132339-d1cd-443b-be47-1f244aff0cde", "colName": "GM", "colDatatype": "float", "colDesc": "\tgross margin on sale"}, {"id": "2ac765fe-ff29-40e7-bf0c-f1b32cc95ab1", "colName": "COGS", "colDatatype": "float", "colDesc": "\tcost of goods sold"}, {"id": "2bb42961-aaa6-4235-aa81-113f24c4c53b", "colName": "UP", "colDatatype": "float", "colDesc": "average inventory price per unit"}, {"id": "9cb76a93-e758-4305-abbe-ac60101d3d24", "colName": "LP", "colDatatype": "float", "colDesc": "\tlast item-added price"}, {"id": "5a654e78-e059-42c6-a494-bc539ca889dd", "colName": "COGSC", "colDatatype": "float", "colDesc": "\tcumulative cost-of-goods-sold"}, {"id": "111c85d5-5bfe-4ad0-b03c-c72a7fc4902e", "colName": "GMC", "colDatatype": "float", "colDesc": "\tcumulative gross margin"}, {"id": "cb4a9d7b-6cdc-4b2a-b1ad-eda5d9b7e488", "colName": "GMP", "colDatatype": "float", "colDesc": "gross margin percentage"}, {"id": "9fa26e3e-c546-4360-b8f0-153fade3fd86", "colName": "CGMP", "colDatatype": "float", "colDesc": "cumulative gross margin percentage"}]}

Remarks

If the 3rd column (quantity) of the resultant table from @DataQuery contains NULL an error will be generated.

This function relies on the SQL Server ROW_NUMBER() function and expects results to be returned in ascending ROW_NUMBER() order within a PARTITION.

When ROW_NUMBER() = 1 all inventory values are re-initialized.

Examples

Example #1

In the following examples, we calculate weighted-average cost 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 WAC Values and store in temp table

SELECT CAST([ID] as Int) as ID,

       [QTY],

       [EB],

       [GM],

       [COGS],

       [UP],

       [LP],

       [COGSC],

       [GMC],

       [GMP],

       [CGMP]

INTO #WAC

FROM wct.WACtvf('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,

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

       w.[EB] as [Inventory Cost],

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

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

       w.[UP] as [Average Price],

       w.[LP] as [Last Price],

       w.[COGSC] as [Cumulative COGS],

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

       w.[GMP] as [GM Percentage],

       w.[CGMP] as [Cumulative GM Percentage]

FROM #c c

    INNER JOIN #WAC w

        ON c.trn = w.[ID]

ORDER BY c.sym,

         c.tDate,

         c.trn;

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":"Inventory-On-Hand","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Inventory Cost","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Gross Margin on Sales","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cost-of-Goods Sold","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Average Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Last Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative COGS","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative Gross Margin","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GM Percentage"},{"field":"Cumulative GM Percentage"}],"rows":[{"trn":"68130223","sym":"ABC","tDate":"2013-02-23","qty":"238.00","price_unit":"12.78","price_extended":"3041.64","Inventory-On-Hand":"238","Inventory Cost":"3041.64","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"12.78","Last Price":"12.78","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"46130301","sym":"ABC","tDate":"2013-03-01","qty":"157.00","price_unit":"13.01","price_extended":"2042.57","Inventory-On-Hand":"395","Inventory Cost":"5084.21","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"12.871417721519","Last Price":"13.01","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"3130308","sym":"ABC","tDate":"2013-03-08","qty":"-157.00","price_unit":"13.17","price_extended":"-2067.69","Inventory-On-Hand":"238","Inventory Cost":"3063.39741772152","Gross Margin on Sales":"46.8774177215187","Cost-of-Goods Sold":"-2020.81258227848","Average Price":"12.871417721519","Last Price":"13.01","Cumulative COGS":"-2020.81258227848","Cumulative Gross Margin":"46.8774177215187","GM Percentage":"0.0226713954807145","Cumulative GM Percentage":"0.0226713954807145"},{"trn":"32130310","sym":"ABC","tDate":"2013-03-10","qty":"-63.00","price_unit":"12.61","price_extended":"-794.43","Inventory-On-Hand":"175","Inventory Cost":"2252.49810126582","Gross Margin on Sales":"-16.469316455696","Cost-of-Goods Sold":"-810.899316455696","Average Price":"12.871417721519","Last Price":"13.01","Cumulative COGS":"-2831.71189873418","Cumulative Gross Margin":"30.4081012658227","GM Percentage":"-0.0207309850530519","Cumulative GM Percentage":"0.010624327863899"},{"trn":"41130310","sym":"ABC","tDate":"2013-03-10","qty":"463.00","price_unit":"13.38","price_extended":"6194.94","Inventory-On-Hand":"638","Inventory Cost":"8447.43810126582","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.2404985913257","Last Price":"13.38","Cumulative COGS":"-2831.71189873418","Cumulative Gross Margin":"30.4081012658227","GM Percentage":"NULL","Cumulative GM Percentage":"0.010624327863899"},{"trn":"26130320","sym":"ABC","tDate":"2013-03-20","qty":"-92.00","price_unit":"13.64","price_extended":"-1254.88","Inventory-On-Hand":"546","Inventory Cost":"7229.31223086385","Gross Margin on Sales":"36.7541295980318","Cost-of-Goods Sold":"-1218.12587040197","Average Price":"13.2404985913257","Last Price":"13.38","Cumulative COGS":"-4049.83776913615","Cumulative Gross Margin":"67.1622308638545","GM Percentage":"0.0292889595802242","Cumulative GM Percentage":"0.0163133910283834"},{"trn":"25130408","sym":"ABC","tDate":"2013-04-08","qty":"298.00","price_unit":"12.98","price_extended":"3868.04","Inventory-On-Hand":"844","Inventory Cost":"11097.3522308639","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1485216005496","Last Price":"12.98","Cumulative COGS":"-4049.83776913615","Cumulative Gross Margin":"67.1622308638545","GM Percentage":"NULL","Cumulative GM Percentage":"0.0163133910283834"},{"trn":"48130430","sym":"ABC","tDate":"2013-04-30","qty":"229.00","price_unit":"13.32","price_extended":"3050.28","Inventory-On-Hand":"1073","Inventory Cost":"14147.6322308639","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1851185748964","Last Price":"13.32","Cumulative COGS":"-4049.83776913615","Cumulative Gross Margin":"67.1622308638545","GM Percentage":"NULL","Cumulative GM Percentage":"0.0163133910283834"},{"trn":"90130430","sym":"ABC","tDate":"2013-04-30","qty":"-191.00","price_unit":"13.49","price_extended":"-2576.59","Inventory-On-Hand":"882","Inventory Cost":"11629.2745830586","Gross Margin on Sales":"58.2323521947837","Cost-of-Goods Sold":"-2518.35764780522","Average Price":"13.1851185748964","Last Price":"13.32","Cumulative COGS":"-6568.19541694136","Cumulative Gross Margin":"125.394583058638","GM Percentage":"0.0226005504153877","Cumulative GM Percentage":"0.0187335320894525"},{"trn":"49130508","sym":"ABC","tDate":"2013-05-08","qty":"238.00","price_unit":"12.79","price_extended":"3044.02","Inventory-On-Hand":"1120","Inventory Cost":"14673.2945830586","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1011558777309","Last Price":"12.79","Cumulative COGS":"-6568.19541694136","Cumulative Gross Margin":"125.394583058638","GM Percentage":"NULL","Cumulative GM Percentage":"0.0187335320894525"},{"trn":"20130518","sym":"ABC","tDate":"2013-05-18","qty":"298.00","price_unit":"13.23","price_extended":"3942.54","Inventory-On-Hand":"1418","Inventory Cost":"18615.8345830586","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1282331333277","Last Price":"13.23","Cumulative COGS":"-6568.19541694136","Cumulative Gross Margin":"125.394583058638","GM Percentage":"NULL","Cumulative GM Percentage":"0.0187335320894525"},{"trn":"89130611","sym":"ABC","tDate":"2013-06-11","qty":"130.00","price_unit":"13.02","price_extended":"1692.60","Inventory-On-Hand":"1548","Inventory Cost":"20308.4345830586","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1191437875056","Last Price":"13.02","Cumulative COGS":"-6568.19541694136","Cumulative Gross Margin":"125.394583058638","GM Percentage":"NULL","Cumulative GM Percentage":"0.0187335320894525"},{"trn":"2130617","sym":"ABC","tDate":"2013-06-17","qty":"313.00","price_unit":"12.93","price_extended":"4047.09","Inventory-On-Hand":"1861","Inventory Cost":"24355.5245830586","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.0873318554856","Last Price":"12.93","Cumulative COGS":"-6568.19541694136","Cumulative Gross Margin":"125.394583058638","GM Percentage":"NULL","Cumulative GM Percentage":"0.0187335320894525"},{"trn":"12130621","sym":"ABC","tDate":"2013-06-21","qty":"-326.00","price_unit":"12.73","price_extended":"-4149.98","Inventory-On-Hand":"1535","Inventory Cost":"20089.0543981703","Gross Margin on Sales":"-116.490184888295","Cost-of-Goods Sold":"-4266.47018488829","Average Price":"13.0873318554856","Last Price":"12.93","Cumulative COGS":"-10834.6656018297","Cumulative Gross Margin":"8.9043981703436","GM Percentage":"-0.028070059346863","Cumulative GM Percentage":"0.000821168505422439"},{"trn":"93130622","sym":"ABC","tDate":"2013-06-22","qty":"-227.00","price_unit":"13.47","price_extended":"-3057.69","Inventory-On-Hand":"1308","Inventory Cost":"17118.2300669751","Gross Margin on Sales":"86.8656688047754","Cost-of-Goods Sold":"-2970.82433119522","Average Price":"13.0873318554856","Last Price":"12.93","Cumulative COGS":"-13805.4899330249","Cumulative Gross Margin":"95.770066975119","GM Percentage":"0.0284089194145827","Cumulative GM Percentage":"0.0068893083774506"},{"trn":"7130722","sym":"ABC","tDate":"2013-07-22","qty":"599.00","price_unit":"13.65","price_extended":"8176.35","Inventory-On-Hand":"1907","Inventory Cost":"25294.5800669751","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.2640692537887","Last Price":"13.65","Cumulative COGS":"-13805.4899330249","Cumulative Gross Margin":"95.770066975119","GM Percentage":"NULL","Cumulative GM Percentage":"0.0068893083774506"},{"trn":"71130731","sym":"ABC","tDate":"2013-07-31","qty":"79.00","price_unit":"13.55","price_extended":"1070.45","Inventory-On-Hand":"1986","Inventory Cost":"26365.0300669751","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.2754431354356","Last Price":"13.55","Cumulative COGS":"-13805.4899330249","Cumulative Gross Margin":"95.770066975119","GM Percentage":"NULL","Cumulative GM Percentage":"0.0068893083774506"},{"trn":"10130908","sym":"ABC","tDate":"2013-09-08","qty":"-386.00","price_unit":"13.65","price_extended":"-5268.90","Inventory-On-Hand":"1600","Inventory Cost":"21240.709016697","Gross Margin on Sales":"144.578949721856","Cost-of-Goods Sold":"-5124.32105027814","Average Price":"13.2754431354356","Last Price":"13.55","Cumulative COGS":"-18929.810983303","Cumulative Gross Margin":"240.349016696975","GM Percentage":"0.0274400633380508","Cumulative GM Percentage":"0.0125376635717686"},{"trn":"39130908","sym":"ABC","tDate":"2013-09-08","qty":"490.00","price_unit":"12.69","price_extended":"6218.10","Inventory-On-Hand":"2090","Inventory Cost":"27458.809016697","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1381861323909","Last Price":"12.69","Cumulative COGS":"-18929.810983303","Cumulative Gross Margin":"240.349016696975","GM Percentage":"NULL","Cumulative GM Percentage":"0.0125376635717686"},{"trn":"61130916","sym":"ABC","tDate":"2013-09-16","qty":"-202.00","price_unit":"12.94","price_extended":"-2613.88","Inventory-On-Hand":"1888","Inventory Cost":"24804.895417954","Gross Margin on Sales":"-40.0335987429617","Cost-of-Goods Sold":"-2653.91359874296","Average Price":"13.1381861323909","Last Price":"12.69","Cumulative COGS":"-21583.724582046","Cumulative Gross Margin":"200.315417954013","GM Percentage":"-0.0153157753006877","Cumulative GM Percentage":"0.00919551276778839"},{"trn":"76131009","sym":"ABC","tDate":"2013-10-09","qty":"249.00","price_unit":"13.47","price_extended":"3354.03","Inventory-On-Hand":"2137","Inventory Cost":"28158.925417954","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1768485811671","Last Price":"13.47","Cumulative COGS":"-21583.724582046","Cumulative Gross Margin":"200.315417954013","GM Percentage":"NULL","Cumulative GM Percentage":"0.00919551276778839"},{"trn":"88131009","sym":"ABC","tDate":"2013-10-09","qty":"187.00","price_unit":"13.36","price_extended":"2498.32","Inventory-On-Hand":"2324","Inventory Cost":"30657.245417954","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1915858080697","Last Price":"13.36","Cumulative COGS":"-21583.724582046","Cumulative Gross Margin":"200.315417954013","GM Percentage":"NULL","Cumulative GM Percentage":"0.00919551276778839"},{"trn":"16131107","sym":"ABC","tDate":"2013-11-07","qty":"27.00","price_unit":"12.68","price_extended":"342.36","Inventory-On-Hand":"2351","Inventory Cost":"30999.605417954","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1857105138043","Last Price":"12.68","Cumulative COGS":"-21583.724582046","Cumulative Gross Margin":"200.315417954013","GM Percentage":"NULL","Cumulative GM Percentage":"0.00919551276778839"},{"trn":"86131114","sym":"ABC","tDate":"2013-11-14","qty":"-386.00","price_unit":"12.40","price_extended":"-4786.40","Inventory-On-Hand":"1965","Inventory Cost":"25909.9211596255","Gross Margin on Sales":"-303.284258328478","Cost-of-Goods Sold":"-5089.68425832848","Average Price":"13.1857105138043","Last Price":"12.68","Cumulative COGS":"-26673.4088403745","Cumulative Gross Margin":"-102.968840374465","GM Percentage":"-0.0633637511132539","Cumulative GM Percentage":"-0.00387531559035023"},{"trn":"8131231","sym":"ABC","tDate":"2013-12-31","qty":"-145.00","price_unit":"13.19","price_extended":"-1912.55","Inventory-On-Hand":"1820","Inventory Cost":"23997.9931351239","Gross Margin on Sales":"0.621975498369238","Cost-of-Goods Sold":"-1911.92802450163","Average Price":"13.1857105138043","Last Price":"12.68","Cumulative COGS":"-28585.3368648761","Cumulative Gross Margin":"-102.346864876096","GM Percentage":"0.000325207444704315","Cumulative GM Percentage":"-0.00359326267628841"},{"trn":"15130307","sym":"GHI","tDate":"2013-03-07","qty":"559.00","price_unit":"35.21","price_extended":"19682.39","Inventory-On-Hand":"559","Inventory Cost":"19682.39","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"35.21","Last Price":"35.21","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"69130403","sym":"GHI","tDate":"2013-04-03","qty":"-151.00","price_unit":"33.16","price_extended":"-5007.16","Inventory-On-Hand":"408","Inventory Cost":"14365.68","Gross Margin on Sales":"-309.549999999999","Cost-of-Goods Sold":"-5316.71","Average Price":"35.21","Last Price":"35.21","Cumulative COGS":"-5316.71","Cumulative Gross Margin":"-309.549999999999","GM Percentage":"-0.0618214716525933","Cumulative GM Percentage":"-0.0618214716525933"},{"trn":"56130419","sym":"GHI","tDate":"2013-04-19","qty":"416.00","price_unit":"34.46","price_extended":"14335.36","Inventory-On-Hand":"824","Inventory Cost":"28701.04","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"34.831359223301","Last Price":"34.46","Cumulative COGS":"-5316.71","Cumulative Gross Margin":"-309.549999999999","GM Percentage":"NULL","Cumulative GM Percentage":"-0.0618214716525933"},{"trn":"4130516","sym":"GHI","tDate":"2013-05-16","qty":"160.00","price_unit":"34.48","price_extended":"5516.80","Inventory-On-Hand":"984","Inventory Cost":"34217.84","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"34.7742276422764","Last Price":"34.48","Cumulative COGS":"-5316.71","Cumulative Gross Margin":"-309.549999999999","GM Percentage":"NULL","Cumulative GM Percentage":"-0.0618214716525933"},{"trn":"43130521","sym":"GHI","tDate":"2013-05-21","qty":"-174.00","price_unit":"34.20","price_extended":"-5950.80","Inventory-On-Hand":"810","Inventory Cost":"28167.1243902439","Gross Margin on Sales":"-99.9156097560981","Cost-of-Goods Sold":"-6050.7156097561","Average Price":"34.7742276422764","Last Price":"34.48","Cumulative COGS":"-11367.4256097561","Cumulative Gross Margin":"-409.465609756097","GM Percentage":"-0.0167902819379072","Cumulative GM Percentage":"-0.0373669560535079"},{"trn":"70130702","sym":"GHI","tDate":"2013-07-02","qty":"-162.00","price_unit":"35.48","price_extended":"-5747.76","Inventory-On-Hand":"648","Inventory Cost":"22533.6995121951","Gross Margin on Sales":"114.335121951221","Cost-of-Goods Sold":"-5633.42487804878","Average Price":"34.7742276422764","Last Price":"34.48","Cumulative COGS":"-17000.8504878049","Cumulative Gross Margin":"-295.130487804877","GM Percentage":"0.0198921183123896","Cumulative GM Percentage":"-0.0176664332818266"},{"trn":"96130716","sym":"GHI","tDate":"2013-07-16","qty":"-347.00","price_unit":"35.29","price_extended":"-12245.63","Inventory-On-Hand":"301","Inventory Cost":"10467.0425203252","Gross Margin on Sales":"178.973008130077","Cost-of-Goods Sold":"-12066.6569918699","Average Price":"34.7742276422764","Last Price":"34.48","Cumulative COGS":"-29067.5074796748","Cumulative Gross Margin":"-116.1574796748","GM Percentage":"0.0146152552486133","Cumulative GM Percentage":"-0.00401216107970095"},{"trn":"63130806","sym":"GHI","tDate":"2013-08-06","qty":"445.00","price_unit":"33.50","price_extended":"14907.50","Inventory-On-Hand":"746","Inventory Cost":"25374.5425203252","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"34.0141320647791","Last Price":"33.5","Cumulative COGS":"-29067.5074796748","Cumulative Gross Margin":"-116.1574796748","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00401216107970095"},{"trn":"77130825","sym":"GHI","tDate":"2013-08-25","qty":"272.00","price_unit":"33.86","price_extended":"9209.92","Inventory-On-Hand":"1018","Inventory Cost":"34584.4625203252","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"33.9729494305749","Last Price":"33.86","Cumulative COGS":"-29067.5074796748","Cumulative Gross Margin":"-116.1574796748","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00401216107970095"},{"trn":"95130908","sym":"GHI","tDate":"2013-09-08","qty":"103.00","price_unit":"35.42","price_extended":"3648.26","Inventory-On-Hand":"1121","Inventory Cost":"38232.7225203252","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"34.105907689853","Last Price":"35.42","Cumulative COGS":"-29067.5074796748","Cumulative Gross Margin":"-116.1574796748","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00401216107970095"},{"trn":"6130924","sym":"GHI","tDate":"2013-09-24","qty":"328.00","price_unit":"34.95","price_extended":"11463.60","Inventory-On-Hand":"1449","Inventory Cost":"49696.3225203252","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-29067.5074796748","Cumulative Gross Margin":"-116.1574796748","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00401216107970095"},{"trn":"17130924","sym":"GHI","tDate":"2013-09-24","qty":"-291.00","price_unit":"35.69","price_extended":"-10385.79","Inventory-On-Hand":"1158","Inventory Cost":"39715.9016415021","Gross Margin on Sales":"405.369121176926","Cost-of-Goods Sold":"-9980.42087882308","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-39047.9283584979","Cumulative Gross Margin":"289.211641502126","GM Percentage":"0.0390311301477235","Cumulative GM Percentage":"0.00735212680693425"},{"trn":"85131002","sym":"GHI","tDate":"2013-10-02","qty":"-295.00","price_unit":"32.51","price_extended":"-9590.45","Inventory-On-Hand":"863","Inventory Cost":"29598.2928468189","Gross Margin on Sales":"-527.158794683182","Cost-of-Goods Sold":"-10117.6087946832","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-49165.5371531811","Cumulative Gross Margin":"-237.947153181057","GM Percentage":"-0.0549670552146335","Cumulative GM Percentage":"-0.00486325104467759"},{"trn":"52131014","sym":"GHI","tDate":"2013-10-14","qty":"-91.00","price_unit":"33.12","price_extended":"-3013.92","Inventory-On-Hand":"772","Inventory Cost":"26477.2677610014","Gross Margin on Sales":"-107.105085817524","Cost-of-Goods Sold":"-3121.02508581752","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-52286.5622389986","Cumulative Gross Margin":"-345.052238998581","GM Percentage":"-0.0355368044996297","Cumulative GM Percentage":"-0.00664309218192888"},{"trn":"73131028","sym":"GHI","tDate":"2013-10-28","qty":"-46.00","price_unit":"34.65","price_extended":"-1593.90","Inventory-On-Hand":"726","Inventory Cost":"24899.6067286101","Gross Margin on Sales":"16.2389676087246","Cost-of-Goods Sold":"-1577.66103239128","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-53864.2232713899","Cumulative Gross Margin":"-328.813271389856","GM Percentage":"0.0101881972574971","Cumulative GM Percentage":"-0.00614197727055525"},{"trn":"50131103","sym":"GHI","tDate":"2013-11-03","qty":"-246.00","price_unit":"35.61","price_extended":"-8760.06","Inventory-On-Hand":"480","Inventory Cost":"16462.5499032133","Gross Margin on Sales":"323.003174603173","Cost-of-Goods Sold":"-8437.05682539683","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-62301.2800967867","Cumulative Gross Margin":"-5.81009678668352","GM Percentage":"0.0368722559666455","Cumulative GM Percentage":"-9.32667622009035E-05"},{"trn":"87131116","sym":"GHI","tDate":"2013-11-16","qty":"-320.00","price_unit":"34.16","price_extended":"-10931.20","Inventory-On-Hand":"160","Inventory Cost":"5487.51663440444","Gross Margin on Sales":"-43.8332688088758","Cost-of-Goods Sold":"-10975.0332688089","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-73276.3133655956","Cumulative Gross Margin":"-49.6433655955593","GM Percentage":"-0.00400992286380963","Cumulative GM Percentage":"-0.000677941050652164"},{"trn":"94131117","sym":"GHI","tDate":"2013-11-17","qty":"-92.00","price_unit":"32.65","price_extended":"-3003.80","Inventory-On-Hand":"68","Inventory Cost":"2332.19456962189","Gross Margin on Sales":"-151.522064782552","Cost-of-Goods Sold":"-3155.32206478255","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"-0.0504434598783382","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"81131123","sym":"GHI","tDate":"2013-11-23","qty":"187.00","price_unit":"33.86","price_extended":"6331.82","Inventory-On-Hand":"255","Inventory Cost":"8664.01456962189","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"33.9765277240074","Last Price":"33.86","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"51131206","sym":"GHI","tDate":"2013-12-06","qty":"85.00","price_unit":"33.64","price_extended":"2859.40","Inventory-On-Hand":"340","Inventory Cost":"11523.4145696219","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"33.8923957930055","Last Price":"33.64","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"75131216","sym":"GHI","tDate":"2013-12-16","qty":"500.00","price_unit":"33.55","price_extended":"16775.00","Inventory-On-Hand":"840","Inventory Cost":"28298.4145696219","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"33.6885887733594","Last Price":"33.55","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"13131221","sym":"GHI","tDate":"2013-12-21","qty":"72.00","price_unit":"34.38","price_extended":"2475.36","Inventory-On-Hand":"912","Inventory Cost":"30773.7745696219","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"33.7431738701994","Last Price":"34.38","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"65131227","sym":"GHI","tDate":"2013-12-27","qty":"376.00","price_unit":"33.49","price_extended":"12592.24","Inventory-On-Hand":"1288","Inventory Cost":"43366.0145696219","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"33.6692659702033","Last Price":"33.49","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"53140102","sym":"GHI","tDate":"2014-01-02","qty":"396.00","price_unit":"35.52","price_extended":"14065.92","Inventory-On-Hand":"1684","Inventory Cost":"57431.9345696219","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"34.1044742099892","Last Price":"35.52","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"18140125","sym":"GHI","tDate":"2014-01-25","qty":"-78.00","price_unit":"35.46","price_extended":"-2765.88","Inventory-On-Hand":"1606","Inventory Cost":"54771.7855812427","Gross Margin on Sales":"105.731011620842","Cost-of-Goods Sold":"-2660.14898837916","Average Price":"34.1044742099892","Last Price":"35.52","Cumulative COGS":"-79091.7844187573","Cumulative Gross Margin":"-95.4344187572697","GM Percentage":"0.038226897631438","Cumulative GM Percentage":"-0.00120808643383232"},{"trn":"37130220","sym":"XYZ","tDate":"2013-02-20","qty":"528.00","price_unit":"23.54","price_extended":"12429.12","Inventory-On-Hand":"528","Inventory Cost":"12429.12","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.54","Last Price":"23.54","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"44130227","sym":"XYZ","tDate":"2013-02-27","qty":"357.00","price_unit":"22.86","price_extended":"8161.02","Inventory-On-Hand":"885","Inventory Cost":"20590.14","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.2656949152542","Last Price":"22.86","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"67130302","sym":"XYZ","tDate":"2013-03-02","qty":"9.00","price_unit":"24.04","price_extended":"216.36","Inventory-On-Hand":"894","Inventory Cost":"20806.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.2734899328859","Last Price":"24.04","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"38130311","sym":"XYZ","tDate":"2013-03-11","qty":"-193.00","price_unit":"24.90","price_extended":"-4805.70","Inventory-On-Hand":"701","Inventory Cost":"16314.716442953","Gross Margin on Sales":"313.91644295302","Cost-of-Goods Sold":"-4491.78355704698","Average Price":"23.2734899328859","Last Price":"24.04","Cumulative COGS":"-4491.78355704698","Cumulative Gross Margin":"313.91644295302","GM Percentage":"0.0653216894423331","Cumulative GM Percentage":"0.0653216894423331"},{"trn":"59130320","sym":"XYZ","tDate":"2013-03-20","qty":"-20.00","price_unit":"23.59","price_extended":"-471.80","Inventory-On-Hand":"681","Inventory Cost":"15849.2466442953","Gross Margin on Sales":"6.33020134228246","Cost-of-Goods Sold":"-465.469798657718","Average Price":"23.2734899328859","Last Price":"24.04","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"0.0134171287458297","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"35130321","sym":"XYZ","tDate":"2013-03-21","qty":"275.00","price_unit":"24.83","price_extended":"6828.25","Inventory-On-Hand":"956","Inventory Cost":"22677.4966442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7212307994721","Last Price":"24.83","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"98130413","sym":"XYZ","tDate":"2013-04-13","qty":"243.00","price_unit":"24.25","price_extended":"5892.75","Inventory-On-Hand":"1199","Inventory Cost":"28570.2466442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.8283958668018","Last Price":"24.25","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"24130418","sym":"XYZ","tDate":"2013-04-18","qty":"275.00","price_unit":"24.83","price_extended":"6828.25","Inventory-On-Hand":"1474","Inventory Cost":"35398.4966442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"24.0152623095626","Last Price":"24.83","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"60130419","sym":"XYZ","tDate":"2013-04-19","qty":"277.00","price_unit":"22.83","price_extended":"6323.91","Inventory-On-Hand":"1751","Inventory Cost":"41722.4066442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.8277593628186","Last Price":"22.83","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"83130428","sym":"XYZ","tDate":"2013-04-28","qty":"142.00","price_unit":"23.13","price_extended":"3284.46","Inventory-On-Hand":"1893","Inventory Cost":"45006.8666442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7754181956129","Last Price":"23.13","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"34130507","sym":"XYZ","tDate":"2013-05-07","qty":"55.00","price_unit":"23.43","price_extended":"1288.65","Inventory-On-Hand":"1948","Inventory Cost":"46295.5166442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7656656284884","Last Price":"23.43","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"99130515","sym":"XYZ","tDate":"2013-05-15","qty":"97.00","price_unit":"25.13","price_extended":"2437.61","Inventory-On-Hand":"2045","Inventory Cost":"48733.1266442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.8303797771615","Last Price":"25.13","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"19130516","sym":"XYZ","tDate":"2013-05-16","qty":"315.00","price_unit":"23.57","price_extended":"7424.55","Inventory-On-Hand":"2360","Inventory Cost":"56157.6766442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7956256967353","Last Price":"23.57","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"30130519","sym":"XYZ","tDate":"2013-05-19","qty":"467.00","price_unit":"23.15","price_extended":"10811.05","Inventory-On-Hand":"2827","Inventory Cost":"66968.7266442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.6889729905537","Last Price":"23.15","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"29130602","sym":"XYZ","tDate":"2013-06-02","qty":"114.00","price_unit":"24.29","price_extended":"2769.06","Inventory-On-Hand":"2941","Inventory Cost":"69737.7866442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7122701952721","Last Price":"24.29","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"91130615","sym":"XYZ","tDate":"2013-06-15","qty":"545.00","price_unit":"23.88","price_extended":"13014.60","Inventory-On-Hand":"3486","Inventory Cost":"82752.3866442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7384930132804","Last Price":"23.88","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"23130619","sym":"XYZ","tDate":"2013-06-19","qty":"296.00","price_unit":"23.46","price_extended":"6944.16","Inventory-On-Hand":"3782","Inventory Cost":"89696.5466442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7166966272595","Last Price":"23.46","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"47130619","sym":"XYZ","tDate":"2013-06-19","qty":"413.00","price_unit":"25.18","price_extended":"10399.34","Inventory-On-Hand":"4195","Inventory Cost":"100095.886644295","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.8607596291526","Last Price":"25.18","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"74130619","sym":"XYZ","tDate":"2013-06-19","qty":"202.00","price_unit":"22.95","price_extended":"4635.90","Inventory-On-Hand":"4397","Inventory Cost":"104731.786644295","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"55130630","sym":"XYZ","tDate":"2013-06-30","qty":"-272.00","price_unit":"23.45","price_extended":"-6378.40","Inventory-On-Hand":"4125","Inventory Cost":"98253.0406885873","Gross Margin on Sales":"-100.345955708059","Cost-of-Goods Sold":"-6478.74595570806","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-11435.9993114128","Cumulative Gross Margin":"219.900688587244","GM Percentage":"-0.0157321515910038","Cumulative GM Percentage":"0.018866041111132"},{"trn":"14130705","sym":"XYZ","tDate":"2013-07-05","qty":"-277.00","price_unit":"25.01","price_extended":"-6927.77","Inventory-On-Hand":"3848","Inventory Cost":"91655.2001381052","Gross Margin on Sales":"329.929449517902","Cost-of-Goods Sold":"-6597.8405504821","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-18033.8398618949","Cumulative Gross Margin":"549.830138105146","GM Percentage":"0.047624192130787","Cumulative GM Percentage":"0.0295867359948356"},{"trn":"5130706","sym":"XYZ","tDate":"2013-07-06","qty":"-170.00","price_unit":"23.46","price_extended":"-3988.20","Inventory-On-Hand":"3678","Inventory Cost":"87605.9839157876","Gross Margin on Sales":"-61.0162223175403","Cost-of-Goods Sold":"-4049.21622231754","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-22083.0560842124","Cumulative Gross Margin":"488.813915787606","GM Percentage":"-0.0152991881845294","Cumulative GM Percentage":"0.0216558892013646"},{"trn":"58130722","sym":"XYZ","tDate":"2013-07-22","qty":"-88.00","price_unit":"24.64","price_extended":"-2168.32","Inventory-On-Hand":"3590","Inventory Cost":"85509.9190477644","Gross Margin on Sales":"72.2551319768031","Cost-of-Goods Sold":"-2096.0648680232","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-24179.1209522356","Cumulative Gross Margin":"561.069047764409","GM Percentage":"0.0333230943665156","Cumulative GM Percentage":"0.0226784453864101"},{"trn":"57130813","sym":"XYZ","tDate":"2013-08-13","qty":"-163.00","price_unit":"23.65","price_extended":"-3854.95","Inventory-On-Hand":"3427","Inventory Cost":"81627.4352581305","Gross Margin on Sales":"-27.5337896338724","Cost-of-Goods Sold":"-3882.48378963387","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-28061.6047418695","Cumulative Gross Margin":"533.535258130536","GM Percentage":"-0.00714245052046652","Cumulative GM Percentage":"0.0186582495532645"},{"trn":"54130831","sym":"XYZ","tDate":"2013-08-31","qty":"-61.00","price_unit":"23.23","price_extended":"-1417.03","Inventory-On-Hand":"3366","Inventory Cost":"80174.4812018872","Gross Margin on Sales":"-35.924056243354","Cost-of-Goods Sold":"-1452.95405624335","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-29514.5587981128","Cumulative Gross Margin":"497.611201887182","GM Percentage":"-0.0253516553942782","Cumulative GM Percentage":"0.0165803139822006"},{"trn":"11130906","sym":"XYZ","tDate":"2013-09-06","qty":"-13.00","price_unit":"23.97","price_extended":"-311.61","Inventory-On-Hand":"3353","Inventory Cost":"79864.8352554747","Gross Margin on Sales":"1.96405358747813","Cost-of-Goods Sold":"-309.645946412522","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-29824.2047445253","Cumulative Gross Margin":"499.57525547466","GM Percentage":"0.00630292220236236","Cumulative GM Percentage":"0.0164747025428446"},{"trn":"27130906","sym":"XYZ","tDate":"2013-09-06","qty":"-147.00","price_unit":"23.81","price_extended":"-3500.07","Inventory-On-Hand":"3206","Inventory Cost":"76363.4541691177","Gross Margin on Sales":"-1.31108635692317","Cost-of-Goods Sold":"-3501.38108635692","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-33325.5858308823","Cumulative Gross Margin":"498.264169117737","GM Percentage":"-0.000374588610205844","Cumulative GM Percentage":"0.0147311488525918"},{"trn":"36130917","sym":"XYZ","tDate":"2013-09-17","qty":"92.00","price_unit":"24.60","price_extended":"2263.20","Inventory-On-Hand":"3298","Inventory Cost":"78626.6541691177","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.8407077529162","Last Price":"24.6","Cumulative COGS":"-33325.5858308823","Cumulative Gross Margin":"498.264169117737","GM Percentage":"NULL","Cumulative GM Percentage":"0.0147311488525918"},{"trn":"92130924","sym":"XYZ","tDate":"2013-09-24","qty":"-248.00","price_unit":"24.58","price_extended":"-6095.84","Inventory-On-Hand":"3050","Inventory Cost":"72714.1586463945","Gross Margin on Sales":"183.344477276773","Cost-of-Goods Sold":"-5912.49552272323","Average Price":"23.8407077529162","Last Price":"24.6","Cumulative COGS":"-39238.0813536055","Cumulative Gross Margin":"681.60864639451","GM Percentage":"0.0300769832011294","Cumulative GM Percentage":"0.0170744974821826"},{"trn":"42131011","sym":"XYZ","tDate":"2013-10-11","qty":"-250.00","price_unit":"23.12","price_extended":"-5780.00","Inventory-On-Hand":"2800","Inventory Cost":"66753.9817081655","Gross Margin on Sales":"-180.176938229051","Cost-of-Goods Sold":"-5960.17693822905","Average Price":"23.8407077529162","Last Price":"24.6","Cumulative COGS":"-45198.2582918345","Cumulative Gross Margin":"501.431708165458","GM Percentage":"-0.0311724806624656","Cumulative GM Percentage":"0.0109723218727623"},{"trn":"22131012","sym":"XYZ","tDate":"2013-10-12","qty":"596.00","price_unit":"23.16","price_extended":"13803.36","Inventory-On-Hand":"3396","Inventory Cost":"80557.3417081655","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7212431413915","Last Price":"23.16","Cumulative COGS":"-45198.2582918345","Cumulative Gross Margin":"501.431708165458","GM Percentage":"NULL","Cumulative GM Percentage":"0.0109723218727623"},{"trn":"40131013","sym":"XYZ","tDate":"2013-10-13","qty":"359.00","price_unit":"23.91","price_extended":"8583.69","Inventory-On-Hand":"3755","Inventory Cost":"89141.0317081655","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7392894029735","Last Price":"23.91","Cumulative COGS":"-45198.2582918345","Cumulative Gross Margin":"501.431708165458","GM Percentage":"NULL","Cumulative GM Percentage":"0.0109723218727623"},{"trn":"1131019","sym":"XYZ","tDate":"2013-10-19","qty":"424.00","price_unit":"25.13","price_extended":"10655.12","Inventory-On-Hand":"4179","Inventory Cost":"99796.1517081655","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.8803904542152","Last Price":"25.13","Cumulative COGS":"-45198.2582918345","Cumulative Gross Margin":"501.431708165458","GM Percentage":"NULL","Cumulative GM Percentage":"0.0109723218727623"},{"trn":"9131025","sym":"XYZ","tDate":"2013-10-25","qty":"-153.00","price_unit":"24.31","price_extended":"-3719.43","Inventory-On-Hand":"4026","Inventory Cost":"96142.4519686705","Gross Margin on Sales":"65.7302605050713","Cost-of-Goods Sold":"-3653.69973949493","Average Price":"23.8803904542152","Last Price":"25.13","Cumulative COGS":"-48851.9580313295","Cumulative Gross Margin":"567.16196867053","GM Percentage":"0.0176721326937384","Cumulative GM Percentage":"0.0114765695680241"},{"trn":"62131027","sym":"XYZ","tDate":"2013-10-27","qty":"-248.00","price_unit":"24.71","price_extended":"-6128.08","Inventory-On-Hand":"3778","Inventory Cost":"90220.1151360252","Gross Margin on Sales":"205.743167354622","Cost-of-Goods Sold":"-5922.33683264538","Average Price":"23.8803904542152","Last Price":"25.13","Cumulative COGS":"-54774.2948639749","Cumulative Gross Margin":"772.905136025151","GM Percentage":"0.0335738383563239","Cumulative GM Percentage":"0.0139143851719826"},{"trn":"45131030","sym":"XYZ","tDate":"2013-10-30","qty":"-350.00","price_unit":"23.36","price_extended":"-8176.00","Inventory-On-Hand":"3428","Inventory Cost":"81861.9784770498","Gross Margin on Sales":"-182.136658975331","Cost-of-Goods Sold":"-8358.13665897533","Average Price":"23.8803904542152","Last Price":"25.13","Cumulative COGS":"-63132.4315229502","Cumulative Gross Margin":"590.768477049821","GM Percentage":"-0.0222769886222273","Cumulative GM Percentage":"0.00927085389700801"},{"trn":"21131103","sym":"XYZ","tDate":"2013-11-03","qty":"-326.00","price_unit":"23.24","price_extended":"-7576.24","Inventory-On-Hand":"3102","Inventory Cost":"74076.9711889757","Gross Margin on Sales":"-208.767288074165","Cost-of-Goods Sold":"-7785.00728807416","Average Price":"23.8803904542152","Last Price":"25.13","Cumulative COGS":"-70917.4388110243","Cumulative Gross Margin":"382.001188975656","GM Percentage":"-0.0275555272898119","Cumulative GM Percentage":"0.00535770251457313"},{"trn":"97131125","sym":"XYZ","tDate":"2013-11-25","qty":"-278.00","price_unit":"23.18","price_extended":"-6444.04","Inventory-On-Hand":"2824","Inventory Cost":"67438.2226427038","Gross Margin on Sales":"-194.708546271832","Cost-of-Goods Sold":"-6638.74854627183","Average Price":"23.8803904542152","Last Price":"25.13","Cumulative COGS":"-77556.1873572962","Cumulative Gross Margin":"187.292642703824","GM Percentage":"-0.0302152913811572","Cumulative GM Percentage":"0.00240911061228316"},{"trn":"28131209","sym":"XYZ","tDate":"2013-12-09","qty":"315.00","price_unit":"24.46","price_extended":"7704.90","Inventory-On-Hand":"3139","Inventory Cost":"75143.1226427038","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.9385545214093","Last Price":"24.46","Cumulative COGS":"-77556.1873572962","Cumulative Gross Margin":"187.292642703824","GM Percentage":"NULL","Cumulative GM Percentage":"0.00240911061228316"},{"trn":"79131223","sym":"XYZ","tDate":"2013-12-23","qty":"-376.00","price_unit":"25.12","price_extended":"-9445.12","Inventory-On-Hand":"2763","Inventory Cost":"66142.2261426539","Gross Margin on Sales":"444.2234999501","Cost-of-Goods Sold":"-9000.8965000499","Average Price":"23.9385545214093","Last Price":"24.46","Cumulative COGS":"-86557.0838573461","Cumulative Gross Margin":"631.516142653923","GM Percentage":"0.0470320652305211","Cumulative GM Percentage":"0.00724310451886971"},{"trn":"33140102","sym":"XYZ","tDate":"2014-01-02","qty":"-196.00","price_unit":"22.98","price_extended":"-4504.08","Inventory-On-Hand":"2567","Inventory Cost":"61450.2694564577","Gross Margin on Sales":"-187.876686196223","Cost-of-Goods Sold":"-4691.95668619622","Average Price":"23.9385545214093","Last Price":"24.46","Cumulative COGS":"-91249.0405435423","Cumulative Gross Margin":"443.6394564577","GM Percentage":"-0.0417125553267755","Cumulative GM Percentage":"0.00483833013123512"},{"trn":"84140104","sym":"XYZ","tDate":"2014-01-04","qty":"261.00","price_unit":"24.46","price_extended":"6384.06","Inventory-On-Hand":"2828","Inventory Cost":"67834.3294564577","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.9866794400487","Last Price":"24.46","Cumulative COGS":"-91249.0405435423","Cumulative Gross Margin":"443.6394564577","GM Percentage":"NULL","Cumulative GM Percentage":"0.00483833013123512"},{"trn":"64140109","sym":"XYZ","tDate":"2014-01-09","qty":"-253.00","price_unit":"24.46","price_extended":"-6188.38","Inventory-On-Hand":"2575","Inventory Cost":"61765.6995581254","Gross Margin on Sales":"119.750101667679","Cost-of-Goods Sold":"-6068.62989833232","Average Price":"23.9866794400487","Last Price":"24.46","Cumulative COGS":"-97317.6704418746","Cumulative Gross Margin":"563.389558125379","GM Percentage":"0.0193507996709444","Cumulative GM Percentage":"0.00575585877518469"},{"trn":"78140112","sym":"XYZ","tDate":"2014-01-12","qty":"332.00","price_unit":"24.28","price_extended":"8060.96","Inventory-On-Hand":"2907","Inventory Cost":"69826.6595581254","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"24.0201787265653","Last Price":"24.28","Cumulative COGS":"-97317.6704418746","Cumulative Gross Margin":"563.389558125379","GM Percentage":"NULL","Cumulative GM Percentage":"0.00575585877518469"},{"trn":"80140126","sym":"XYZ","tDate":"2014-01-26","qty":"404.00","price_unit":"24.23","price_extended":"9788.92","Inventory-On-Hand":"3311","Inventory Cost":"79615.5795581254","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"24.0457805974405","Last Price":"24.23","Cumulative COGS":"-97317.6704418746","Cumulative Gross Margin":"563.389558125379","GM Percentage":"NULL","Cumulative GM Percentage":"0.00575585877518469"},{"trn":"82140131","sym":"XYZ","tDate":"2014-01-31","qty":"548.00","price_unit":"23.65","price_extended":"12960.20","Inventory-On-Hand":"3859","Inventory Cost":"92575.7795581254","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.989577496275","Last Price":"23.65","Cumulative COGS":"-97317.6704418746","Cumulative Gross Margin":"563.389558125379","GM Percentage":"NULL","Cumulative GM Percentage":"0.00575585877518469"},{"trn":"66140203","sym":"XYZ","tDate":"2014-02-03","qty":"459.00","price_unit":"23.70","price_extended":"10878.30","Inventory-On-Hand":"4318","Inventory Cost":"103454.079558125","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.9587956364348","Last Price":"23.7","Cumulative COGS":"-97317.6704418746","Cumulative Gross Margin":"563.389558125379","GM Percentage":"NULL","Cumulative GM Percentage":"0.00575585877518469"},{"trn":"72140204","sym":"XYZ","tDate":"2014-02-04","qty":"-208.00","price_unit":"24.36","price_extended":"-5066.88","Inventory-On-Hand":"4110","Inventory Cost":"98470.6500657469","Gross Margin on Sales":"83.4505076215673","Cost-of-Goods Sold":"-4983.42949237843","Average Price":"23.9587956364348","Last Price":"23.7","Cumulative COGS":"-102301.099934253","Cumulative Gross Margin":"646.840065746946","GM Percentage":"0.0164698014599847","Cumulative GM Percentage":"0.0062831763874726"},{"trn":"31140205","sym":"XYZ","tDate":"2014-02-05","qty":"42.00","price_unit":"24.39","price_extended":"1024.38","Inventory-On-Hand":"4152","Inventory Cost":"99495.0300657469","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.9631575302859","Last Price":"24.39","Cumulative COGS":"-102301.099934253","Cumulative Gross Margin":"646.840065746946","GM Percentage":"NULL","Cumulative GM Percentage":"0.0062831763874726"}]}

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

SELECT CAST([ID] as Int) as ID,

       [QTY],

       [EB],

       [GM],

       [COGS],

       [UP],

       [LP],

       [COGSC],

       [GMC],

       [GMP],

       [CGMP]

INTO #WAC

FROM wct.WACtvf('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,

       w.[QTY] as [GBP Position],

       w.[EB] as [USD Position],

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

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

       w.[UP] as [Average Price],

       w.[LP] as [Last Price],

       w.[COGSC] as [Cumulative COGS],

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

       w.[GMP] as [GM Percentage],

       w.[CGMP] as [Cumulative GM Percentage]

FROM #fx c

    INNER JOIN #WAC w

        ON c.trn = w.[ID]

ORDER BY c.trn;

This produces the following result.

{"columns":[{"field":"trn","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":"ctr"},{"field":"amt_ctr","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GBP Position","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"USD Position","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"P/L","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cost-of-Goods Sold","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Average Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Last Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative COGS","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative P/L","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GM Percentage"},{"field":"Cumulative GM Percentage"}],"rows":[{"trn":"101","ccy":"GBP","amt_ccy":"8000000.00","rate":"1.619","ctr":"USD","amt_ctr":"-12952000.00","GBP Position":"8000000","USD Position":"12952000","P/L":"0","Cost-of-Goods Sold":"0","Average Price":"1.619","Last Price":"1.619","Cumulative COGS":"0","Cumulative P/L":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"102","ccy":"GBP","amt_ccy":"-10000000.00","rate":"1.62","ctr":"USD","amt_ctr":"16200000.00","GBP Position":"-2000000","USD Position":"-3240000","P/L":"8000","Cost-of-Goods Sold":"-12952000","Average Price":"1.62","Last Price":"1.62","Cumulative COGS":"-12952000","Cumulative P/L":"8000","GM Percentage":"0.000617283950617284","Cumulative GM Percentage":"0.000617283950617284"},{"trn":"103","ccy":"GBP","amt_ccy":"-4000000.00","rate":"1.613","ctr":"USD","amt_ctr":"6452000.00","GBP Position":"-6000000","USD Position":"-9692000","P/L":"0","Cost-of-Goods Sold":"0","Average Price":"1.61533333333333","Last Price":"1.613","Cumulative COGS":"-12952000","Cumulative P/L":"8000","GM Percentage":"NULL","Cumulative GM Percentage":"0.000617283950617284"},{"trn":"104","ccy":"GBP","amt_ccy":"7000000.00","rate":"1.618","ctr":"USD","amt_ctr":"-11326000.00","GBP Position":"1000000","USD Position":"1618000","P/L":"-16000","Cost-of-Goods Sold":"9692000","Average Price":"1.618","Last Price":"1.618","Cumulative COGS":"-3260000","Cumulative P/L":"-8000","GM Percentage":"0.00164812525751957","Cumulative GM Percentage":"-0.002460024600246"},{"trn":"105","ccy":"GBP","amt_ccy":"6000000.00","rate":"1.623","ctr":"USD","amt_ctr":"-9738000.00","GBP Position":"7000000","USD Position":"11356000","P/L":"0","Cost-of-Goods Sold":"0","Average Price":"1.62228571428571","Last Price":"1.623","Cumulative COGS":"-3260000","Cumulative P/L":"-8000","GM Percentage":"NULL","Cumulative GM Percentage":"-0.002460024600246"},{"trn":"106","ccy":"GBP","amt_ccy":"-5000000.00","rate":"1.618","ctr":"USD","amt_ctr":"8090000.00","GBP Position":"2000000","USD Position":"3244571.42857143","P/L":"-21428.5714285718","Cost-of-Goods Sold":"-8111428.57142857","Average Price":"1.62228571428571","Last Price":"1.623","Cumulative COGS":"-11371428.5714286","Cumulative P/L":"-29428.5714285718","GM Percentage":"-0.00264877273529936","Cumulative GM Percentage":"-0.00259465450789736"},{"trn":"107","ccy":"GBP","amt_ccy":"-10000000.00","rate":"1.602","ctr":"USD","amt_ctr":"16020000.00","GBP Position":"-8000000","USD Position":"-12816000","P/L":"-40571.4285714291","Cost-of-Goods Sold":"-3244571.42857143","Average Price":"1.602","Last Price":"1.602","Cumulative COGS":"-14616000","Cumulative P/L":"-70000.0000000009","GM Percentage":"-0.0126627429998218","Cumulative GM Percentage":"-0.00481231953801739"},{"trn":"108","ccy":"GBP","amt_ccy":"2000000.00","rate":"1.608","ctr":"USD","amt_ctr":"-3216000.00","GBP Position":"-6000000","USD Position":"-9612000","P/L":"-12000","Cost-of-Goods Sold":"3204000","Average Price":"1.602","Last Price":"1.602","Cumulative COGS":"-11412000","Cumulative P/L":"-82000.0000000009","GM Percentage":"0.00373134328358209","Cumulative GM Percentage":"-0.00723742277140344"},{"trn":"109","ccy":"GBP","amt_ccy":"-2000000.00","rate":"1.602","ctr":"USD","amt_ctr":"3204000.00","GBP Position":"-8000000","USD Position":"-12816000","P/L":"0","Cost-of-Goods Sold":"0","Average Price":"1.602","Last Price":"1.602","Cumulative COGS":"-11412000","Cumulative P/L":"-82000.0000000009","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00723742277140344"},{"trn":"110","ccy":"GBP","amt_ccy":"10000000.00","rate":"1.626","ctr":"USD","amt_ctr":"-16260000.00","GBP Position":"2000000","USD Position":"3252000","P/L":"-192000","Cost-of-Goods Sold":"12816000","Average Price":"1.626","Last Price":"1.626","Cumulative COGS":"1404000","Cumulative P/L":"-274000.000000001","GM Percentage":"0.014760147601476","Cumulative GM Percentage":"0.163289630512515"}]}

Example #3

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

ORDER BY sym,

         tDate,

         trn;

--Calculate the WAC Values and store in temp table

SELECT CAST([ID] as Int) as ID,

       [QTY],

       [EB],

       [GM],

       [COGS],

       [UP],

       [LP],

       [COGSC],

       [GMC],

       [GMP],

       [CGMP]

INTO #WAC

FROM wct.WACtvf('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,

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

       w.[EB] as [Inventory Cost],

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

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

       w.[UP] as [Average Price],

       w.[LP] as [Last Price],

       w.[COGSC] as [Cumulative COGS],

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

       w.[GMP] as [GM Percentage],

       w.[CGMP] as [Cumulative GM Percentage]

FROM #inv i

    INNER JOIN #WAC w

        ON i.ID = w.[ID]

    INNER JOIN #c c

        ON i.trn = c.trn

ORDER BY i.id;

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":"Inventory-On-Hand","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Inventory Cost","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Gross Margin on Sales","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cost-of-Goods Sold","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Average Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Last Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative COGS","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative Gross Margin","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GM Percentage"},{"field":"Cumulative GM Percentage"}],"rows":[{"trn":"68130223","sym":"ABC","tDate":"2013-02-23","qty":"238.00","price_unit":"12.78","price_extended":"3041.64","Inventory-On-Hand":"238","Inventory Cost":"3041.64","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"12.78","Last Price":"12.78","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"46130301","sym":"ABC","tDate":"2013-03-01","qty":"157.00","price_unit":"13.01","price_extended":"2042.57","Inventory-On-Hand":"395","Inventory Cost":"5084.21","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"12.871417721519","Last Price":"13.01","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"3130308","sym":"ABC","tDate":"2013-03-08","qty":"-157.00","price_unit":"13.17","price_extended":"-2067.69","Inventory-On-Hand":"238","Inventory Cost":"3063.39741772152","Gross Margin on Sales":"46.8774177215187","Cost-of-Goods Sold":"-2020.81258227848","Average Price":"12.871417721519","Last Price":"13.01","Cumulative COGS":"-2020.81258227848","Cumulative Gross Margin":"46.8774177215187","GM Percentage":"0.0226713954807145","Cumulative GM Percentage":"0.0226713954807145"},{"trn":"32130310","sym":"ABC","tDate":"2013-03-10","qty":"-63.00","price_unit":"12.61","price_extended":"-794.43","Inventory-On-Hand":"175","Inventory Cost":"2252.49810126582","Gross Margin on Sales":"-16.469316455696","Cost-of-Goods Sold":"-810.899316455696","Average Price":"12.871417721519","Last Price":"13.01","Cumulative COGS":"-2831.71189873418","Cumulative Gross Margin":"30.4081012658227","GM Percentage":"-0.0207309850530519","Cumulative GM Percentage":"0.010624327863899"},{"trn":"41130310","sym":"ABC","tDate":"2013-03-10","qty":"463.00","price_unit":"13.38","price_extended":"6194.94","Inventory-On-Hand":"638","Inventory Cost":"8447.43810126582","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.2404985913257","Last Price":"13.38","Cumulative COGS":"-2831.71189873418","Cumulative Gross Margin":"30.4081012658227","GM Percentage":"NULL","Cumulative GM Percentage":"0.010624327863899"},{"trn":"26130320","sym":"ABC","tDate":"2013-03-20","qty":"-92.00","price_unit":"13.64","price_extended":"-1254.88","Inventory-On-Hand":"546","Inventory Cost":"7229.31223086385","Gross Margin on Sales":"36.7541295980318","Cost-of-Goods Sold":"-1218.12587040197","Average Price":"13.2404985913257","Last Price":"13.38","Cumulative COGS":"-4049.83776913615","Cumulative Gross Margin":"67.1622308638545","GM Percentage":"0.0292889595802242","Cumulative GM Percentage":"0.0163133910283834"},{"trn":"25130408","sym":"ABC","tDate":"2013-04-08","qty":"298.00","price_unit":"12.98","price_extended":"3868.04","Inventory-On-Hand":"844","Inventory Cost":"11097.3522308639","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1485216005496","Last Price":"12.98","Cumulative COGS":"-4049.83776913615","Cumulative Gross Margin":"67.1622308638545","GM Percentage":"NULL","Cumulative GM Percentage":"0.0163133910283834"},{"trn":"48130430","sym":"ABC","tDate":"2013-04-30","qty":"229.00","price_unit":"13.32","price_extended":"3050.28","Inventory-On-Hand":"1073","Inventory Cost":"14147.6322308639","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1851185748964","Last Price":"13.32","Cumulative COGS":"-4049.83776913615","Cumulative Gross Margin":"67.1622308638545","GM Percentage":"NULL","Cumulative GM Percentage":"0.0163133910283834"},{"trn":"90130430","sym":"ABC","tDate":"2013-04-30","qty":"-191.00","price_unit":"13.49","price_extended":"-2576.59","Inventory-On-Hand":"882","Inventory Cost":"11629.2745830586","Gross Margin on Sales":"58.2323521947837","Cost-of-Goods Sold":"-2518.35764780522","Average Price":"13.1851185748964","Last Price":"13.32","Cumulative COGS":"-6568.19541694136","Cumulative Gross Margin":"125.394583058638","GM Percentage":"0.0226005504153877","Cumulative GM Percentage":"0.0187335320894525"},{"trn":"49130508","sym":"ABC","tDate":"2013-05-08","qty":"238.00","price_unit":"12.79","price_extended":"3044.02","Inventory-On-Hand":"1120","Inventory Cost":"14673.2945830586","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1011558777309","Last Price":"12.79","Cumulative COGS":"-6568.19541694136","Cumulative Gross Margin":"125.394583058638","GM Percentage":"NULL","Cumulative GM Percentage":"0.0187335320894525"},{"trn":"20130518","sym":"ABC","tDate":"2013-05-18","qty":"298.00","price_unit":"13.23","price_extended":"3942.54","Inventory-On-Hand":"1418","Inventory Cost":"18615.8345830586","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1282331333277","Last Price":"13.23","Cumulative COGS":"-6568.19541694136","Cumulative Gross Margin":"125.394583058638","GM Percentage":"NULL","Cumulative GM Percentage":"0.0187335320894525"},{"trn":"89130611","sym":"ABC","tDate":"2013-06-11","qty":"130.00","price_unit":"13.02","price_extended":"1692.60","Inventory-On-Hand":"1548","Inventory Cost":"20308.4345830586","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1191437875056","Last Price":"13.02","Cumulative COGS":"-6568.19541694136","Cumulative Gross Margin":"125.394583058638","GM Percentage":"NULL","Cumulative GM Percentage":"0.0187335320894525"},{"trn":"2130617","sym":"ABC","tDate":"2013-06-17","qty":"313.00","price_unit":"12.93","price_extended":"4047.09","Inventory-On-Hand":"1861","Inventory Cost":"24355.5245830586","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.0873318554856","Last Price":"12.93","Cumulative COGS":"-6568.19541694136","Cumulative Gross Margin":"125.394583058638","GM Percentage":"NULL","Cumulative GM Percentage":"0.0187335320894525"},{"trn":"12130621","sym":"ABC","tDate":"2013-06-21","qty":"-326.00","price_unit":"12.73","price_extended":"-4149.98","Inventory-On-Hand":"1535","Inventory Cost":"20089.0543981703","Gross Margin on Sales":"-116.490184888295","Cost-of-Goods Sold":"-4266.47018488829","Average Price":"13.0873318554856","Last Price":"12.93","Cumulative COGS":"-10834.6656018297","Cumulative Gross Margin":"8.9043981703436","GM Percentage":"-0.028070059346863","Cumulative GM Percentage":"0.000821168505422439"},{"trn":"93130622","sym":"ABC","tDate":"2013-06-22","qty":"-227.00","price_unit":"13.47","price_extended":"-3057.69","Inventory-On-Hand":"1308","Inventory Cost":"17118.2300669751","Gross Margin on Sales":"86.8656688047754","Cost-of-Goods Sold":"-2970.82433119522","Average Price":"13.0873318554856","Last Price":"12.93","Cumulative COGS":"-13805.4899330249","Cumulative Gross Margin":"95.770066975119","GM Percentage":"0.0284089194145827","Cumulative GM Percentage":"0.0068893083774506"},{"trn":"7130722","sym":"ABC","tDate":"2013-07-22","qty":"599.00","price_unit":"13.65","price_extended":"8176.35","Inventory-On-Hand":"1907","Inventory Cost":"25294.5800669751","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.2640692537887","Last Price":"13.65","Cumulative COGS":"-13805.4899330249","Cumulative Gross Margin":"95.770066975119","GM Percentage":"NULL","Cumulative GM Percentage":"0.0068893083774506"},{"trn":"71130731","sym":"ABC","tDate":"2013-07-31","qty":"79.00","price_unit":"13.55","price_extended":"1070.45","Inventory-On-Hand":"1986","Inventory Cost":"26365.0300669751","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.2754431354356","Last Price":"13.55","Cumulative COGS":"-13805.4899330249","Cumulative Gross Margin":"95.770066975119","GM Percentage":"NULL","Cumulative GM Percentage":"0.0068893083774506"},{"trn":"10130908","sym":"ABC","tDate":"2013-09-08","qty":"-386.00","price_unit":"13.65","price_extended":"-5268.90","Inventory-On-Hand":"1600","Inventory Cost":"21240.709016697","Gross Margin on Sales":"144.578949721856","Cost-of-Goods Sold":"-5124.32105027814","Average Price":"13.2754431354356","Last Price":"13.55","Cumulative COGS":"-18929.810983303","Cumulative Gross Margin":"240.349016696975","GM Percentage":"0.0274400633380508","Cumulative GM Percentage":"0.0125376635717686"},{"trn":"39130908","sym":"ABC","tDate":"2013-09-08","qty":"490.00","price_unit":"12.69","price_extended":"6218.10","Inventory-On-Hand":"2090","Inventory Cost":"27458.809016697","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1381861323909","Last Price":"12.69","Cumulative COGS":"-18929.810983303","Cumulative Gross Margin":"240.349016696975","GM Percentage":"NULL","Cumulative GM Percentage":"0.0125376635717686"},{"trn":"61130916","sym":"ABC","tDate":"2013-09-16","qty":"-202.00","price_unit":"12.94","price_extended":"-2613.88","Inventory-On-Hand":"1888","Inventory Cost":"24804.895417954","Gross Margin on Sales":"-40.0335987429617","Cost-of-Goods Sold":"-2653.91359874296","Average Price":"13.1381861323909","Last Price":"12.69","Cumulative COGS":"-21583.724582046","Cumulative Gross Margin":"200.315417954013","GM Percentage":"-0.0153157753006877","Cumulative GM Percentage":"0.00919551276778839"},{"trn":"76131009","sym":"ABC","tDate":"2013-10-09","qty":"249.00","price_unit":"13.47","price_extended":"3354.03","Inventory-On-Hand":"2137","Inventory Cost":"28158.925417954","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1768485811671","Last Price":"13.47","Cumulative COGS":"-21583.724582046","Cumulative Gross Margin":"200.315417954013","GM Percentage":"NULL","Cumulative GM Percentage":"0.00919551276778839"},{"trn":"88131009","sym":"ABC","tDate":"2013-10-09","qty":"187.00","price_unit":"13.36","price_extended":"2498.32","Inventory-On-Hand":"2324","Inventory Cost":"30657.245417954","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1915858080697","Last Price":"13.36","Cumulative COGS":"-21583.724582046","Cumulative Gross Margin":"200.315417954013","GM Percentage":"NULL","Cumulative GM Percentage":"0.00919551276778839"},{"trn":"16131107","sym":"ABC","tDate":"2013-11-07","qty":"27.00","price_unit":"12.68","price_extended":"342.36","Inventory-On-Hand":"2351","Inventory Cost":"30999.605417954","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"13.1857105138043","Last Price":"12.68","Cumulative COGS":"-21583.724582046","Cumulative Gross Margin":"200.315417954013","GM Percentage":"NULL","Cumulative GM Percentage":"0.00919551276778839"},{"trn":"86131114","sym":"ABC","tDate":"2013-11-14","qty":"-386.00","price_unit":"12.40","price_extended":"-4786.40","Inventory-On-Hand":"1965","Inventory Cost":"25909.9211596255","Gross Margin on Sales":"-303.284258328478","Cost-of-Goods Sold":"-5089.68425832848","Average Price":"13.1857105138043","Last Price":"12.68","Cumulative COGS":"-26673.4088403745","Cumulative Gross Margin":"-102.968840374465","GM Percentage":"-0.0633637511132539","Cumulative GM Percentage":"-0.00387531559035023"},{"trn":"8131231","sym":"ABC","tDate":"2013-12-31","qty":"-145.00","price_unit":"13.19","price_extended":"-1912.55","Inventory-On-Hand":"1820","Inventory Cost":"23997.9931351239","Gross Margin on Sales":"0.621975498369238","Cost-of-Goods Sold":"-1911.92802450163","Average Price":"13.1857105138043","Last Price":"12.68","Cumulative COGS":"-28585.3368648761","Cumulative Gross Margin":"-102.346864876096","GM Percentage":"0.000325207444704315","Cumulative GM Percentage":"-0.00359326267628841"},{"trn":"15130307","sym":"GHI","tDate":"2013-03-07","qty":"559.00","price_unit":"35.21","price_extended":"19682.39","Inventory-On-Hand":"559","Inventory Cost":"19682.39","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"35.21","Last Price":"35.21","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"69130403","sym":"GHI","tDate":"2013-04-03","qty":"-151.00","price_unit":"33.16","price_extended":"-5007.16","Inventory-On-Hand":"408","Inventory Cost":"14365.68","Gross Margin on Sales":"-309.549999999999","Cost-of-Goods Sold":"-5316.71","Average Price":"35.21","Last Price":"35.21","Cumulative COGS":"-5316.71","Cumulative Gross Margin":"-309.549999999999","GM Percentage":"-0.0618214716525933","Cumulative GM Percentage":"-0.0618214716525933"},{"trn":"56130419","sym":"GHI","tDate":"2013-04-19","qty":"416.00","price_unit":"34.46","price_extended":"14335.36","Inventory-On-Hand":"824","Inventory Cost":"28701.04","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"34.831359223301","Last Price":"34.46","Cumulative COGS":"-5316.71","Cumulative Gross Margin":"-309.549999999999","GM Percentage":"NULL","Cumulative GM Percentage":"-0.0618214716525933"},{"trn":"4130516","sym":"GHI","tDate":"2013-05-16","qty":"160.00","price_unit":"34.48","price_extended":"5516.80","Inventory-On-Hand":"984","Inventory Cost":"34217.84","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"34.7742276422764","Last Price":"34.48","Cumulative COGS":"-5316.71","Cumulative Gross Margin":"-309.549999999999","GM Percentage":"NULL","Cumulative GM Percentage":"-0.0618214716525933"},{"trn":"43130521","sym":"GHI","tDate":"2013-05-21","qty":"-174.00","price_unit":"34.20","price_extended":"-5950.80","Inventory-On-Hand":"810","Inventory Cost":"28167.1243902439","Gross Margin on Sales":"-99.9156097560981","Cost-of-Goods Sold":"-6050.7156097561","Average Price":"34.7742276422764","Last Price":"34.48","Cumulative COGS":"-11367.4256097561","Cumulative Gross Margin":"-409.465609756097","GM Percentage":"-0.0167902819379072","Cumulative GM Percentage":"-0.0373669560535079"},{"trn":"70130702","sym":"GHI","tDate":"2013-07-02","qty":"-162.00","price_unit":"35.48","price_extended":"-5747.76","Inventory-On-Hand":"648","Inventory Cost":"22533.6995121951","Gross Margin on Sales":"114.335121951221","Cost-of-Goods Sold":"-5633.42487804878","Average Price":"34.7742276422764","Last Price":"34.48","Cumulative COGS":"-17000.8504878049","Cumulative Gross Margin":"-295.130487804877","GM Percentage":"0.0198921183123896","Cumulative GM Percentage":"-0.0176664332818266"},{"trn":"96130716","sym":"GHI","tDate":"2013-07-16","qty":"-347.00","price_unit":"35.29","price_extended":"-12245.63","Inventory-On-Hand":"301","Inventory Cost":"10467.0425203252","Gross Margin on Sales":"178.973008130077","Cost-of-Goods Sold":"-12066.6569918699","Average Price":"34.7742276422764","Last Price":"34.48","Cumulative COGS":"-29067.5074796748","Cumulative Gross Margin":"-116.1574796748","GM Percentage":"0.0146152552486133","Cumulative GM Percentage":"-0.00401216107970095"},{"trn":"63130806","sym":"GHI","tDate":"2013-08-06","qty":"445.00","price_unit":"33.50","price_extended":"14907.50","Inventory-On-Hand":"746","Inventory Cost":"25374.5425203252","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"34.0141320647791","Last Price":"33.5","Cumulative COGS":"-29067.5074796748","Cumulative Gross Margin":"-116.1574796748","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00401216107970095"},{"trn":"77130825","sym":"GHI","tDate":"2013-08-25","qty":"272.00","price_unit":"33.86","price_extended":"9209.92","Inventory-On-Hand":"1018","Inventory Cost":"34584.4625203252","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"33.9729494305749","Last Price":"33.86","Cumulative COGS":"-29067.5074796748","Cumulative Gross Margin":"-116.1574796748","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00401216107970095"},{"trn":"95130908","sym":"GHI","tDate":"2013-09-08","qty":"103.00","price_unit":"35.42","price_extended":"3648.26","Inventory-On-Hand":"1121","Inventory Cost":"38232.7225203252","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"34.105907689853","Last Price":"35.42","Cumulative COGS":"-29067.5074796748","Cumulative Gross Margin":"-116.1574796748","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00401216107970095"},{"trn":"6130924","sym":"GHI","tDate":"2013-09-24","qty":"328.00","price_unit":"34.95","price_extended":"11463.60","Inventory-On-Hand":"1449","Inventory Cost":"49696.3225203252","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-29067.5074796748","Cumulative Gross Margin":"-116.1574796748","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00401216107970095"},{"trn":"17130924","sym":"GHI","tDate":"2013-09-24","qty":"-291.00","price_unit":"35.69","price_extended":"-10385.79","Inventory-On-Hand":"1158","Inventory Cost":"39715.9016415021","Gross Margin on Sales":"405.369121176926","Cost-of-Goods Sold":"-9980.42087882308","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-39047.9283584979","Cumulative Gross Margin":"289.211641502126","GM Percentage":"0.0390311301477235","Cumulative GM Percentage":"0.00735212680693425"},{"trn":"85131002","sym":"GHI","tDate":"2013-10-02","qty":"-295.00","price_unit":"32.51","price_extended":"-9590.45","Inventory-On-Hand":"863","Inventory Cost":"29598.2928468189","Gross Margin on Sales":"-527.158794683182","Cost-of-Goods Sold":"-10117.6087946832","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-49165.5371531811","Cumulative Gross Margin":"-237.947153181057","GM Percentage":"-0.0549670552146335","Cumulative GM Percentage":"-0.00486325104467759"},{"trn":"52131014","sym":"GHI","tDate":"2013-10-14","qty":"-91.00","price_unit":"33.12","price_extended":"-3013.92","Inventory-On-Hand":"772","Inventory Cost":"26477.2677610014","Gross Margin on Sales":"-107.105085817524","Cost-of-Goods Sold":"-3121.02508581752","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-52286.5622389986","Cumulative Gross Margin":"-345.052238998581","GM Percentage":"-0.0355368044996297","Cumulative GM Percentage":"-0.00664309218192888"},{"trn":"73131028","sym":"GHI","tDate":"2013-10-28","qty":"-46.00","price_unit":"34.65","price_extended":"-1593.90","Inventory-On-Hand":"726","Inventory Cost":"24899.6067286101","Gross Margin on Sales":"16.2389676087246","Cost-of-Goods Sold":"-1577.66103239128","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-53864.2232713899","Cumulative Gross Margin":"-328.813271389856","GM Percentage":"0.0101881972574971","Cumulative GM Percentage":"-0.00614197727055525"},{"trn":"50131103","sym":"GHI","tDate":"2013-11-03","qty":"-246.00","price_unit":"35.61","price_extended":"-8760.06","Inventory-On-Hand":"480","Inventory Cost":"16462.5499032133","Gross Margin on Sales":"323.003174603173","Cost-of-Goods Sold":"-8437.05682539683","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-62301.2800967867","Cumulative Gross Margin":"-5.81009678668352","GM Percentage":"0.0368722559666455","Cumulative GM Percentage":"-9.32667622009035E-05"},{"trn":"87131116","sym":"GHI","tDate":"2013-11-16","qty":"-320.00","price_unit":"34.16","price_extended":"-10931.20","Inventory-On-Hand":"160","Inventory Cost":"5487.51663440444","Gross Margin on Sales":"-43.8332688088758","Cost-of-Goods Sold":"-10975.0332688089","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-73276.3133655956","Cumulative Gross Margin":"-49.6433655955593","GM Percentage":"-0.00400992286380963","Cumulative GM Percentage":"-0.000677941050652164"},{"trn":"94131117","sym":"GHI","tDate":"2013-11-17","qty":"-92.00","price_unit":"32.65","price_extended":"-3003.80","Inventory-On-Hand":"68","Inventory Cost":"2332.19456962189","Gross Margin on Sales":"-151.522064782552","Cost-of-Goods Sold":"-3155.32206478255","Average Price":"34.2969789650277","Last Price":"34.95","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"-0.0504434598783382","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"81131123","sym":"GHI","tDate":"2013-11-23","qty":"187.00","price_unit":"33.86","price_extended":"6331.82","Inventory-On-Hand":"255","Inventory Cost":"8664.01456962189","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"33.9765277240074","Last Price":"33.86","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"51131206","sym":"GHI","tDate":"2013-12-06","qty":"85.00","price_unit":"33.64","price_extended":"2859.40","Inventory-On-Hand":"340","Inventory Cost":"11523.4145696219","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"33.8923957930055","Last Price":"33.64","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"75131216","sym":"GHI","tDate":"2013-12-16","qty":"500.00","price_unit":"33.55","price_extended":"16775.00","Inventory-On-Hand":"840","Inventory Cost":"28298.4145696219","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"33.6885887733594","Last Price":"33.55","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"13131221","sym":"GHI","tDate":"2013-12-21","qty":"72.00","price_unit":"34.38","price_extended":"2475.36","Inventory-On-Hand":"912","Inventory Cost":"30773.7745696219","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"33.7431738701994","Last Price":"34.38","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"65131227","sym":"GHI","tDate":"2013-12-27","qty":"376.00","price_unit":"33.49","price_extended":"12592.24","Inventory-On-Hand":"1288","Inventory Cost":"43366.0145696219","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"33.6692659702033","Last Price":"33.49","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"53140102","sym":"GHI","tDate":"2014-01-02","qty":"396.00","price_unit":"35.52","price_extended":"14065.92","Inventory-On-Hand":"1684","Inventory Cost":"57431.9345696219","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"34.1044742099892","Last Price":"35.52","Cumulative COGS":"-76431.6354303781","Cumulative Gross Margin":"-201.165430378112","GM Percentage":"NULL","Cumulative GM Percentage":"-0.00263891105981783"},{"trn":"18140125","sym":"GHI","tDate":"2014-01-25","qty":"-78.00","price_unit":"35.46","price_extended":"-2765.88","Inventory-On-Hand":"1606","Inventory Cost":"54771.7855812427","Gross Margin on Sales":"105.731011620842","Cost-of-Goods Sold":"-2660.14898837916","Average Price":"34.1044742099892","Last Price":"35.52","Cumulative COGS":"-79091.7844187573","Cumulative Gross Margin":"-95.4344187572697","GM Percentage":"0.038226897631438","Cumulative GM Percentage":"-0.00120808643383232"},{"trn":"37130220","sym":"XYZ","tDate":"2013-02-20","qty":"528.00","price_unit":"23.54","price_extended":"12429.12","Inventory-On-Hand":"528","Inventory Cost":"12429.12","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.54","Last Price":"23.54","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"44130227","sym":"XYZ","tDate":"2013-02-27","qty":"357.00","price_unit":"22.86","price_extended":"8161.02","Inventory-On-Hand":"885","Inventory Cost":"20590.14","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.2656949152542","Last Price":"22.86","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"67130302","sym":"XYZ","tDate":"2013-03-02","qty":"9.00","price_unit":"24.04","price_extended":"216.36","Inventory-On-Hand":"894","Inventory Cost":"20806.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.2734899328859","Last Price":"24.04","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"trn":"38130311","sym":"XYZ","tDate":"2013-03-11","qty":"-193.00","price_unit":"24.90","price_extended":"-4805.70","Inventory-On-Hand":"701","Inventory Cost":"16314.716442953","Gross Margin on Sales":"313.91644295302","Cost-of-Goods Sold":"-4491.78355704698","Average Price":"23.2734899328859","Last Price":"24.04","Cumulative COGS":"-4491.78355704698","Cumulative Gross Margin":"313.91644295302","GM Percentage":"0.0653216894423331","Cumulative GM Percentage":"0.0653216894423331"},{"trn":"59130320","sym":"XYZ","tDate":"2013-03-20","qty":"-20.00","price_unit":"23.59","price_extended":"-471.80","Inventory-On-Hand":"681","Inventory Cost":"15849.2466442953","Gross Margin on Sales":"6.33020134228246","Cost-of-Goods Sold":"-465.469798657718","Average Price":"23.2734899328859","Last Price":"24.04","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"0.0134171287458297","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"35130321","sym":"XYZ","tDate":"2013-03-21","qty":"275.00","price_unit":"24.83","price_extended":"6828.25","Inventory-On-Hand":"956","Inventory Cost":"22677.4966442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7212307994721","Last Price":"24.83","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"98130413","sym":"XYZ","tDate":"2013-04-13","qty":"243.00","price_unit":"24.25","price_extended":"5892.75","Inventory-On-Hand":"1199","Inventory Cost":"28570.2466442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.8283958668018","Last Price":"24.25","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"24130418","sym":"XYZ","tDate":"2013-04-18","qty":"275.00","price_unit":"24.83","price_extended":"6828.25","Inventory-On-Hand":"1474","Inventory Cost":"35398.4966442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"24.0152623095626","Last Price":"24.83","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"60130419","sym":"XYZ","tDate":"2013-04-19","qty":"277.00","price_unit":"22.83","price_extended":"6323.91","Inventory-On-Hand":"1751","Inventory Cost":"41722.4066442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.8277593628186","Last Price":"22.83","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"83130428","sym":"XYZ","tDate":"2013-04-28","qty":"142.00","price_unit":"23.13","price_extended":"3284.46","Inventory-On-Hand":"1893","Inventory Cost":"45006.8666442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7754181956129","Last Price":"23.13","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"34130507","sym":"XYZ","tDate":"2013-05-07","qty":"55.00","price_unit":"23.43","price_extended":"1288.65","Inventory-On-Hand":"1948","Inventory Cost":"46295.5166442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7656656284884","Last Price":"23.43","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"99130515","sym":"XYZ","tDate":"2013-05-15","qty":"97.00","price_unit":"25.13","price_extended":"2437.61","Inventory-On-Hand":"2045","Inventory Cost":"48733.1266442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.8303797771615","Last Price":"25.13","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"19130516","sym":"XYZ","tDate":"2013-05-16","qty":"315.00","price_unit":"23.57","price_extended":"7424.55","Inventory-On-Hand":"2360","Inventory Cost":"56157.6766442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7956256967353","Last Price":"23.57","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"30130519","sym":"XYZ","tDate":"2013-05-19","qty":"467.00","price_unit":"23.15","price_extended":"10811.05","Inventory-On-Hand":"2827","Inventory Cost":"66968.7266442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.6889729905537","Last Price":"23.15","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"29130602","sym":"XYZ","tDate":"2013-06-02","qty":"114.00","price_unit":"24.29","price_extended":"2769.06","Inventory-On-Hand":"2941","Inventory Cost":"69737.7866442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7122701952721","Last Price":"24.29","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"91130615","sym":"XYZ","tDate":"2013-06-15","qty":"545.00","price_unit":"23.88","price_extended":"13014.60","Inventory-On-Hand":"3486","Inventory Cost":"82752.3866442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7384930132804","Last Price":"23.88","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"23130619","sym":"XYZ","tDate":"2013-06-19","qty":"296.00","price_unit":"23.46","price_extended":"6944.16","Inventory-On-Hand":"3782","Inventory Cost":"89696.5466442953","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7166966272595","Last Price":"23.46","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"47130619","sym":"XYZ","tDate":"2013-06-19","qty":"413.00","price_unit":"25.18","price_extended":"10399.34","Inventory-On-Hand":"4195","Inventory Cost":"100095.886644295","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.8607596291526","Last Price":"25.18","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"74130619","sym":"XYZ","tDate":"2013-06-19","qty":"202.00","price_unit":"22.95","price_extended":"4635.90","Inventory-On-Hand":"4397","Inventory Cost":"104731.786644295","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-4957.2533557047","Cumulative Gross Margin":"320.246644295303","GM Percentage":"NULL","Cumulative GM Percentage":"0.0606815053141265"},{"trn":"55130630","sym":"XYZ","tDate":"2013-06-30","qty":"-272.00","price_unit":"23.45","price_extended":"-6378.40","Inventory-On-Hand":"4125","Inventory Cost":"98253.0406885873","Gross Margin on Sales":"-100.345955708059","Cost-of-Goods Sold":"-6478.74595570806","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-11435.9993114128","Cumulative Gross Margin":"219.900688587244","GM Percentage":"-0.0157321515910038","Cumulative GM Percentage":"0.018866041111132"},{"trn":"14130705","sym":"XYZ","tDate":"2013-07-05","qty":"-277.00","price_unit":"25.01","price_extended":"-6927.77","Inventory-On-Hand":"3848","Inventory Cost":"91655.2001381052","Gross Margin on Sales":"329.929449517902","Cost-of-Goods Sold":"-6597.8405504821","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-18033.8398618949","Cumulative Gross Margin":"549.830138105146","GM Percentage":"0.047624192130787","Cumulative GM Percentage":"0.0295867359948356"},{"trn":"5130706","sym":"XYZ","tDate":"2013-07-06","qty":"-170.00","price_unit":"23.46","price_extended":"-3988.20","Inventory-On-Hand":"3678","Inventory Cost":"87605.9839157876","Gross Margin on Sales":"-61.0162223175403","Cost-of-Goods Sold":"-4049.21622231754","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-22083.0560842124","Cumulative Gross Margin":"488.813915787606","GM Percentage":"-0.0152991881845294","Cumulative GM Percentage":"0.0216558892013646"},{"trn":"58130722","sym":"XYZ","tDate":"2013-07-22","qty":"-88.00","price_unit":"24.64","price_extended":"-2168.32","Inventory-On-Hand":"3590","Inventory Cost":"85509.9190477644","Gross Margin on Sales":"72.2551319768031","Cost-of-Goods Sold":"-2096.0648680232","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-24179.1209522356","Cumulative Gross Margin":"561.069047764409","GM Percentage":"0.0333230943665156","Cumulative GM Percentage":"0.0226784453864101"},{"trn":"57130813","sym":"XYZ","tDate":"2013-08-13","qty":"-163.00","price_unit":"23.65","price_extended":"-3854.95","Inventory-On-Hand":"3427","Inventory Cost":"81627.4352581305","Gross Margin on Sales":"-27.5337896338724","Cost-of-Goods Sold":"-3882.48378963387","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-28061.6047418695","Cumulative Gross Margin":"533.535258130536","GM Percentage":"-0.00714245052046652","Cumulative GM Percentage":"0.0186582495532645"},{"trn":"54130831","sym":"XYZ","tDate":"2013-08-31","qty":"-61.00","price_unit":"23.23","price_extended":"-1417.03","Inventory-On-Hand":"3366","Inventory Cost":"80174.4812018872","Gross Margin on Sales":"-35.924056243354","Cost-of-Goods Sold":"-1452.95405624335","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-29514.5587981128","Cumulative Gross Margin":"497.611201887182","GM Percentage":"-0.0253516553942782","Cumulative GM Percentage":"0.0165803139822006"},{"trn":"11130906","sym":"XYZ","tDate":"2013-09-06","qty":"-13.00","price_unit":"23.97","price_extended":"-311.61","Inventory-On-Hand":"3353","Inventory Cost":"79864.8352554747","Gross Margin on Sales":"1.96405358747813","Cost-of-Goods Sold":"-309.645946412522","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-29824.2047445253","Cumulative Gross Margin":"499.57525547466","GM Percentage":"0.00630292220236236","Cumulative GM Percentage":"0.0164747025428446"},{"trn":"27130906","sym":"XYZ","tDate":"2013-09-06","qty":"-147.00","price_unit":"23.81","price_extended":"-3500.07","Inventory-On-Hand":"3206","Inventory Cost":"76363.4541691177","Gross Margin on Sales":"-1.31108635692317","Cost-of-Goods Sold":"-3501.38108635692","Average Price":"23.818918954809","Last Price":"22.95","Cumulative COGS":"-33325.5858308823","Cumulative Gross Margin":"498.264169117737","GM Percentage":"-0.000374588610205844","Cumulative GM Percentage":"0.0147311488525918"},{"trn":"36130917","sym":"XYZ","tDate":"2013-09-17","qty":"92.00","price_unit":"24.60","price_extended":"2263.20","Inventory-On-Hand":"3298","Inventory Cost":"78626.6541691177","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.8407077529162","Last Price":"24.6","Cumulative COGS":"-33325.5858308823","Cumulative Gross Margin":"498.264169117737","GM Percentage":"NULL","Cumulative GM Percentage":"0.0147311488525918"},{"trn":"92130924","sym":"XYZ","tDate":"2013-09-24","qty":"-248.00","price_unit":"24.58","price_extended":"-6095.84","Inventory-On-Hand":"3050","Inventory Cost":"72714.1586463945","Gross Margin on Sales":"183.344477276773","Cost-of-Goods Sold":"-5912.49552272323","Average Price":"23.8407077529162","Last Price":"24.6","Cumulative COGS":"-39238.0813536055","Cumulative Gross Margin":"681.60864639451","GM Percentage":"0.0300769832011294","Cumulative GM Percentage":"0.0170744974821826"},{"trn":"42131011","sym":"XYZ","tDate":"2013-10-11","qty":"-250.00","price_unit":"23.12","price_extended":"-5780.00","Inventory-On-Hand":"2800","Inventory Cost":"66753.9817081655","Gross Margin on Sales":"-180.176938229051","Cost-of-Goods Sold":"-5960.17693822905","Average Price":"23.8407077529162","Last Price":"24.6","Cumulative COGS":"-45198.2582918345","Cumulative Gross Margin":"501.431708165458","GM Percentage":"-0.0311724806624656","Cumulative GM Percentage":"0.0109723218727623"},{"trn":"22131012","sym":"XYZ","tDate":"2013-10-12","qty":"596.00","price_unit":"23.16","price_extended":"13803.36","Inventory-On-Hand":"3396","Inventory Cost":"80557.3417081655","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7212431413915","Last Price":"23.16","Cumulative COGS":"-45198.2582918345","Cumulative Gross Margin":"501.431708165458","GM Percentage":"NULL","Cumulative GM Percentage":"0.0109723218727623"},{"trn":"40131013","sym":"XYZ","tDate":"2013-10-13","qty":"359.00","price_unit":"23.91","price_extended":"8583.69","Inventory-On-Hand":"3755","Inventory Cost":"89141.0317081655","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.7392894029735","Last Price":"23.91","Cumulative COGS":"-45198.2582918345","Cumulative Gross Margin":"501.431708165458","GM Percentage":"NULL","Cumulative GM Percentage":"0.0109723218727623"},{"trn":"1131019","sym":"XYZ","tDate":"2013-10-19","qty":"424.00","price_unit":"25.13","price_extended":"10655.12","Inventory-On-Hand":"4179","Inventory Cost":"99796.1517081655","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.8803904542152","Last Price":"25.13","Cumulative COGS":"-45198.2582918345","Cumulative Gross Margin":"501.431708165458","GM Percentage":"NULL","Cumulative GM Percentage":"0.0109723218727623"},{"trn":"9131025","sym":"XYZ","tDate":"2013-10-25","qty":"-153.00","price_unit":"24.31","price_extended":"-3719.43","Inventory-On-Hand":"4026","Inventory Cost":"96142.4519686705","Gross Margin on Sales":"65.7302605050713","Cost-of-Goods Sold":"-3653.69973949493","Average Price":"23.8803904542152","Last Price":"25.13","Cumulative COGS":"-48851.9580313295","Cumulative Gross Margin":"567.16196867053","GM Percentage":"0.0176721326937384","Cumulative GM Percentage":"0.0114765695680241"},{"trn":"62131027","sym":"XYZ","tDate":"2013-10-27","qty":"-248.00","price_unit":"24.71","price_extended":"-6128.08","Inventory-On-Hand":"3778","Inventory Cost":"90220.1151360252","Gross Margin on Sales":"205.743167354622","Cost-of-Goods Sold":"-5922.33683264538","Average Price":"23.8803904542152","Last Price":"25.13","Cumulative COGS":"-54774.2948639749","Cumulative Gross Margin":"772.905136025151","GM Percentage":"0.0335738383563239","Cumulative GM Percentage":"0.0139143851719826"},{"trn":"45131030","sym":"XYZ","tDate":"2013-10-30","qty":"-350.00","price_unit":"23.36","price_extended":"-8176.00","Inventory-On-Hand":"3428","Inventory Cost":"81861.9784770498","Gross Margin on Sales":"-182.136658975331","Cost-of-Goods Sold":"-8358.13665897533","Average Price":"23.8803904542152","Last Price":"25.13","Cumulative COGS":"-63132.4315229502","Cumulative Gross Margin":"590.768477049821","GM Percentage":"-0.0222769886222273","Cumulative GM Percentage":"0.00927085389700801"},{"trn":"21131103","sym":"XYZ","tDate":"2013-11-03","qty":"-326.00","price_unit":"23.24","price_extended":"-7576.24","Inventory-On-Hand":"3102","Inventory Cost":"74076.9711889757","Gross Margin on Sales":"-208.767288074165","Cost-of-Goods Sold":"-7785.00728807416","Average Price":"23.8803904542152","Last Price":"25.13","Cumulative COGS":"-70917.4388110243","Cumulative Gross Margin":"382.001188975656","GM Percentage":"-0.0275555272898119","Cumulative GM Percentage":"0.00535770251457313"},{"trn":"97131125","sym":"XYZ","tDate":"2013-11-25","qty":"-278.00","price_unit":"23.18","price_extended":"-6444.04","Inventory-On-Hand":"2824","Inventory Cost":"67438.2226427038","Gross Margin on Sales":"-194.708546271832","Cost-of-Goods Sold":"-6638.74854627183","Average Price":"23.8803904542152","Last Price":"25.13","Cumulative COGS":"-77556.1873572962","Cumulative Gross Margin":"187.292642703824","GM Percentage":"-0.0302152913811572","Cumulative GM Percentage":"0.00240911061228316"},{"trn":"28131209","sym":"XYZ","tDate":"2013-12-09","qty":"315.00","price_unit":"24.46","price_extended":"7704.90","Inventory-On-Hand":"3139","Inventory Cost":"75143.1226427038","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.9385545214093","Last Price":"24.46","Cumulative COGS":"-77556.1873572962","Cumulative Gross Margin":"187.292642703824","GM Percentage":"NULL","Cumulative GM Percentage":"0.00240911061228316"},{"trn":"79131223","sym":"XYZ","tDate":"2013-12-23","qty":"-376.00","price_unit":"25.12","price_extended":"-9445.12","Inventory-On-Hand":"2763","Inventory Cost":"66142.2261426539","Gross Margin on Sales":"444.2234999501","Cost-of-Goods Sold":"-9000.8965000499","Average Price":"23.9385545214093","Last Price":"24.46","Cumulative COGS":"-86557.0838573461","Cumulative Gross Margin":"631.516142653923","GM Percentage":"0.0470320652305211","Cumulative GM Percentage":"0.00724310451886971"},{"trn":"33140102","sym":"XYZ","tDate":"2014-01-02","qty":"-196.00","price_unit":"22.98","price_extended":"-4504.08","Inventory-On-Hand":"2567","Inventory Cost":"61450.2694564577","Gross Margin on Sales":"-187.876686196223","Cost-of-Goods Sold":"-4691.95668619622","Average Price":"23.9385545214093","Last Price":"24.46","Cumulative COGS":"-91249.0405435423","Cumulative Gross Margin":"443.6394564577","GM Percentage":"-0.0417125553267755","Cumulative GM Percentage":"0.00483833013123512"},{"trn":"84140104","sym":"XYZ","tDate":"2014-01-04","qty":"261.00","price_unit":"24.46","price_extended":"6384.06","Inventory-On-Hand":"2828","Inventory Cost":"67834.3294564577","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.9866794400487","Last Price":"24.46","Cumulative COGS":"-91249.0405435423","Cumulative Gross Margin":"443.6394564577","GM Percentage":"NULL","Cumulative GM Percentage":"0.00483833013123512"},{"trn":"64140109","sym":"XYZ","tDate":"2014-01-09","qty":"-253.00","price_unit":"24.46","price_extended":"-6188.38","Inventory-On-Hand":"2575","Inventory Cost":"61765.6995581254","Gross Margin on Sales":"119.750101667679","Cost-of-Goods Sold":"-6068.62989833232","Average Price":"23.9866794400487","Last Price":"24.46","Cumulative COGS":"-97317.6704418746","Cumulative Gross Margin":"563.389558125379","GM Percentage":"0.0193507996709444","Cumulative GM Percentage":"0.00575585877518469"},{"trn":"78140112","sym":"XYZ","tDate":"2014-01-12","qty":"332.00","price_unit":"24.28","price_extended":"8060.96","Inventory-On-Hand":"2907","Inventory Cost":"69826.6595581254","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"24.0201787265653","Last Price":"24.28","Cumulative COGS":"-97317.6704418746","Cumulative Gross Margin":"563.389558125379","GM Percentage":"NULL","Cumulative GM Percentage":"0.00575585877518469"},{"trn":"80140126","sym":"XYZ","tDate":"2014-01-26","qty":"404.00","price_unit":"24.23","price_extended":"9788.92","Inventory-On-Hand":"3311","Inventory Cost":"79615.5795581254","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"24.0457805974405","Last Price":"24.23","Cumulative COGS":"-97317.6704418746","Cumulative Gross Margin":"563.389558125379","GM Percentage":"NULL","Cumulative GM Percentage":"0.00575585877518469"},{"trn":"82140131","sym":"XYZ","tDate":"2014-01-31","qty":"548.00","price_unit":"23.65","price_extended":"12960.20","Inventory-On-Hand":"3859","Inventory Cost":"92575.7795581254","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.989577496275","Last Price":"23.65","Cumulative COGS":"-97317.6704418746","Cumulative Gross Margin":"563.389558125379","GM Percentage":"NULL","Cumulative GM Percentage":"0.00575585877518469"},{"trn":"66140203","sym":"XYZ","tDate":"2014-02-03","qty":"459.00","price_unit":"23.70","price_extended":"10878.30","Inventory-On-Hand":"4318","Inventory Cost":"103454.079558125","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.9587956364348","Last Price":"23.7","Cumulative COGS":"-97317.6704418746","Cumulative Gross Margin":"563.389558125379","GM Percentage":"NULL","Cumulative GM Percentage":"0.00575585877518469"},{"trn":"72140204","sym":"XYZ","tDate":"2014-02-04","qty":"-208.00","price_unit":"24.36","price_extended":"-5066.88","Inventory-On-Hand":"4110","Inventory Cost":"98470.6500657469","Gross Margin on Sales":"83.4505076215673","Cost-of-Goods Sold":"-4983.42949237843","Average Price":"23.9587956364348","Last Price":"23.7","Cumulative COGS":"-102301.099934253","Cumulative Gross Margin":"646.840065746946","GM Percentage":"0.0164698014599847","Cumulative GM Percentage":"0.0062831763874726"},{"trn":"31140205","sym":"XYZ","tDate":"2014-02-05","qty":"42.00","price_unit":"24.39","price_extended":"1024.38","Inventory-On-Hand":"4152","Inventory Cost":"99495.0300657469","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"23.9631575302859","Last Price":"24.39","Cumulative COGS":"-102301.099934253","Cumulative Gross Margin":"646.840065746946","GM Percentage":"NULL","Cumulative GM Percentage":"0.0062831763874726"}]}

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   #p
  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 WAC 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 #p
 ORDER BY [Manufacturer],
          [Description],
          [Material],
          [Size],
          [Color],
          [Date],
          qty DESC;

--Put the tvf results into the #WAC table
SELECT CAST([ID] as Int) as ID,
       [QTY],
       [EB],
       [GM],
       [COGS],
       [UP],
       [LP],
       [COGSC],
       [GMC],
       [GMP],
       [CGMP]
INTO   #WAC
  FROM wct.WACtvf('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],
       w.[QTY] as [Inventory-On-Hand],
       w.[EB] as [Inventory Cost],
       w.[GM] as [Gross Margin on Sales],
       w.[COGS] as [Cost-of-Goods Sold],
       w.[UP] as [Average Price],
       w.[LP] as [Last Price],
       w.[COGSC] as [Cumulative COGS],
       w.[GMC] as [Cumulative Gross Margin],
       w.[GMP] as [GM Percentage],
       w.[CGMP] as [Cumulative GM Percentage]
  FROM #p2
 INNER JOIN #WAC w
    ON #p2.ID = w.[ID]
 ORDER BY #p2.id;

This produces the following result.

{"columns":[{"field":"Manufacturer"},{"field":"Description"},{"field":"Material"},{"field":"Size"},{"field":"Color"},{"field":"Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"qty","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"unit price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"extended price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Inventory-On-Hand","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Inventory Cost","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Gross Margin on Sales","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cost-of-Goods Sold","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Average Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Last Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative COGS","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative Gross Margin","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GM Percentage"},{"field":"Cumulative GM Percentage"}],"rows":[{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"12 oz","Color":"Black","Date":"2017-01-15","qty":"50","unit price":"1.12","extended price":"56.00","Inventory-On-Hand":"50","Inventory Cost":"56","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.12","Last Price":"1.12","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"12 oz","Color":"Black","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"113.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.135","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"12 oz","Color":"White","Date":"2017-01-15","qty":"50","unit price":"1.12","extended price":"56.00","Inventory-On-Hand":"50","Inventory Cost":"56","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.12","Last Price":"1.12","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"12 oz","Color":"White","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"113.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.135","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"16 oz","Color":"Black","Date":"2017-01-15","qty":"50","unit price":"1.12","extended price":"56.00","Inventory-On-Hand":"50","Inventory Cost":"56","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.12","Last Price":"1.12","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"16 oz","Color":"Black","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"113.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.135","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"16 oz","Color":"White","Date":"2017-01-15","qty":"50","unit price":"1.12","extended price":"56.00","Inventory-On-Hand":"50","Inventory Cost":"56","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.12","Last Price":"1.12","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"16 oz","Color":"White","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"113.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.135","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"20 oz","Color":"Black","Date":"2017-01-15","qty":"50","unit price":"1.12","extended price":"56.00","Inventory-On-Hand":"50","Inventory Cost":"56","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.12","Last Price":"1.12","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"20 oz","Color":"Black","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"113.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.135","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"20 oz","Color":"White","Date":"2017-01-15","qty":"50","unit price":"1.12","extended price":"56.00","Inventory-On-Hand":"50","Inventory Cost":"56","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.12","Last Price":"1.12","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"20 oz","Color":"White","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"113.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.135","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"8 oz","Color":"Black","Date":"2017-01-15","qty":"50","unit price":"1.12","extended price":"56.00","Inventory-On-Hand":"50","Inventory Cost":"56","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.12","Last Price":"1.12","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"8 oz","Color":"Black","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"113.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.135","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"8 oz","Color":"White","Date":"2017-01-15","qty":"50","unit price":"1.12","extended price":"56.00","Inventory-On-Hand":"50","Inventory Cost":"56","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.12","Last Price":"1.12","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Ceramic","Size":"8 oz","Color":"White","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"113.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.135","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"12 oz","Color":"Black","Date":"2017-01-15","qty":"50","unit price":"0.89","extended price":"44.50","Inventory-On-Hand":"50","Inventory Cost":"44.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"12 oz","Color":"Black","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"102","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.02","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"12 oz","Color":"White","Date":"2017-01-15","qty":"50","unit price":"0.89","extended price":"44.50","Inventory-On-Hand":"50","Inventory Cost":"44.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"12 oz","Color":"White","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"102","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.02","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"16 oz","Color":"Black","Date":"2017-01-15","qty":"50","unit price":"0.89","extended price":"44.50","Inventory-On-Hand":"50","Inventory Cost":"44.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"16 oz","Color":"Black","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"102","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.02","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"16 oz","Color":"White","Date":"2017-01-15","qty":"50","unit price":"0.89","extended price":"44.50","Inventory-On-Hand":"50","Inventory Cost":"44.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"16 oz","Color":"White","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"102","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.02","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"20 oz","Color":"Black","Date":"2017-01-15","qty":"50","unit price":"0.89","extended price":"44.50","Inventory-On-Hand":"50","Inventory Cost":"44.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"20 oz","Color":"Black","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"102","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.02","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"20 oz","Color":"White","Date":"2017-01-15","qty":"50","unit price":"0.89","extended price":"44.50","Inventory-On-Hand":"50","Inventory Cost":"44.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"20 oz","Color":"White","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"102","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.02","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"8 oz","Color":"Black","Date":"2017-01-15","qty":"50","unit price":"0.89","extended price":"44.50","Inventory-On-Hand":"50","Inventory Cost":"44.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"8 oz","Color":"Black","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"100","Inventory Cost":"102","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.02","Last Price":"1.15","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"8 oz","Color":"White","Date":"2017-01-15","qty":"50","unit price":"0.89","extended price":"44.50","Inventory-On-Hand":"50","Inventory Cost":"44.5","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"0","Cumulative Gross Margin":"0","GM Percentage":"NULL","Cumulative GM Percentage":"NULL"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"8 oz","Color":"White","Date":"2017-01-16","qty":"-5","unit price":"2.95","extended price":"-14.75","Inventory-On-Hand":"45","Inventory Cost":"40.05","Gross Margin on Sales":"10.3","Cost-of-Goods Sold":"-4.45","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"-4.45","Cumulative Gross Margin":"10.3","GM Percentage":"0.698305084745762","Cumulative GM Percentage":"0.698305084745762"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"8 oz","Color":"White","Date":"2017-01-23","qty":"-2","unit price":"2.95","extended price":"-5.90","Inventory-On-Hand":"43","Inventory Cost":"38.27","Gross Margin on Sales":"4.12","Cost-of-Goods Sold":"-1.78","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"-6.23","Cumulative Gross Margin":"14.42","GM Percentage":"0.698305084745763","Cumulative GM Percentage":"0.698305084745763"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"8 oz","Color":"White","Date":"2017-01-24","qty":"-1","unit price":"2.95","extended price":"-2.95","Inventory-On-Hand":"42","Inventory Cost":"37.38","Gross Margin on Sales":"2.06","Cost-of-Goods Sold":"-0.890000000000001","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"-7.12","Cumulative Gross Margin":"16.48","GM Percentage":"0.698305084745763","Cumulative GM Percentage":"0.698305084745763"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"8 oz","Color":"White","Date":"2017-02-01","qty":"-3","unit price":"2.95","extended price":"-8.85","Inventory-On-Hand":"39","Inventory Cost":"34.71","Gross Margin on Sales":"6.18","Cost-of-Goods Sold":"-2.67","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"-9.79000000000001","Cumulative Gross Margin":"22.66","GM Percentage":"0.698305084745762","Cumulative GM Percentage":"0.698305084745763"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"8 oz","Color":"White","Date":"2017-02-08","qty":"-7","unit price":"2.95","extended price":"-20.65","Inventory-On-Hand":"32","Inventory Cost":"28.48","Gross Margin on Sales":"14.42","Cost-of-Goods Sold":"-6.23","Average Price":"0.89","Last Price":"0.89","Cumulative COGS":"-16.02","Cumulative Gross Margin":"37.08","GM Percentage":"0.698305084745763","Cumulative GM Percentage":"0.698305084745763"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"8 oz","Color":"White","Date":"2017-02-15","qty":"50","unit price":"1.15","extended price":"57.50","Inventory-On-Hand":"82","Inventory Cost":"85.98","Gross Margin on Sales":"0","Cost-of-Goods Sold":"0","Average Price":"1.04853658536585","Last Price":"1.15","Cumulative COGS":"-16.02","Cumulative Gross Margin":"37.08","GM Percentage":"NULL","Cumulative GM Percentage":"0.698305084745763"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"8 oz","Color":"White","Date":"2017-02-15","qty":"-6","unit price":"2.95","extended price":"-17.70","Inventory-On-Hand":"76","Inventory Cost":"79.6887804878049","Gross Margin on Sales":"11.4087804878049","Cost-of-Goods Sold":"-6.29121951219513","Average Price":"1.04853658536585","Last Price":"1.15","Cumulative COGS":"-22.3112195121951","Cumulative Gross Margin":"48.4887804878049","GM Percentage":"0.644563869367507","Cumulative GM Percentage":"0.684869780901199"},{"Manufacturer":"ZYX","Description":"Coffee Mug","Material":"Plastic","Size":"8 oz","Color":"White","Date":"2017-02-22","qty":"-2","unit price":"2.95","extended price":"-5.90","Inventory-On-Hand":"74","Inventory Cost":"77.5917073170732","Gross Margin on Sales":"3.8029268292683","Cost-of-Goods Sold":"-2.0970731707317","Average Price":"1.04853658536585","Last Price":"1.15","Cumulative COGS":"-24.4082926829268","Cumulative Gross Margin":"52.2917073170732","GM Percentage":"0.644563869367508","Cumulative GM Percentage":"0.681769326167838"}]}

See Also

FIFO - Calculate FIFO (first in, first out) values in an ordered resultant table.

FIFOend - Calculate the ending FIFO balances in an ordered resultant table.

FIFOtvf - Calculate running FIFO (First In, First Out) values in an ordered resultant table.

LIFO - Calculate LIFO (last in, first out) values in an ordered resultant table.

LIFOend - Calculate the ending LIFO balances in an ordered resultant table.

LIFOtvf - Calculate the running LIFO balances in an ordered resultant table.

WAC - Calculate running weighted average cost in an ordered resultant table.