Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
GooD Day All

I am trying to calcule the weighted average price of traded stock using the windows function. The calculation are correct on row 1 , 2 and 3 . Starting row 4 the result incorrect. Please tell me what is wrong.

What I have tried:

drop table if exists #Temp
CREATE TABLE #Temp (
    [Id]        INT             IDENTITY (1, 1) NOT NULL,
    [TransID]   INT             NULL,
    [TransType] BIT             DEFAULT ((0)) NULL,
    [Symbol]    INT             NULL,
    [Quantity]  INT             NULL,
    [Price]     DECIMAL (10, 2) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
    insert into #Temp values
     (37 , 0 , 2040 , 1000 , 49.7),
    (37 , 1 , 2040 , 500 , 50.6),
    (37 , 0 , 2040 , 592 , 47),
    (37 , 1 , 2040 , 500 , 50.8),
    (37 , 0 , 2040 , 197 , 0)
    --(77 , 1 , 2040 , 789 , 54.3)
    

  ;with x as
(
select id ,TransID , Symbol ,TransType 
, Case when TransType = 0 then Price else 0 end as BuyPrice
 ,sum(case when TransType = 0 then 1 else 0 end) over  (PARTITION BY Transid ORDER BY Id
      ROWS UNBOUNDED PRECEDING    )   as FN  
, Rqty = sum(Case when TransType = 0 then 1 else -1 end * Quantity) OVER
            (   PARTITION BY Transid ORDER BY Id ROWS UNBOUNDED PRECEDING)
  , Case when TransType = 0 then Quantity else - Quantity end as XQty 
   

from #Temp
), 
b as
(
select *,
 NewPrice = FIRST_VALUE(BuyPrice) OVER
  (
    PARTITION BY Transid, FN ORDER BY Id  
    ROWS UNBOUNDED PRECEDING )

  from x
  ) 
select *
, NewCost = 
Sum(XQty * NewPrice)  over (PARTITION BY Transid 
ORDER BY id ROWS UNBOUNDED PRECEDING)


 ,BPrice= sum(case when TransType = 0 then XQty*BuyPrice else XQty*NewPrice end) OVER
            (   PARTITION BY Transid ORDER BY Id 

            ) 
            /
sum(case when TransType = 0 then XQty else XQty  end) OVER
            (   PARTITION BY Transid ORDER BY Id 

            )
           

 from b

the manual calculation
Bprice	Xqty	NewCost
49.7	1000	49700
49.7	-500	24850
48.23	592	    52674
48.23	-500	28555.87
36.19	197	    28555.87
Posted
Updated 19-May-23 2:40am
v2

1 solution

You are using the SUM window function to calculate the NewCost column. The SUM function is an aggregate function that calculates the cumulative sum of a column within a given window. To calculate the weighted average price, you need multiply the quantity by the price and then summing those values.

Remove the unnecessary subquery for calculating Rqty since it's not used in the final calculation.

NewCost should be - 'SUM(XQty * NewPrice) OVER (PARTITION BY TransID ORDER BY Id)'

WITH x AS
(
    SELECT Id, TransID, Symbol, TransType,
        CASE WHEN TransType = 0 THEN Price ELSE 0 END AS BuyPrice,
        SUM(CASE WHEN TransType = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY TransID ORDER BY Id ROWS UNBOUNDED PRECEDING) AS FN,
        SUM(CASE WHEN TransType = 0 THEN 1 ELSE -1 END * Quantity) OVER (PARTITION BY TransID ORDER BY Id ROWS UNBOUNDED PRECEDING) AS Rqty,
        CASE WHEN TransType = 0 THEN Quantity ELSE -Quantity END AS XQty
    FROM #Temp
),
b AS
(
    SELECT *,
        NewPrice = FIRST_VALUE(BuyPrice) OVER (PARTITION BY TransID, FN ORDER BY Id ROWS UNBOUNDED PRECEDING)
    FROM x
)
SELECT *,
    NewCost = SUM(XQty * NewPrice) OVER (PARTITION BY TransID ORDER BY Id),
    BPrice = SUM(CASE WHEN TransType = 0 THEN XQty * BuyPrice ELSE XQty * NewPrice END) OVER (PARTITION BY TransID ORDER BY Id) /
             SUM(CASE WHEN TransType = 0 THEN XQty ELSE XQty END) OVER (PARTITION BY TransID ORDER BY Id)
FROM b;


Output -
Bprice	Xqty	NewCost
49.7	1000	49700
49.7	-500	24850
48.23	592	    52674
48.23	-500	28555.87
36.19	197	    28555.87
 
Share this answer
 
Comments
qulaitks 19-May-23 8:56am    
I have tried your corrected code and had different output than yours
TransID XCost NewCost BPrice
37 49700.00 49700.00 49.700000
37 24850.00 24850.00 49.700000
37 52674.00 52674.00 48.236263
37 29174.00 29174.00 49.280405
37 29174.00 29174.00 36.975918
Andre Oosthuizen 19-May-23 9:27am    
I will test the statement again and post any changes as soon as time allows.
Andre Oosthuizen 20-May-23 6:21am    
It looks like I had the calculation of the NewPrice column in the b incorrect. Try the following NewPrice statement -
NewPrice = CASE
    WHEN TransType = 0 THEN BuyPrice
    ELSE LAG(NewPrice) OVER (PARTITION BY TransID, FN ORDER BY Id)
END
qulaitks 20-May-23 7:02am    
Thansk, I have tried that and the output is similar to the previous code using the FIRST_VALUE

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900