15,963,126 members
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 *,
(
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

## Solution 1

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```

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

Top Experts
Last 24hrsThis month
 OriginalGriff 20 Mehran Hoodeh 10 Chris Copeland 5 jackspero18 5 Andre Oosthuizen 5
 OriginalGriff 228 Pete O'Hanlon 150 merano99 80 Andre Oosthuizen 55 den2k88 50

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