Calculation between Rows and Columns Different SQL SERVER

Question

I need a solution or function to perform the following calculation

(Backlog(dia anterior) + Abertos(dia atual)) - fechados(dia atual) = backlog(dia atual)

Here’s a screenshot of the table below. I’ve never come across this situation of having to calculate values between rows of different columns. I read something about Lead and Leg but I could not apply the solution to what I need, someone can give a light?

Answer 1

I didn’t do it the smartest way in the world, and it can even perform poorly for large data loads. I am taking into account that each line is numbered only in a growing way (not that this is a big problem, can you get around this).

In this case, each row contains 3 raw information:

1. a line identifier (in the case you displayed, it is a date; I will turn into id)
2. the amount of Aberto
3. the amount of Fechado

Thus, each row contains an attribute derived from it, the "balance". In this case, balance is aberto - fechado. I can do the following mapping:

with _saldo as (
  select
    id,
    aberto - fechado as saldo
  from linhas
)
select * from _saldo 

Note that this will bring me just how much each day "moved". We need, however, to accumulate these movements to know how much has in the whole so far:

with _saldo as (
  select
    id,
    aberto - fechado as saldo
  from linhas
), burndown as (
  select
    id,
    (select sum(s2.saldo) from s2 where s2.id <= s.id) as backlog
)
select * from burndown

To let each line have its unique identifier, we can use the ROW_NUMBER. The ROW_NUMBER will generate a line number for each line, and in order for it to know how to enumerate the lines, we need to say what it needs to take into consideration to "sort" the records and enumerate them. In this case, I am calling the column generically "day" (since mentioning it like this is more trivial in SQL than "Creation Date"). In this case, note that CTE linhas can be replaced without any problem by the table containing the actual data:

with linhas as (
    select '2018-01-01' as dia, 10 as aberto, 9 as fechado
    union all
    select '2018-01-02' as dia, 7 as aberto, 3 as fechado
    union all
    select '2018-01-03' as dia, 0 as aberto, 5 as fechado
), _v as (
    select ROW_NUMBER() OVER (ORDER BY dia) as id, dia, aberto, fechado from linhas
), saldo as (
    select dia, id, aberto - fechado as saldo
    from _v
), burndown as (
    select
        dia,
        s.id,
        (select sum(s2.saldo) from saldo s2 where s2.id <= s.id) as acumulado
    from saldo s
)
select dia, acumulado from burndown