Balance of the current row based on two-field criteria

Question

If I have a table like this:

DATE        CATEGORY    QTY      SUM
01/01/2019     B         2       true
02/01/2019     B         3       true
03/01/2019     A         1       false
04/01/2019     B         3       true
05/01/2019     A         3       true
06/01/2019     A         2       true

I need to add the qty field by category, where the 'sum' column is true, sorting by the 'date' field. How can I get the following result using CTE in SQL Server

Result Query:

DATE        CATEGORY    QTY_TOTAL   SUM
01/01/2019     B           2        true
02/01/2019     B           5        true
03/01/2019     A           0        false
04/01/2019     B           8        true
05/01/2019     A           3        true
06/01/2019     A           5        true

PS: I looked for some similar question, but found none that could clarify my doubts completely.

Thanks in advance!

Answer 1

You want cumulative sum :

select t.*, sum(case when [sum] = 'true' then qty else 0 end) over (partition by category order by [date]) as QTY_TOTAL
from table t
order by [date];