How to build query where 2 fields are inter dependent in MSSQL

Question

I am new to the world of SQL queries, I am comfortable writing basic sql queries limited to to CRUD operations. I am now working on a project where I have to write complex queries and I am seeking help on how to do it.

Scenario I have a table x

The logic I need to implement is

1. The first record starts with some default value let us say 0 as StartCount.
2. I need to add numbers Add1+Add2 and deduct Minus
3. The result of step 2+StartCount becomes my EndCount
4. The next Month StartCount is the EndCount of the previous row.
5. I have to repeat step 2,3,4 for all the rows in the table.

How can I do this using SQL

Answer 1

You want a cumulative sum, is available using window/analytic functions. It is something like this:

select x.*,
       (first_value(startcount) over (order by <ordercol>) +
        sum(add1 + add2 - minus) over (order by <ordercol>)
       ) as yourvalue
from x;

<ordercol> is needed because SQL tables represent unordered sets. You need a column that specifies the ordering of the rows.