Condition to SUM previous values – SQLite

My following SQLite database:

Explaining brazilian tax income:

a) if I have loss, I don’t need to pay any tax (e.g.: January)

b) negative results can be subtracted from the next month positive outcome (e.g.: in February, instead of paying the full tax for $ 5942, the tax can be applied only to (5942 – 3200) = 2742.

c) if previous negative results are not sufficient to cover the next positive outcome, I got pay tax (e.g.: in September, I could compensate from June and July, but I had to aggregate from August (e.g.: total tax = -5000 -2185 +5000 +3000 = 815)

My goal would be build the following table:

I couldn’t figure out a way to solve this problem. Any help?

Tks

Answer

You need to use recursive CTEs here. If you are not familiar with this feature you might check out my tutorial, the official documentation referenced in that tutorial, as well as any number of other tutorials available on the Internet.

First, I generate temporary row numbers using the row_number Window function in the source CTE block below (replace “RESULTS” with your table name). Then I use recursive CTE (losses) to calculate residual loss from the previous months, which can be used to reduce your taxes. (This part might be tricky to understand if you are not familiar with recursive CTEs.) Finally, I calculate the total taxable amount adjusted for previous remaining loss if necessary.

WITH RECURSIVE
    source AS (
        SELECT row_number() OVER (ORDER BY ym) AS rid, *
        FROM RESULTS
    ),
    losses AS (
            SELECT s.*, 0 AS res_loss
            FROM source AS s
            WHERE rid = 1
        UNION ALL
            SELECT s.*, iif(l.res_loss + l.profitloss < 0, l.res_loss + l.profitloss, 0) AS res_loss
            FROM source AS s, losses AS l
            WHERE s.rid = l.rid + 1
    )
SELECT ym, profitloss, iif(profitloss + res_loss > 0, profitloss + res_loss, 0) AS tax
FROM losses
ORDER BY ym;