SQL Server WAC Function

Updated 2023-11-13 17:08:55.470000

Description

Use WAC to calculate running weighted average cost values in an ordered resultant table, without the need for a self-join. WAC calculates balances for each value from the first value to the last value in the ordered group or partition. WAC can return the quantity on-hand, the inventory value, the gross margin on sale, the gross margin percentage, the cost of goods sold, the average inventory price, the last inventory price, the cumulative cost of goods sold, the cumulative gross margin on sale, and the cumulative gross margin percentage.

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

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

WAC requires monotonically ascending row numbers within a partition; otherwise the function returns an error message

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

Syntax

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

Arguments

@Qty

the number of units being added to or subtracted from inventory. @Qty is an expression of type float or of a type that can be implicitly converted to float.

@Cost

the value associated with @Qty. Additions to inventory should have a @Cost > 0; withdrawals from inventory should have a cost <= 0. If you are not interested in calculating the gross margin on sales or if the quantity on hand will never be less than zero, then just enter zero for the @Cost when the @Qty is less than zero. @Cost is an expression of type float or of a type that can be implicitly converted to float.

@RV

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

@Round

the number of decimals places to store the result. @Round is only used in the calculation of the gross margin. @Round is an expression of type int or of a type that can be implicitly converted to int.

@RowNum

the number of the row within the group for which the sum is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.

@Id

a unique identifier for the WAC calculation. @Id allows you to specify multiple moving sums within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.

Return Type

float

Remarks

If @Id is NULL then @Id = 0.

To calculate LIFO values, use the LIFO function.

To calculate FIFO values, use the FIFO function.

If @RowNum is equal to 1, then cost of goods sold = 0, gross margin = 0, quantity on hand = @Qty, and inventory balance = @Cost.

@RowNum must be in ascending order.

There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem.

Examples

In the following examples, we calculate FIFO inventory values for stock trades in the ABC, XYZ, and GHI companies. We will create a temporary table, #c and populate it with some data. We can be either short or long the shares at any point in time.

--Create the temporary table
CREATE TABLE #c
(
    trn int,
    sym char(3),
    tDate date,
    qty money,
    price_unit money,
    price_extended money,
    PRIMARY KEY (trn)
);
--Populate the table with some data
INSERT INTO #c
VALUES
(01131019, 'XYZ', '2013-10-19', 424, 25.13, 10655.12);
INSERT INTO #c
VALUES
(02130617, 'ABC', '2013-06-17', 313, 12.93, 4047.09);
INSERT INTO #c
VALUES
(03130308, 'ABC', '2013-03-08', -157, 13.17, -2067.69);
INSERT INTO #c
VALUES
(04130516, 'GHI', '2013-05-16', 160, 34.48, 5516.8);
INSERT INTO #c
VALUES
(05130706, 'XYZ', '2013-07-06', -170, 23.46, -3988.2);
INSERT INTO #c
VALUES
(06130924, 'GHI', '2013-09-24', 328, 34.95, 11463.6);
INSERT INTO #c
VALUES
(07130722, 'ABC', '2013-07-22', 599, 13.65, 8176.35);
INSERT INTO #c
VALUES
(08131231, 'ABC', '2013-12-31', -145, 13.19, -1912.55);
INSERT INTO #c
VALUES
(09131025, 'XYZ', '2013-10-25', -153, 24.31, -3719.43);
INSERT INTO #c
VALUES
(10130908, 'ABC', '2013-09-08', -386, 13.65, -5268.9);
INSERT INTO #c
VALUES
(11130906, 'XYZ', '2013-09-06', -13, 23.97, -311.61);
INSERT INTO #c
VALUES
(12130621, 'ABC', '2013-06-21', -326, 12.73, -4149.98);
INSERT INTO #c
VALUES
(13131221, 'GHI', '2013-12-21', 72, 34.38, 2475.36);
INSERT INTO #c
VALUES
(14130705, 'XYZ', '2013-07-05', -277, 25.01, -6927.77);
INSERT INTO #c
VALUES
(15130307, 'GHI', '2013-03-07', 559, 35.21, 19682.39);
INSERT INTO #c
VALUES
(16131107, 'ABC', '2013-11-07', 27, 12.68, 342.36);
INSERT INTO #c
VALUES
(17130924, 'GHI', '2013-09-24', -291, 35.69, -10385.79);
INSERT INTO #c
VALUES
(18140125, 'GHI', '2014-01-25', -78, 35.46, -2765.88);
INSERT INTO #c
VALUES
(19130516, 'XYZ', '2013-05-16', 315, 23.57, 7424.55);
INSERT INTO #c
VALUES
(20130518, 'ABC', '2013-05-18', 298, 13.23, 3942.54);
INSERT INTO #c
VALUES
(21131103, 'XYZ', '2013-11-03', -326, 23.24, -7576.24);
INSERT INTO #c
VALUES
(22131012, 'XYZ', '2013-10-12', 596, 23.16, 13803.36);
INSERT INTO #c
VALUES
(23130619, 'XYZ', '2013-06-19', 296, 23.46, 6944.16);
INSERT INTO #c
VALUES
(24130418, 'XYZ', '2013-04-18', 275, 24.83, 6828.25);
INSERT INTO #c
VALUES
(25130408, 'ABC', '2013-04-08', 298, 12.98, 3868.04);
INSERT INTO #c
VALUES
(26130320, 'ABC', '2013-03-20', -92, 13.64, -1254.88);
INSERT INTO #c
VALUES
(27130906, 'XYZ', '2013-09-06', -147, 23.81, -3500.07);
INSERT INTO #c
VALUES
(28131209, 'XYZ', '2013-12-09', 315, 24.46, 7704.9);
INSERT INTO #c
VALUES
(29130602, 'XYZ', '2013-06-02', 114, 24.29, 2769.06);
INSERT INTO #c
VALUES
(30130519, 'XYZ', '2013-05-19', 467, 23.15, 10811.05);
INSERT INTO #c
VALUES
(31140205, 'XYZ', '2014-02-05', 42, 24.39, 1024.38);
INSERT INTO #c
VALUES
(32130310, 'ABC', '2013-03-10', -63, 12.61, -794.43);
INSERT INTO #c
VALUES
(33140102, 'XYZ', '2014-01-02', -196, 22.98, -4504.08);
INSERT INTO #c
VALUES
(34130507, 'XYZ', '2013-05-07', 55, 23.43, 1288.65);
INSERT INTO #c
VALUES
(35130321, 'XYZ', '2013-03-21', 275, 24.83, 6828.25);
INSERT INTO #c
VALUES
(36130917, 'XYZ', '2013-09-17', 92, 24.6, 2263.2);
INSERT INTO #c
VALUES
(37130220, 'XYZ', '2013-02-20', 528, 23.54, 12429.12);
INSERT INTO #c
VALUES
(38130311, 'XYZ', '2013-03-11', -193, 24.9, -4805.7);
INSERT INTO #c
VALUES
(39130908, 'ABC', '2013-09-08', 490, 12.69, 6218.1);
INSERT INTO #c
VALUES
(40131013, 'XYZ', '2013-10-13', 359, 23.91, 8583.69);
INSERT INTO #c
VALUES
(41130310, 'ABC', '2013-03-10', 463, 13.38, 6194.94);
INSERT INTO #c
VALUES
(42131011, 'XYZ', '2013-10-11', -250, 23.12, -5780);
INSERT INTO #c
VALUES
(43130521, 'GHI', '2013-05-21', -174, 34.2, -5950.8);
INSERT INTO #c
VALUES
(44130227, 'XYZ', '2013-02-27', 357, 22.86, 8161.02);
INSERT INTO #c
VALUES
(45131030, 'XYZ', '2013-10-30', -350, 23.36, -8176);
INSERT INTO #c
VALUES
(46130301, 'ABC', '2013-03-01', 157, 13.01, 2042.57);
INSERT INTO #c
VALUES
(47130619, 'XYZ', '2013-06-19', 413, 25.18, 10399.34);
INSERT INTO #c
VALUES
(48130430, 'ABC', '2013-04-30', 229, 13.32, 3050.28);
INSERT INTO #c
VALUES
(49130508, 'ABC', '2013-05-08', 238, 12.79, 3044.02);
INSERT INTO #c
VALUES
(50131103, 'GHI', '2013-11-03', -246, 35.61, -8760.06);
INSERT INTO #c
VALUES
(51131206, 'GHI', '2013-12-06', 85, 33.64, 2859.4);
INSERT INTO #c
VALUES
(52131014, 'GHI', '2013-10-14', -91, 33.12, -3013.92);
INSERT INTO #c
VALUES
(53140102, 'GHI', '2014-01-02', 396, 35.52, 14065.92);
INSERT INTO #c
VALUES
(54130831, 'XYZ', '2013-08-31', -61, 23.23, -1417.03);
INSERT INTO #c
VALUES
(55130630, 'XYZ', '2013-06-30', -272, 23.45, -6378.4);
INSERT INTO #c
VALUES
(56130419, 'GHI', '2013-04-19', 416, 34.46, 14335.36);
INSERT INTO #c
VALUES
(57130813, 'XYZ', '2013-08-13', -163, 23.65, -3854.95);
INSERT INTO #c
VALUES
(58130722, 'XYZ', '2013-07-22', -88, 24.64, -2168.32);
INSERT INTO #c
VALUES
(59130320, 'XYZ', '2013-03-20', -20, 23.59, -471.8);
INSERT INTO #c
VALUES
(60130419, 'XYZ', '2013-04-19', 277, 22.83, 6323.91);
INSERT INTO #c
VALUES
(61130916, 'ABC', '2013-09-16', -202, 12.94, -2613.88);
INSERT INTO #c
VALUES
(62131027, 'XYZ', '2013-10-27', -248, 24.71, -6128.08);
INSERT INTO #c
VALUES
(63130806, 'GHI', '2013-08-06', 445, 33.5, 14907.5);
INSERT INTO #c
VALUES
(64140109, 'XYZ', '2014-01-09', -253, 24.46, -6188.38);
INSERT INTO #c
VALUES
(65131227, 'GHI', '2013-12-27', 376, 33.49, 12592.24);
INSERT INTO #c
VALUES
(66140203, 'XYZ', '2014-02-03', 459, 23.7, 10878.3);
INSERT INTO #c
VALUES
(67130302, 'XYZ', '2013-03-02', 9, 24.04, 216.36);
INSERT INTO #c
VALUES
(68130223, 'ABC', '2013-02-23', 238, 12.78, 3041.64);
INSERT INTO #c
VALUES
(69130403, 'GHI', '2013-04-03', -151, 33.16, -5007.16);
INSERT INTO #c
VALUES
(70130702, 'GHI', '2013-07-02', -162, 35.48, -5747.76);
INSERT INTO #c
VALUES
(71130731, 'ABC', '2013-07-31', 79, 13.55, 1070.45);
INSERT INTO #c
VALUES
(72140204, 'XYZ', '2014-02-04', -208, 24.36, -5066.88);
INSERT INTO #c
VALUES
(73131028, 'GHI', '2013-10-28', -46, 34.65, -1593.9);
INSERT INTO #c
VALUES
(74130619, 'XYZ', '2013-06-19', 202, 22.95, 4635.9);
INSERT INTO #c
VALUES
(75131216, 'GHI', '2013-12-16', 500, 33.55, 16775);
INSERT INTO #c
VALUES
(76131009, 'ABC', '2013-10-09', 249, 13.47, 3354.03);
INSERT INTO #c
VALUES
(77130825, 'GHI', '2013-08-25', 272, 33.86, 9209.92);
INSERT INTO #c
VALUES
(78140112, 'XYZ', '2014-01-12', 332, 24.28, 8060.96);
INSERT INTO #c
VALUES
(79131223, 'XYZ', '2013-12-23', -376, 25.12, -9445.12);
INSERT INTO #c
VALUES
(80140126, 'XYZ', '2014-01-26', 404, 24.23, 9788.92);
INSERT INTO #c
VALUES
(81131123, 'GHI', '2013-11-23', 187, 33.86, 6331.82);
INSERT INTO #c
VALUES
(82140131, 'XYZ', '2014-01-31', 548, 23.65, 12960.2);
INSERT INTO #c
VALUES
(83130428, 'XYZ', '2013-04-28', 142, 23.13, 3284.46);
INSERT INTO #c
VALUES
(84140104, 'XYZ', '2014-01-04', 261, 24.46, 6384.06);
INSERT INTO #c
VALUES
(85131002, 'GHI', '2013-10-02', -295, 32.51, -9590.45);
INSERT INTO #c
VALUES
(86131114, 'ABC', '2013-11-14', -386, 12.4, -4786.4);
INSERT INTO #c
VALUES
(87131116, 'GHI', '2013-11-16', -320, 34.16, -10931.2);
INSERT INTO #c
VALUES
(88131009, 'ABC', '2013-10-09', 187, 13.36, 2498.32);
INSERT INTO #c
VALUES
(89130611, 'ABC', '2013-06-11', 130, 13.02, 1692.6);
INSERT INTO #c
VALUES
(90130430, 'ABC', '2013-04-30', -191, 13.49, -2576.59);
INSERT INTO #c
VALUES
(91130615, 'XYZ', '2013-06-15', 545, 23.88, 13014.6);
INSERT INTO #c
VALUES
(92130924, 'XYZ', '2013-09-24', -248, 24.58, -6095.84);
INSERT INTO #c
VALUES
(93130622, 'ABC', '2013-06-22', -227, 13.47, -3057.69);
INSERT INTO #c
VALUES
(94131117, 'GHI', '2013-11-17', -92, 32.65, -3003.8);
INSERT INTO #c
VALUES
(95130908, 'GHI', '2013-09-08', 103, 35.42, 3648.26);
INSERT INTO #c
VALUES
(96130716, 'GHI', '2013-07-16', -347, 35.29, -12245.63);
INSERT INTO #c
VALUES
(97131125, 'XYZ', '2013-11-25', -278, 23.18, -6444.04);
INSERT INTO #c
VALUES
(98130413, 'XYZ', '2013-04-13', 243, 24.25, 5892.75);
INSERT INTO #c
VALUES
(99130515, 'XYZ', '2013-05-15', 97, 25.13, 2437.61);
--Calculate the WAC values
SELECT *,
       wct.WAC(   qty,                                                           
                 --@Qty
                  price_extended,                                                
                            --@Cost
                  'QTY',                                                         
                            --@RV
                  2,                                                             
                            --@Round
                  ROW_NUMBER() OVER (PARTITION BY sym ORDER BY sym, tDate, trn), 
                            --@RowNum
                  0                                                              
                            --@Id
              ) as [Quantity on Hand],
       wct.WAC(   qty,                                                           
                 --@Qty
                  price_extended,                                                
                            --@Cost
                  'EV',                                                          
                            --@RV
                  2,                                                             
                            --@Round
                  ROW_NUMBER() OVER (PARTITION BY sym ORDER BY sym, tDate, trn), 
                            --@RowNum
                  1                                                              
                            --@Id
              ) as [Inventory Value]
FROM #c
ORDER BY sym,
         tDate,
         trn;
DROP TABLE #c;

This produces the following result.

Using the #c table, we can generate a report showing the gross margin on sales and cost of goods sold.

SELECT sym,
       tDate,
       qty,
       price_extended,
       wct.WAC(qty, price_extended, 'GM', 2, ROW_NUMBER() OVER (PARTITION BY sym 
                 ORDER BY sym, tDate, trn), 0) as [Gross Margin],
       wct.WAC(qty, price_extended, 'COGS', 2, ROW_NUMBER() OVER (PARTITION BY sym 
                 ORDER BY sym, tDate, trn), 1) as [COGS]
FROM #c
ORDER BY sym,
         tDate,
         trn;

This produces the following result.

If we had wanted to calculate the cumulative values instead we only need the change the return value (@RV) passed into the function.

SELECT sym,
       tDate,
       qty,
       price_extended,
       wct.WAC(qty, price_extended, 'GMC', 2, ROW_NUMBER() OVER (PARTITION BY sym 
                 ORDER BY sym, tDate, trn), 0) as [Gross Margin],
       wct.WAC(qty, price_extended, 'COGSC', 2, ROW_NUMBER() OVER (PARTITION BY 
                 sym ORDER BY sym, tDate, trn), 1) as [COGS]
FROM #c
ORDER BY sym,
         tDate,
         trn;

This produces the following result.

In this example, we will look at a simple FX blotter. In FX trading, it is not at all unusual to switch from a long currency position to a short currency position during the course of a day. Additionally, if you are trading something like the US dollars against the Euro, if you are long US dollars, you are short the Euro. Thus, it's critical to pay attention to the sign. We will create another table, #fx, and populate it with some data.

CREATE TABLE #fx
(
    rn int,
    ccy char(3),
    amt_ccy money,
    rate float,
    ctr char(3),
    amt_ctr money,
    PRIMARY KEY (rn)
);
--Populate the table with some data
INSERT INTO #fx
VALUES
(1, 'GBP', 8000000, 1.619, 'USD', -12952000);
INSERT INTO #fx
VALUES
(2, 'GBP', -10000000, 1.62, 'USD', 16200000);
INSERT INTO #fx
VALUES
(3, 'GBP', -4000000, 1.613, 'USD', 6452000);
INSERT INTO #fx
VALUES
(4, 'GBP', 7000000, 1.618, 'USD', -11326000);
INSERT INTO #fx
VALUES
(5, 'GBP', 6000000, 1.623, 'USD', -9738000);
INSERT INTO #fx
VALUES
(6, 'GBP', -5000000, 1.618, 'USD', 8090000);
INSERT INTO #fx
VALUES
(7, 'GBP', -10000000, 1.602, 'USD', 16020000);
INSERT INTO #fx
VALUES
(8, 'GBP', 2000000, 1.608, 'USD', -3216000);
INSERT INTO #fx
VALUES
(9, 'GBP', -2000000, 1.602, 'USD', 3204000);
INSERT INTO #fx
VALUES
(10, 'GBP', 10000000, 1.626, 'USD', -16260000);

In this SQL we will keep track of GBP position, the USD position, and the P/L.

SELECT rn,
       ccy,
       amt_ccy,
       rate,
       ctr,
       amt_ctr,
       wct.WAC(amt_ccy, -amt_ctr, 'QTY', 2, ROW_NUMBER() OVER (ORDER BY rn), 0) 
                 as [GBP Position],
       wct.WAC(amt_ccy, -amt_ctr, 'EV', 2, ROW_NUMBER() OVER (ORDER BY rn), 1) as 
                 [USD Position],
       wct.WAC(amt_ccy, -amt_ctr, 'GM', 2, ROW_NUMBER() OVER (ORDER BY rn), 2) as 
                 [P/L]
FROM #fx;

This produces the following result.