Search code examples
sqlsql-serverexcelrows

Subtract from previous row based on certain rows

I have a table like this in SQL

Sample Table

I want to calculate the demand column just like the demand column I created in Excel.

This is the formula I used in Excel

=IF(T2=T1,IF(V1>V2,V1-V2,0),0)

This formula checks if the product type is of the same category and if yes, calculates the number of products that were taken by substracting the stock amount from the previous timestamp.

I want to implement the same in Microsoft SQL Server. Any help will be appreciated.

Solution

  • You can use:

    select t.*,
       (case when lag(stock) over (partition by type order by time) > stock
             then lag(stock) over (partition by type order by time) - stock
             else 0
        end)
from t;