Calculate column value based on criterias (values from both current row and a previous one)

Question

I am trying to learn some SQL by combining it with my interest in finance. At the moment I am attempting to calculate some portfolio data based on a list of transactions (in SQL Server).

This is my table:

create table StockTransactions 
(
    TransactionID int,
    Instrument varchar(32),
    TransactionType varchar(32),
    Units int,
    Price float,
    TransactionSum as case
        when TransactionType = 'Buy' then (-(Units * Price))
        when TransactionType = 'Sell'  then (Units * Price)
        else 0
        end,
    PurchaseCost float
)

Sample data:

insert into StockTransactions (TransactionID, Instrument, TransactionType, Units, Price)
values 
(1, 'Apple', 'Buy', 10, 120),
(2, 'Microsoft', 'Buy', 20, 290),
(3, 'Apple', 'Buy', 10, 125),
(4, 'Apple', 'Sell', 5, 140),
(5, 'Apple', 'Buy', 10, 130),
(6, 'Apple', 'Sell', 10, 135)

Which results in this table:

TransactionID	Instrument	TransactionType	Units	Price	TransactionSum	PurchaseCost
1	Apple	Buy	10	120	-1200	NULL
2	Microsoft	Buy	20	290	-5800	NULL
3	Apple	Buy	10	125	-1250	NULL
4	Apple	Sell	5	140	700	NULL
5	Apple	Buy	10	130	-1300	NULL
6	Apple	Sell	10	135	1350	NULL

I am trying to calculate the values for the PurchaseCost column. The values I want are:

Row	PurchaseCost
1	-1200
2	-5800
3	-2450
4	-1837.5
5	-3137.5
6	-2353.13

The logic is as follows. If a purchase has been made, the TransactionSum should be added to the latest previous PurchaseCost for that instrument. However, when a sale has been made a certain sum should be subtracted.

In transaction four I sell 5 of my 20 Apple shares. Therefor I'd like to subtract 1/4 of the previous PurchaseCost for a new PurchaseCost value of -1837.5.

In transaction six I sell 10 of my 25 Apple shares, and would then like to reduce the PurchaseCost to 2353.13 (60% of the previous value).

I can't seem to figure out how to do this by myself. Any ideas?

Answer 1

You can create store procedure to calculate each row Here, the code to calculate and select using temporary table

IF OBJECT_ID('tempdb..#TempStock') IS NOT NULL DROP TABLE #TempStock

DECLARE @TransId int
DECLARE @TempInst varchar(32)
DECLARE @TempPurchase float
DECLARE @TempUnit int
DECLARE @TempTrans varchar(32)
DECLARE @LastPurchase float
DECLARE @LastUnits int

CREATE TABLE #TempStock (
    TransactionID int,
    Instrument varchar(32),
    Units int,
    PurchaseCost float
)

DECLARE MY_CURSOR CURSOR
FOR SELECT DISTINCT TransactionID 
FROM StockTransactions


OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @TransId
WHILE @@FETCH_STATUS = 0
BEGIN 
    SELECT @TempInst = Instrument, @TempTrans = TransactionType, @TempUnit = Units, @TempPurchase = TransactionSum FROM StockTransactions
    WHERE TransactionID = @TransId

    SELECT  TOP 1 @LastPurchase = PurchaseCost, @LastUnits = Units
    FROM #TempStock
    WHERE Instrument = @TempInst
    ORDER BY TransactionID DESC
    
    IF @@ROWCOUNT = 0
    BEGIN
        SELECT @LastPurchase = 0, @LastUnits = 0 
    END

    IF @TempTrans = 'Buy'
    BEGIN
        INSERT INTO #TempStock (TransactionID, Instrument, Units, PurchaseCost)
        VALUES (
            @TransId,
            @TempInst,
            ISNULL(@LastUnits,0) + ISNULL(@TempUnit,0),
            ISNULL(@LastPurchase,0) + ISNULL(@TempPurchase,0)
        )
    END
    ELSE IF @TempTrans = 'Sell'
    BEGIN
        INSERT INTO #TempStock (TransactionID, Instrument, Units, PurchaseCost)
        VALUES (
            @TransId,
            @TempInst,
            ISNULL(@LastUnits,0) - ISNULL(@TempUnit,0),
            (ISNULL(@LastPurchase,0) * (ISNULL(@LastUnits,0) - ISNULL(@TempUnit,0))) / ISNULL(@LastUnits,0)
        )
    END
    
    FETCH NEXT FROM MY_CURSOR INTO @TransId
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

SELECT TransactionID, PurchaseCost FROM #TempStock

DROP TABLE #TempStock

You can update the master table from that temporary table