I have the data as below.

The desired output in NetMF

I want to calculate Net MF using below conditions.

1. At datetime 9:15, I will return MF value into Net MF
2. From 9:16 onwards below is the logic i want to follow
   • If AVG_Price at 9:16 > AVG_Price at 9:15 then Net MF at 9:16 = Net MF at 9:15 MF at 9:16
   • If AVG_Price at 9:16 < AVG_Price at 9:15 then Net MF at 9:16 = Net MF at 9:15 - MF at 9:16
   • If AVG_Price at 9:16 = AVG_Price at 9:15 and Net MF at 9:15 < 0 then Net MF at 9:16 = Net MF at 9:15 - MF at 9:16
   • If AVG_Price at 9:16 = AVG_Price at 9:15 and Net MF at 9:15 >= 0 then Net MF at 9:16 = Net MF at 9:15 MF at 9:16

This logic continues till the end of the time period.

I need this logic to work for Multiple stock codes and multiple datetimes.

Currently I have below code in place. This code returns only values for first 1 or 2 rows

WITH v_table_name AS
    (
        SELECT stock_code,[datetime], [AVG_Price],[MF], CASE WHEN cast(datetime as time) = '09:15:00.000' and close_price >= open_price THEN [MF] 
                       WHEN cast(datetime as time) = '09:15:00.000' and close_price < open_price THEN -1*[MF] 
                       WHEN Avg_Price > LAG(Avg_Price,1,-1) over (Partition by stock_code order by datetime asc) THEN (LAG([Net MF],1,-1) over (Partition by stock_code order by datetime asc))   [MF]
                       WHEN Avg_Price < LAG(Avg_Price,1,-1) over (Partition by stock_code order by datetime asc) THEN (LAG([Net MF],1,-1) over (Partition by stock_code order by datetime asc)) - [MF]
                       WHEN Avg_Price = LAG(Avg_Price,1,-1) over (Partition by stock_code order by datetime asc) and (LAG([Net MF],1,-1) over (Partition by stock_code order by datetime asc)) < 0 THEN (LAG([Net MF],1,-1) over (Partition by stock_code order by datetime asc)) - [MF]
                       WHEN Avg_Price = LAG(Avg_Price,1,-1) over (Partition by stock_code order by datetime asc) and (LAG([Net MF],1,-1) over (Partition by stock_code order by datetime asc)) > 0 THEN (LAG([Net MF],1,-1) over (Partition by stock_code order by datetime asc))   [MF]
                       END as [Net MF] from Equity
    ) 
    UPDATE [Equity] set Equity.[Net MF] = v_table_name.[Net MF]
    FROM v_table_name
    WHERE Equity.stock_code = v_table_name.stock_code
    and Equity.datetime = v_table_name.datetime

CodePudding user response：

As the Net MF depends on previous row value, one simple solution is to use a recursive CTE and calculate the Net MF in the recursive part of the CTE based on your required condition

with 
cte as
(
    select rn = row_number() over (partition by stock_code order by [datetime]),
           stock_code, [datetime], AVG_Price, [MF]
    from   Equity
),
rcte as
(
    -- anchor
    select c.rn, c.stock_code, c.[datetime], c.AVG_Price, c.[MF], [Net MF] = c.[MF]
    from   cte c
    where  c.rn = 1

    union all

    -- recursive
    select c.rn, c.stock_code, c.[datetime], c.AVG_Price, c.[MF],
           [Net MF] = case when c.AVG_Price > r.AVG_Price
                           then r.[Net MF]   c.[MF]
                           when c.AVG_Price < r.AVG_Price
                           then r.[Net MF] - c.[MF]
                           when c.AVG_Price = r.AVG_Price
                           and  r.[Net MF] < 0
                           then r.[Net MF] - c.[MF]
                           when c.AVG_Price = r.AVG_Price
                           and  r.[Net MF] >= 0
                           then r.[Net MF]   c.[MF]  
                           end
    from   cte c
           inner join rcte r on  c.stock_code = r.stock_code
                             and c.rn         = r.rn   1
)
update e
set    [Net MF] = r.[Net MF]
from   rcte r
       inner join Equity e on   r.stock_code = e.stock_code
                           and  r.[datetime] = e.[datetime]
OPTION (MAXRECURSION 0);

Note : open/close price logic is not in as it is not clear for me. You may add it in yourself

db<>fiddle demo