Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server weighted-average cost (WAC) function


WAC

Updated: 31 Dec 2013


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:

'Q','QTY'
Quantity on hand
'B','EV','EB'
Inventory value
'G','GM'
Gross margin
'C','COG','COGS'
Cost of goods sold
'U','UP'
(Average) Unit price
'L','LP'
Last price
'CC','COGC','COGSC'
Cumulative cost of goods sold
'GC','GMC'
Cumulative gross margin
'GP','GMP'
Gross margin percentage
'CGP','CGMP'
Cumulative gross margin percentage

@Round
the number of decimals places to store the result. @Round is only used in the calculation of the gross margin. @Round is an expression of type int or of a type that can be implicitly converted to int.
@RowNum
the number of the row within the group for which the sum is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.
@Id
a unique identifier for the 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.
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.
Example
In the following examples, we calculate FIFO inventory values for stock trades in the ABC, XYZ, and GHI companies. We will create a temporary table, #c and populate it with some data. We can be either short or long the shares at any point in time.
--Create the temporary table
CREATE TABLE #c   (
      trn               int,
      sym               char(3),
      tDate             date,
      qty               money,
      price_unit        money,
      price_extended    money,
      PRIMARY KEY (trn)
      )
--Populate the table with some data
INSERT INTO #c VALUES (01131019,'XYZ','2013-10-19',424,25.13,10655.12)
INSERT INTO #c VALUES (02130617,'ABC','2013-06-17',313,12.93,4047.09)
INSERT INTO #c VALUES (03130308,'ABC','2013-03-08',-157,13.17,-2067.69)
INSERT INTO #c VALUES (04130516,'GHI','2013-05-16',160,34.48,5516.8)
INSERT INTO #c VALUES (05130706,'XYZ','2013-07-06',-170,23.46,-3988.2)
INSERT INTO #c VALUES (06130924,'GHI','2013-09-24',328,34.95,11463.6)
INSERT INTO #c VALUES (07130722,'ABC','2013-07-22',599,13.65,8176.35)
INSERT INTO #c VALUES (08131231,'ABC','2013-12-31',-145,13.19,-1912.55)
INSERT INTO #c VALUES (09131025,'XYZ','2013-10-25',-153,24.31,-3719.43)
INSERT INTO #c VALUES (10130908,'ABC','2013-09-08',-386,13.65,-5268.9)
INSERT INTO #c VALUES (11130906,'XYZ','2013-09-06',-13,23.97,-311.61)
INSERT INTO #c VALUES (12130621,'ABC','2013-06-21',-326,12.73,-4149.98)
INSERT INTO #c VALUES (13131221,'GHI','2013-12-21',72,34.38,2475.36)
INSERT INTO #c VALUES (14130705,'XYZ','2013-07-05',-277,25.01,-6927.77)
INSERT INTO #c VALUES (15130307,'GHI','2013-03-07',559,35.21,19682.39)
INSERT INTO #c VALUES (16131107,'ABC','2013-11-07',27,12.68,342.36)
INSERT INTO #c VALUES (17130924,'GHI','2013-09-24',-291,35.69,-10385.79)
INSERT INTO #c VALUES (18140125,'GHI','2014-01-25',-78,35.46,-2765.88)
INSERT INTO #c VALUES (19130516,'XYZ','2013-05-16',315,23.57,7424.55)
INSERT INTO #c VALUES (20130518,'ABC','2013-05-18',298,13.23,3942.54)
INSERT INTO #c VALUES (21131103,'XYZ','2013-11-03',-326,23.24,-7576.24)
INSERT INTO #c VALUES (22131012,'XYZ','2013-10-12',596,23.16,13803.36)
INSERT INTO #c VALUES (23130619,'XYZ','2013-06-19',296,23.46,6944.16)
INSERT INTO #c VALUES (24130418,'XYZ','2013-04-18',275,24.83,6828.25)
INSERT INTO #c VALUES (25130408,'ABC','2013-04-08',298,12.98,3868.04)
INSERT INTO #c VALUES (26130320,'ABC','2013-03-20',-92,13.64,-1254.88)
INSERT INTO #c VALUES (27130906,'XYZ','2013-09-06',-147,23.81,-3500.07)
INSERT INTO #c VALUES (28131209,'XYZ','2013-12-09',315,24.46,7704.9)
INSERT INTO #c VALUES (29130602,'XYZ','2013-06-02',114,24.29,2769.06)
INSERT INTO #c VALUES (30130519,'XYZ','2013-05-19',467,23.15,10811.05)
INSERT INTO #c VALUES (31140205,'XYZ','2014-02-05',42,24.39,1024.38)
INSERT INTO #c VALUES (32130310,'ABC','2013-03-10',-63,12.61,-794.43)
INSERT INTO #c VALUES (33140102,'XYZ','2014-01-02',-196,22.98,-4504.08)
INSERT INTO #c VALUES (34130507,'XYZ','2013-05-07',55,23.43,1288.65)
INSERT INTO #c VALUES (35130321,'XYZ','2013-03-21',275,24.83,6828.25)
INSERT INTO #c VALUES (36130917,'XYZ','2013-09-17',92,24.6,2263.2)
INSERT INTO #c VALUES (37130220,'XYZ','2013-02-20',528,23.54,12429.12)
INSERT INTO #c VALUES (38130311,'XYZ','2013-03-11',-193,24.9,-4805.7)
INSERT INTO #c VALUES (39130908,'ABC','2013-09-08',490,12.69,6218.1)
INSERT INTO #c VALUES (40131013,'XYZ','2013-10-13',359,23.91,8583.69)
INSERT INTO #c VALUES (41130310,'ABC','2013-03-10',463,13.38,6194.94)
INSERT INTO #c VALUES (42131011,'XYZ','2013-10-11',-250,23.12,-5780)
INSERT INTO #c VALUES (43130521,'GHI','2013-05-21',-174,34.2,-5950.8)
INSERT INTO #c VALUES (44130227,'XYZ','2013-02-27',357,22.86,8161.02)
INSERT INTO #c VALUES (45131030,'XYZ','2013-10-30',-350,23.36,-8176)
INSERT INTO #c VALUES (46130301,'ABC','2013-03-01',157,13.01,2042.57)
INSERT INTO #c VALUES (47130619,'XYZ','2013-06-19',413,25.18,10399.34)
INSERT INTO #c VALUES (48130430,'ABC','2013-04-30',229,13.32,3050.28)
INSERT INTO #c VALUES (49130508,'ABC','2013-05-08',238,12.79,3044.02)
INSERT INTO #c VALUES (50131103,'GHI','2013-11-03',-246,35.61,-8760.06)
INSERT INTO #c VALUES (51131206,'GHI','2013-12-06',85,33.64,2859.4)
INSERT INTO #c VALUES (52131014,'GHI','2013-10-14',-91,33.12,-3013.92)
INSERT INTO #c VALUES (53140102,'GHI','2014-01-02',396,35.52,14065.92)
INSERT INTO #c VALUES (54130831,'XYZ','2013-08-31',-61,23.23,-1417.03)
INSERT INTO #c VALUES (55130630,'XYZ','2013-06-30',-272,23.45,-6378.4)
INSERT INTO #c VALUES (56130419,'GHI','2013-04-19',416,34.46,14335.36)
INSERT INTO #c VALUES (57130813,'XYZ','2013-08-13',-163,23.65,-3854.95)
INSERT INTO #c VALUES (58130722,'XYZ','2013-07-22',-88,24.64,-2168.32)
INSERT INTO #c VALUES (59130320,'XYZ','2013-03-20',-20,23.59,-471.8)
INSERT INTO #c VALUES (60130419,'XYZ','2013-04-19',277,22.83,6323.91)
INSERT INTO #c VALUES (61130916,'ABC','2013-09-16',-202,12.94,-2613.88)
INSERT INTO #c VALUES (62131027,'XYZ','2013-10-27',-248,24.71,-6128.08)
INSERT INTO #c VALUES (63130806,'GHI','2013-08-06',445,33.5,14907.5)
INSERT INTO #c VALUES (64140109,'XYZ','2014-01-09',-253,24.46,-6188.38)
INSERT INTO #c VALUES (65131227,'GHI','2013-12-27',376,33.49,12592.24)
INSERT INTO #c VALUES (66140203,'XYZ','2014-02-03',459,23.7,10878.3)
INSERT INTO #c VALUES (67130302,'XYZ','2013-03-02',9,24.04,216.36)
INSERT INTO #c VALUES (68130223,'ABC','2013-02-23',238,12.78,3041.64)
INSERT INTO #c VALUES (69130403,'GHI','2013-04-03',-151,33.16,-5007.16)
INSERT INTO #c VALUES (70130702,'GHI','2013-07-02',-162,35.48,-5747.76)
INSERT INTO #c VALUES (71130731,'ABC','2013-07-31',79,13.55,1070.45)
INSERT INTO #c VALUES (72140204,'XYZ','2014-02-04',-208,24.36,-5066.88)
INSERT INTO #c VALUES (73131028,'GHI','2013-10-28',-46,34.65,-1593.9)
INSERT INTO #c VALUES (74130619,'XYZ','2013-06-19',202,22.95,4635.9)
INSERT INTO #c VALUES (75131216,'GHI','2013-12-16',500,33.55,16775)
INSERT INTO #c VALUES (76131009,'ABC','2013-10-09',249,13.47,3354.03)
INSERT INTO #c VALUES (77130825,'GHI','2013-08-25',272,33.86,9209.92)
INSERT INTO #c VALUES (78140112,'XYZ','2014-01-12',332,24.28,8060.96)
INSERT INTO #c VALUES (79131223,'XYZ','2013-12-23',-376,25.12,-9445.12)
INSERT INTO #c VALUES (80140126,'XYZ','2014-01-26',404,24.23,9788.92)
INSERT INTO #c VALUES (81131123,'GHI','2013-11-23',187,33.86,6331.82)
INSERT INTO #c VALUES (82140131,'XYZ','2014-01-31',548,23.65,12960.2)
INSERT INTO #c VALUES (83130428,'XYZ','2013-04-28',142,23.13,3284.46)
INSERT INTO #c VALUES (84140104,'XYZ','2014-01-04',261,24.46,6384.06)
INSERT INTO #c VALUES (85131002,'GHI','2013-10-02',-295,32.51,-9590.45)
INSERT INTO #c VALUES (86131114,'ABC','2013-11-14',-386,12.4,-4786.4)
INSERT INTO #c VALUES (87131116,'GHI','2013-11-16',-320,34.16,-10931.2)
INSERT INTO #c VALUES (88131009,'ABC','2013-10-09',187,13.36,2498.32)
INSERT INTO #c VALUES (89130611,'ABC','2013-06-11',130,13.02,1692.6)
INSERT INTO #c VALUES (90130430,'ABC','2013-04-30',-191,13.49,-2576.59)
INSERT INTO #c VALUES (91130615,'XYZ','2013-06-15',545,23.88,13014.6)
INSERT INTO #c VALUES (92130924,'XYZ','2013-09-24',-248,24.58,-6095.84)
INSERT INTO #c VALUES (93130622,'ABC','2013-06-22',-227,13.47,-3057.69)
INSERT INTO #c VALUES (94131117,'GHI','2013-11-17',-92,32.65,-3003.8)
INSERT INTO #c VALUES (95130908,'GHI','2013-09-08',103,35.42,3648.26)
INSERT INTO #c VALUES (96130716,'GHI','2013-07-16',-347,35.29,-12245.63)
INSERT INTO #c VALUES (97131125,'XYZ','2013-11-25',-278,23.18,-6444.04)
INSERT INTO #c VALUES (98130413,'XYZ','2013-04-13',243,24.25,5892.75)
INSERT INTO #c VALUES (99130515,'XYZ','2013-05-15',97,25.13,2437.61)
--Calculate the 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.
        trn sym tDate       qty price_unit price_extended On Hand    Value
----------- ---- ---------- ---- ---------- -------------- ------- --------
   68130223  ABC 2013-02-23  238      12.78        3041.64     238  3041.64
   46130301  ABC 2013-03-01  157      13.01        2042.57     395  5084.21
    3130308  ABC 2013-03-08 -157      13.17       -2067.69     238  3063.40
   32130310  ABC 2013-03-10  -63      12.61        -794.43     175  2252.50
   41130310  ABC 2013-03-10  463      13.38        6194.94     638  8447.44
   26130320  ABC 2013-03-20  -92      13.64       -1254.88     546  7229.31
   25130408  ABC 2013-04-08  298      12.98        3868.04     844 11097.35
   48130430  ABC 2013-04-30  229      13.32        3050.28    1073 14147.63
   90130430  ABC 2013-04-30 -191      13.49       -2576.59     882 11629.27
   49130508  ABC 2013-05-08  238      12.79        3044.02    1120 14673.29
   20130518  ABC 2013-05-18  298      13.23        3942.54    1418 18615.83
   89130611  ABC 2013-06-11  130      13.02        1692.60    1548 20308.43
    2130617  ABC 2013-06-17  313      12.93        4047.09    1861 24355.52
   12130621  ABC 2013-06-21 -326      12.73       -4149.98    1535 20089.05
   93130622  ABC 2013-06-22 -227      13.47       -3057.69    1308 17118.23
    7130722  ABC 2013-07-22  599      13.65        8176.35    1907 25294.58
   71130731  ABC 2013-07-31   79      13.55        1070.45    1986 26365.03
   10130908  ABC 2013-09-08 -386      13.65       -5268.90    1600 21240.71
   39130908  ABC 2013-09-08  490      12.69        6218.10    2090 27458.81
   61130916  ABC 2013-09-16 -202      12.94       -2613.88    1888 24804.90
   76131009  ABC 2013-10-09  249      13.47        3354.03    2137 28158.93
   88131009  ABC 2013-10-09  187      13.36        2498.32    2324 30657.25
   16131107  ABC 2013-11-07   27      12.68         342.36    2351 30999.61
   86131114  ABC 2013-11-14 -386      12.40       -4786.40    1965 25909.92
    8131231  ABC 2013-12-31 -145      13.19       -1912.55    1820 23997.99
   15130307  GHI 2013-03-07  559      35.21       19682.39     559 19682.39
   69130403  GHI 2013-04-03 -151      33.16       -5007.16     408 14365.68
   56130419  GHI 2013-04-19  416      34.46       14335.36     824 28701.04
    4130516  GHI 2013-05-16  160      34.48        5516.80     984 34217.84
   43130521  GHI 2013-05-21 -174      34.20       -5950.80     810 28167.12
   70130702  GHI 2013-07-02 -162      35.48       -5747.76     648 22533.70
   96130716  GHI 2013-07-16 -347      35.29      -12245.63     301 10467.04
   63130806  GHI 2013-08-06  445      33.50       14907.50     746 25374.54
   77130825  GHI 2013-08-25  272      33.86        9209.92    1018 34584.46
   95130908  GHI 2013-09-08  103      35.42        3648.26    1121 38232.72
    6130924  GHI 2013-09-24  328      34.95       11463.60    1449 49696.32
   17130924  GHI 2013-09-24 -291      35.69      -10385.79    1158 39715.90
   85131002  GHI 2013-10-02 -295      32.51       -9590.45     863 29598.29
   52131014  GHI 2013-10-14  -91      33.12       -3013.92     772 26477.27
   73131028  GHI 2013-10-28  -46      34.65       -1593.90     726 24899.61
   50131103  GHI 2013-11-03 -246      35.61       -8760.06     480 16462.55
   87131116  GHI 2013-11-16 -320      34.16      -10931.20     160  5487.52
   94131117  GHI 2013-11-17  -92      32.65       -3003.80      68  2332.20
   81131123  GHI 2013-11-23  187      33.86        6331.82     255  8664.02
   51131206  GHI 2013-12-06   85      33.64        2859.40     340 11523.42
   75131216  GHI 2013-12-16  500      33.55       16775.00     840 28298.42
   13131221  GHI 2013-12-21   72      34.38        2475.36     912 30773.78
   65131227  GHI 2013-12-27  376      33.49       12592.24    1288 43366.02
   53140102  GHI 2014-01-02  396      35.52       14065.92    1684 57431.94
   18140125  GHI 2014-01-25  -78      35.46       -2765.88    1606 54771.79
   37130220  XYZ 2013-02-20  528      23.54       12429.12     528 12429.12
   44130227  XYZ 2013-02-27  357      22.86        8161.02     885 20590.14
   67130302  XYZ 2013-03-02    9      24.04         216.36     894 20806.50
   38130311  XYZ 2013-03-11 -193      24.90       -4805.70     701 16314.72
   59130320  XYZ 2013-03-20  -20      23.59        -471.80     681 15849.25
   35130321  XYZ 2013-03-21  275      24.83        6828.25     956 22677.50
   98130413  XYZ 2013-04-13  243      24.25        5892.75    1199 28570.25
   24130418  XYZ 2013-04-18  275      24.83        6828.25    1474 35398.50
   60130419  XYZ 2013-04-19  277      22.83        6323.91    1751 41722.41
   83130428  XYZ 2013-04-28  142      23.13        3284.46    1893 45006.87
   34130507  XYZ 2013-05-07   55      23.43        1288.65    1948 46295.52
   99130515  XYZ 2013-05-15   97      25.13        2437.61    2045 48733.13
   19130516  XYZ 2013-05-16  315      23.57        7424.55    2360 56157.68
   30130519  XYZ 2013-05-19  467      23.15       10811.05    2827 66968.73
   29130602  XYZ 2013-06-02  114      24.29        2769.06    2941 69737.79
   91130615  XYZ 2013-06-15  545      23.88       13014.60    3486 82752.39
   23130619  XYZ 2013-06-19  296      23.46        6944.16    3782 89696.55
   47130619  XYZ 2013-06-19  413      25.18       10399.34    4195 100095.8
   74130619  XYZ 2013-06-19  202      22.95        4635.90    4397 104731.7
   55130630  XYZ 2013-06-30 -272      23.45       -6378.40    4125 98253.04
   14130705  XYZ 2013-07-05 -277      25.01       -6927.77    3848 91655.20
    5130706  XYZ 2013-07-06 -170      23.46       -3988.20    3678 87605.98
   58130722  XYZ 2013-07-22  -88      24.64       -2168.32    3590 85509.92
   57130813  XYZ 2013-08-13 -163      23.65       -3854.95    3427 81627.44
   54130831  XYZ 2013-08-31  -61      23.23       -1417.03    3366 80174.49
   11130906  XYZ 2013-09-06  -13      23.97        -311.61    3353 79864.84
   27130906  XYZ 2013-09-06 -147      23.81       -3500.07    3206 76363.46
   36130917  XYZ 2013-09-17   92      24.60        2263.20    3298 78626.66
   92130924  XYZ 2013-09-24 -248      24.58       -6095.84    3050 72714.16
   42131011  XYZ 2013-10-11 -250      23.12       -5780.00    2800 66753.98
   22131012  XYZ 2013-10-12  596      23.16       13803.36    3396 80557.34
   40131013  XYZ 2013-10-13  359      23.91        8583.69    3755 89141.03
    1131019  XYZ 2013-10-19  424      25.13       10655.12    4179 99796.15
    9131025  XYZ 2013-10-25 -153      24.31       -3719.43    4026 96142.45
   62131027  XYZ 2013-10-27 -248      24.71       -6128.08    3778 90220.11
   45131030  XYZ 2013-10-30 -350      23.36       -8176.00    3428 81861.97
   21131103  XYZ 2013-11-03 -326      23.24       -7576.24    3102 74076.96
   97131125  XYZ 2013-11-25 -278      23.18       -6444.04    2824 67438.21
   28131209  XYZ 2013-12-09  315      24.46        7704.90    3139 75143.11
   79131223  XYZ 2013-12-23 -376      25.12       -9445.12    2763 66142.22
   33140102  XYZ 2014-01-02 -196      22.98       -4504.08    2567 61450.26
   84140104  XYZ 2014-01-04  261      24.46        6384.06    2828 67834.32
   64140109  XYZ 2014-01-09 -253      24.46       -6188.38    2575 61765.69
   78140112  XYZ 2014-01-12  332      24.28        8060.96    2907 69826.65
   80140126  XYZ 2014-01-26  404      24.23        9788.92    3311 79615.57
   82140131  XYZ 2014-01-31  548      23.65       12960.20    3859 92575.77
   66140203  XYZ 2014-02-03  459      23.70       10878.30    4318 103454.0
   72140204  XYZ 2014-02-04 -208      24.36       -5066.88    4110 98470.64
   31140205  XYZ 2014-02-05   42      24.39        1024.38    4152 99495.02


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.
sym tDate       qty price_unit price_extended Gross Margin     COGS
---- ---------- ---- ---------- -------------- ------------ --------
 ABC 2013-02-23  238      12.78        3041.64          .00      .00
 ABC 2013-03-01  157      13.01        2042.57          .00      .00
 ABC 2013-03-08 -157      13.17       -2067.69        46.88 -2020.81
 ABC 2013-03-10  -63      12.61        -794.43       -16.47  -810.90
 ABC 2013-03-10  463      13.38        6194.94          .00      .00
 ABC 2013-03-20  -92      13.64       -1254.88        36.75 -1218.13
 ABC 2013-04-08  298      12.98        3868.04          .00      .00
 ABC 2013-04-30  229      13.32        3050.28          .00      .00
 ABC 2013-04-30 -191      13.49       -2576.59        58.23 -2518.36
 ABC 2013-05-08  238      12.79        3044.02          .00      .00
 ABC 2013-05-18  298      13.23        3942.54          .00      .00
 ABC 2013-06-11  130      13.02        1692.60          .00      .00
 ABC 2013-06-17  313      12.93        4047.09          .00      .00
 ABC 2013-06-21 -326      12.73       -4149.98      -116.49 -4266.47
 ABC 2013-06-22 -227      13.47       -3057.69        86.87 -2970.82
 ABC 2013-07-22  599      13.65        8176.35          .00      .00
 ABC 2013-07-31   79      13.55        1070.45          .00      .00
 ABC 2013-09-08 -386      13.65       -5268.90       144.58 -5124.32
 ABC 2013-09-08  490      12.69        6218.10          .00      .00
 ABC 2013-09-16 -202      12.94       -2613.88       -40.03 -2653.91
 ABC 2013-10-09  249      13.47        3354.03          .00      .00
 ABC 2013-10-09  187      13.36        2498.32          .00      .00
 ABC 2013-11-07   27      12.68         342.36          .00      .00
 ABC 2013-11-14 -386      12.40       -4786.40      -303.29 -5089.69
 ABC 2013-12-31 -145      13.19       -1912.55          .62 -1911.93
 GHI 2013-03-07  559      35.21       19682.39          .00      .00
 GHI 2013-04-03 -151      33.16       -5007.16      -309.55 -5316.71
 GHI 2013-04-19  416      34.46       14335.36          .00      .00
 GHI 2013-05-16  160      34.48        5516.80          .00      .00
 GHI 2013-05-21 -174      34.20       -5950.80       -99.92 -6050.72
 GHI 2013-07-02 -162      35.48       -5747.76       114.34 -5633.42
 GHI 2013-07-16 -347      35.29      -12245.63       178.97 -12066.6
 GHI 2013-08-06  445      33.50       14907.50          .00      .00
 GHI 2013-08-25  272      33.86        9209.92          .00      .00
 GHI 2013-09-08  103      35.42        3648.26          .00      .00
 GHI 2013-09-24  328      34.95       11463.60          .00      .00
 GHI 2013-09-24 -291      35.69      -10385.79       405.37 -9980.42
 GHI 2013-10-02 -295      32.51       -9590.45      -527.16 -10117.6
 GHI 2013-10-14  -91      33.12       -3013.92      -107.10 -3121.02
 GHI 2013-10-28  -46      34.65       -1593.90        16.24 -1577.66
 GHI 2013-11-03 -246      35.61       -8760.06       323.00 -8437.06
 GHI 2013-11-16 -320      34.16      -10931.20       -43.83 -10975.0
 GHI 2013-11-17  -92      32.65       -3003.80      -151.52 -3155.32
 GHI 2013-11-23  187      33.86        6331.82          .00      .00
 GHI 2013-12-06   85      33.64        2859.40          .00      .00
 GHI 2013-12-16  500      33.55       16775.00          .00      .00
 GHI 2013-12-21   72      34.38        2475.36          .00      .00
 GHI 2013-12-27  376      33.49       12592.24          .00      .00
 GHI 2014-01-02  396      35.52       14065.92          .00      .00
 GHI 2014-01-25  -78      35.46       -2765.88       105.73 -2660.15
 XYZ 2013-02-20  528      23.54       12429.12          .00      .00
 XYZ 2013-02-27  357      22.86        8161.02          .00      .00
 XYZ 2013-03-02    9      24.04         216.36          .00      .00
 XYZ 2013-03-11 -193      24.90       -4805.70       313.92 -4491.78
 XYZ 2013-03-20  -20      23.59        -471.80         6.33  -465.47
 XYZ 2013-03-21  275      24.83        6828.25          .00      .00
 XYZ 2013-04-13  243      24.25        5892.75          .00      .00
 XYZ 2013-04-18  275      24.83        6828.25          .00      .00
 XYZ 2013-04-19  277      22.83        6323.91          .00      .00
 XYZ 2013-04-28  142      23.13        3284.46          .00      .00
 XYZ 2013-05-07   55      23.43        1288.65          .00      .00
 XYZ 2013-05-15   97      25.13        2437.61          .00      .00
 XYZ 2013-05-16  315      23.57        7424.55          .00      .00
 XYZ 2013-05-19  467      23.15       10811.05          .00      .00
 XYZ 2013-06-02  114      24.29        2769.06          .00      .00
 XYZ 2013-06-15  545      23.88       13014.60          .00      .00
 XYZ 2013-06-19  296      23.46        6944.16          .00      .00
 XYZ 2013-06-19  413      25.18       10399.34          .00      .00
 XYZ 2013-06-19  202      22.95        4635.90          .00      .00
 XYZ 2013-06-30 -272      23.45       -6378.40      -100.35 -6478.75
 XYZ 2013-07-05 -277      25.01       -6927.77       329.93 -6597.84
 XYZ 2013-07-06 -170      23.46       -3988.20       -61.02 -4049.22
 XYZ 2013-07-22  -88      24.64       -2168.32        72.26 -2096.06
 XYZ 2013-08-13 -163      23.65       -3854.95       -27.53 -3882.48
 XYZ 2013-08-31  -61      23.23       -1417.03       -35.92 -1452.95
 XYZ 2013-09-06  -13      23.97        -311.61         1.96  -309.65
 XYZ 2013-09-06 -147      23.81       -3500.07        -1.31 -3501.38
 XYZ 2013-09-17   92      24.60        2263.20          .00      .00
 XYZ 2013-09-24 -248      24.58       -6095.84       183.34 -5912.50
 XYZ 2013-10-11 -250      23.12       -5780.00      -180.18 -5960.18
 XYZ 2013-10-12  596      23.16       13803.36          .00      .00
 XYZ 2013-10-13  359      23.91        8583.69          .00      .00
 XYZ 2013-10-19  424      25.13       10655.12          .00      .00
 XYZ 2013-10-25 -153      24.31       -3719.43        65.73 -3653.70
 XYZ 2013-10-27 -248      24.71       -6128.08       205.74 -5922.34
 XYZ 2013-10-30 -350      23.36       -8176.00      -182.14 -8358.14
 XYZ 2013-11-03 -326      23.24       -7576.24      -208.77 -7785.01
 XYZ 2013-11-25 -278      23.18       -6444.04      -194.71 -6638.75
 XYZ 2013-12-09  315      24.46        7704.90          .00      .00
 XYZ 2013-12-23 -376      25.12       -9445.12       444.23 -9000.89
 XYZ 2014-01-02 -196      22.98       -4504.08      -187.88 -4691.96
 XYZ 2014-01-04  261      24.46        6384.06          .00      .00
 XYZ 2014-01-09 -253      24.46       -6188.38       119.75 -6068.63
 XYZ 2014-01-12  332      24.28        8060.96          .00      .00
 XYZ 2014-01-26  404      24.23        9788.92          .00      .00
 XYZ 2014-01-31  548      23.65       12960.20          .00      .00
 XYZ 2014-02-03  459      23.70       10878.30          .00      .00
 XYZ 2014-02-04 -208      24.36       -5066.88        83.45 -4983.43
 XYZ 2014-02-05   42      24.39        1024.38          .00      .00


If we had wanted to calculate the cumulative values instead we only need the change the return value (@RV) passed into the function.
SELECT sym
,tDate
,qty
,price_extended
,wct.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.
sym tDate       qty price_unit price_extended Gross Margin         COGS
---- ---------- ---- ---------- -------------- ------------ ------------
 ABC 2013-02-23  238      12.78        3041.64          .00          .00
 ABC 2013-03-01  157      13.01        2042.57          .00          .00
 ABC 2013-03-08 -157      13.17       -2067.69        46.88     -2020.81
 ABC 2013-03-10  -63      12.61        -794.43        30.41     -2831.71
 ABC 2013-03-10  463      13.38        6194.94        30.41     -2831.71
 ABC 2013-03-20  -92      13.64       -1254.88        67.16     -4049.84
 ABC 2013-04-08  298      12.98        3868.04        67.16     -4049.84
 ABC 2013-04-30  229      13.32        3050.28        67.16     -4049.84
 ABC 2013-04-30 -191      13.49       -2576.59       125.39     -6568.20
 ABC 2013-05-08  238      12.79        3044.02       125.39     -6568.20
 ABC 2013-05-18  298      13.23        3942.54       125.39     -6568.20
 ABC 2013-06-11  130      13.02        1692.60       125.39     -6568.20
 ABC 2013-06-17  313      12.93        4047.09       125.39     -6568.20
 ABC 2013-06-21 -326      12.73       -4149.98         8.90    -10834.67
 ABC 2013-06-22 -227      13.47       -3057.69        95.77    -13805.49
 ABC 2013-07-22  599      13.65        8176.35        95.77    -13805.49
 ABC 2013-07-31   79      13.55        1070.45        95.77    -13805.49
 ABC 2013-09-08 -386      13.65       -5268.90       240.35    -18929.81
 ABC 2013-09-08  490      12.69        6218.10       240.35    -18929.81
 ABC 2013-09-16 -202      12.94       -2613.88       200.32    -21583.72
 ABC 2013-10-09  249      13.47        3354.03       200.32    -21583.72
 ABC 2013-10-09  187      13.36        2498.32       200.32    -21583.72
 ABC 2013-11-07   27      12.68         342.36       200.32    -21583.72
 ABC 2013-11-14 -386      12.40       -4786.40      -102.97    -26673.41
 ABC 2013-12-31 -145      13.19       -1912.55      -102.35    -28585.34
 GHI 2013-03-07  559      35.21       19682.39          .00          .00
 GHI 2013-04-03 -151      33.16       -5007.16      -309.55     -5316.71
 GHI 2013-04-19  416      34.46       14335.36      -309.55     -5316.71
 GHI 2013-05-16  160      34.48        5516.80      -309.55     -5316.71
 GHI 2013-05-21 -174      34.20       -5950.80      -409.47    -11367.43
 GHI 2013-07-02 -162      35.48       -5747.76      -295.13    -17000.85
 GHI 2013-07-16 -347      35.29      -12245.63      -116.16    -29067.51
 GHI 2013-08-06  445      33.50       14907.50      -116.16    -29067.51
 GHI 2013-08-25  272      33.86        9209.92      -116.16    -29067.51
 GHI 2013-09-08  103      35.42        3648.26      -116.16    -29067.51
 GHI 2013-09-24  328      34.95       11463.60      -116.16    -29067.51
 GHI 2013-09-24 -291      35.69      -10385.79       289.21    -39047.93
 GHI 2013-10-02 -295      32.51       -9590.45      -237.95    -49165.54
 GHI 2013-10-14  -91      33.12       -3013.92      -345.05    -52286.56
 GHI 2013-10-28  -46      34.65       -1593.90      -328.81    -53864.22
 GHI 2013-11-03 -246      35.61       -8760.06        -5.81    -62301.28
 GHI 2013-11-16 -320      34.16      -10931.20       -49.64    -73276.31
 GHI 2013-11-17  -92      32.65       -3003.80      -201.16    -76431.63
 GHI 2013-11-23  187      33.86        6331.82      -201.16    -76431.63
 GHI 2013-12-06   85      33.64        2859.40      -201.16    -76431.63
 GHI 2013-12-16  500      33.55       16775.00      -201.16    -76431.63
 GHI 2013-12-21   72      34.38        2475.36      -201.16    -76431.63
 GHI 2013-12-27  376      33.49       12592.24      -201.16    -76431.63
 GHI 2014-01-02  396      35.52       14065.92      -201.16    -76431.63
 GHI 2014-01-25  -78      35.46       -2765.88       -95.43    -79091.78
 XYZ 2013-02-20  528      23.54       12429.12          .00          .00
 XYZ 2013-02-27  357      22.86        8161.02          .00          .00
 XYZ 2013-03-02    9      24.04         216.36          .00          .00
 XYZ 2013-03-11 -193      24.90       -4805.70       313.92     -4491.78
 XYZ 2013-03-20  -20      23.59        -471.80       320.25     -4957.25
 XYZ 2013-03-21  275      24.83        6828.25       320.25     -4957.25
 XYZ 2013-04-13  243      24.25        5892.75       320.25     -4957.25
 XYZ 2013-04-18  275      24.83        6828.25       320.25     -4957.25
 XYZ 2013-04-19  277      22.83        6323.91       320.25     -4957.25
 XYZ 2013-04-28  142      23.13        3284.46       320.25     -4957.25
 XYZ 2013-05-07   55      23.43        1288.65       320.25     -4957.25
 XYZ 2013-05-15   97      25.13        2437.61       320.25     -4957.25
 XYZ 2013-05-16  315      23.57        7424.55       320.25     -4957.25
 XYZ 2013-05-19  467      23.15       10811.05       320.25     -4957.25
 XYZ 2013-06-02  114      24.29        2769.06       320.25     -4957.25
 XYZ 2013-06-15  545      23.88       13014.60       320.25     -4957.25
 XYZ 2013-06-19  296      23.46        6944.16       320.25     -4957.25
 XYZ 2013-06-19  413      25.18       10399.34       320.25     -4957.25
 XYZ 2013-06-19  202      22.95        4635.90       320.25     -4957.25
 XYZ 2013-06-30 -272      23.45       -6378.40       219.90    -11436.00
 XYZ 2013-07-05 -277      25.01       -6927.77       549.83    -18033.84
 XYZ 2013-07-06 -170      23.46       -3988.20       488.81    -22083.06
 XYZ 2013-07-22  -88      24.64       -2168.32       561.07    -24179.12
 XYZ 2013-08-13 -163      23.65       -3854.95       533.54    -28061.60
 XYZ 2013-08-31  -61      23.23       -1417.03       497.62    -29514.55
 XYZ 2013-09-06  -13      23.97        -311.61       499.58    -29824.20
 XYZ 2013-09-06 -147      23.81       -3500.07       498.27    -33325.58
 XYZ 2013-09-17   92      24.60        2263.20       498.27    -33325.58
 XYZ 2013-09-24 -248      24.58       -6095.84       681.61    -39238.08
 XYZ 2013-10-11 -250      23.12       -5780.00       501.43    -45198.26
 XYZ 2013-10-12  596      23.16       13803.36       501.43    -45198.26
 XYZ 2013-10-13  359      23.91        8583.69       501.43    -45198.26
 XYZ 2013-10-19  424      25.13       10655.12       501.43    -45198.26
 XYZ 2013-10-25 -153      24.31       -3719.43       567.16    -48851.96
 XYZ 2013-10-27 -248      24.71       -6128.08       772.90    -54774.30
 XYZ 2013-10-30 -350      23.36       -8176.00       590.76    -63132.44
 XYZ 2013-11-03 -326      23.24       -7576.24       381.99    -70917.45
 XYZ 2013-11-25 -278      23.18       -6444.04       187.28    -77556.20
 XYZ 2013-12-09  315      24.46        7704.90       187.28    -77556.20
 XYZ 2013-12-23 -376      25.12       -9445.12       631.51    -86557.09
 XYZ 2014-01-02 -196      22.98       -4504.08       443.63    -91249.05
 XYZ 2014-01-04  261      24.46        6384.06       443.63    -91249.05
 XYZ 2014-01-09 -253      24.46       -6188.38       563.38    -97317.68
 XYZ 2014-01-12  332      24.28        8060.96       563.38    -97317.68
 XYZ 2014-01-26  404      24.23        9788.92       563.38    -97317.68
 XYZ 2014-01-31  548      23.65       12960.20       563.38    -97317.68
 XYZ 2014-02-03  459      23.70       10878.30       563.38    -97317.68
 XYZ 2014-02-04 -208      24.36       -5066.88       646.83   -102301.11
 XYZ 2014-02-05   42      24.39        1024.38       646.83   -102301.11


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.
rn   ccy       amt_ccy rate  ctr       amt_ctr GBP Position USD Position          P/L
---- ---- ------------ ----- ---- ------------ ------------ ------------ ------------
   1 GBP    8000000.00 1.619 USD  -12952000.00   8000000.00  12952000.00          .00
   2 GBP  -10000000.00 1.620 USD   16200000.00  -2000000.00  -3240000.00      8000.00
   3 GBP   -4000000.00 1.613 USD    6452000.00  -6000000.00  -9692000.00          .00
   4 GBP    7000000.00 1.618 USD  -11326000.00   1000000.00   1618000.00    -16000.00
   5 GBP    6000000.00 1.623 USD   -9738000.00   7000000.00  11356000.00          .00
   6 GBP   -5000000.00 1.618 USD    8090000.00   2000000.00   3244571.43    -21428.57
   7 GBP  -10000000.00 1.602 USD   16020000.00  -8000000.00 -12816000.00    -40571.43
   8 GBP    2000000.00 1.608 USD   -3216000.00  -6000000.00  -9612000.00    -12000.00
   9 GBP   -2000000.00 1.602 USD    3204000.00  -8000000.00 -12816000.00          .00
  10 GBP   10000000.00 1.626 USD  -16260000.00   2000000.00   3252000.00   -192000.00
 
 
 

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service