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