How to return value at the same column based on the value of the previous row SQL

I have a problem in SQL and I'm having trouble solving it: I would like to get the first value that was calculated based on other parameters in the table, and then apply a new formula taking the resulting value from the first line:

  A         B    X    Y     Z   P
    3305623815 0.04 1 5/1/2023 0 0.96
    3305623815 0.04 1 2/5/2023 0 0.92
    3305623815 0.04 1 3/5/2023 0 0.88
    3305623815 0.04 1 4/5/2023 0 0.84
    3305623815 0.04 1 5/5/2023 0 0.8
    3305623815 0.04 1 6/5/2023 0 0.76
    3305623815 0.04 1 7/5/2023 0 0.72
    3305623815 0.04 1 5/8/2023 2 2.68`

My goal is to create this column P, to have the value 0.96 (first line), the calculation is: (X-B Z), and the value of the next line is given (value of the previous line P1 - B Z), and of the third line (P2 - B Z), and so on. I tried using the LAG function, but it is always returning the value of the first line, which is 0.96. Any idea which query to use to return the values of column P?

I want to create the column P(let`s imagine it is not in the table shared), as it is in the table, returning the values based in the previous formula calculation description.

CodePudding user response：

You could use the LAG and running SUM window functions as the following:

Select A, B, X, Y, Z,
  (X - SUM(lag_B) Over (Partition By A Order By Y)   Z) AS P
From
(
  Select *,
    LAG(B, 1, B) Over (Partition By A Order By Y) lag_B
  From table_name
) T

LAG(B, 1, B) will return the current B value when there no lag value for B (default value when lag is null).

See demo.