Skip to content
Advertisement

Get previous value of current row in select

I am calculating value based on value in previous row. I used LAG for getting value from previous row and it works well for first two but not for next rows. Let me elaborate my scenario if row is first then I am taking Open_HA same as O column. But for next rows I am taking previous row Open_HA and adding it to previous row Close_HA to calculate the next row Open_HA and dividing it by 2 and so on. What I have done is below.

Expected result should be like below image

enter image description here

For more clarification excel link that doing accurate calculation as I am trying to do with above query.

Advertisement

Answer

This can be done with recursion to perform the non-trivial running calculation related to the prior row.

The fiddle for SQL Server

First we calculate Close_HA in the first CTE term, then we recursively use that result to calculate Open_HA.

If you’ve already calculated Close_HA (it’s already in the table), we can skip the first CTE term.

The SQL:

The result:

Setup for the test case:

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement