Search code examples
sqlsql-serversql-server-2019

Compare value between row and increment another column

I have a table with a column A with a value smallint and I want compare if the value is greater than a last value in a same table. If this value is greater, I want increment a value in a column B.

Before request :

Id Column A Column B
1 300 0
2 400 0
3 400 0
4 500 0
5 550 0

After request :

Id Column A Column B Column DiffValue
1 300 0 0
2 400 1 100
3 400 1 0
4 500 2 100
5 550 3 50

Actually I have this code for compared the value between two row but I'm blocked now.. Code :

UPDATE tA
SET tA.DiffValue =tB.DiffValue,
FROM MyTable as tA
INNER JOIN(
        SELECT Id, DiffValue = A - LAG(A) OVER (ORDER BY A ASC)
        FROM MyTable 
        ) as tB ON tA.Id = tB.Id

Do you have any solution ?

Solution

  • Thank you @siggemannen it's a good answer, I just change 'when lag(a) < a by 'DiffValue > 0'

    select * from (select sum(flag) over(order by id) as running, * from (select case when DiffValue > 0 then 1 else 0 end as flag, * from yourtable) x) x) x