title: cummulative sum with reset value and then resum
body: <p>I have following data:</p>
  
  <pre><code>lbid | lbdate     | lbtype        | lbamount
  -----+------------+---------------+---------
     1 | 2017-11-01 | Add Plafon    |     20
     2 | 2017-11-02 | Use Balance   |      5
     3 | 2017-11-03 | Add Balance   |      1
     4 | 2017-11-04 | Reduce Plafon |     10
     5 | 2017-11-06 | Use Balance   |      8
     6 | 2017-11-07 | Add Balance   |      2
     7 | 2017-11-08 | Reduce Plafon |      5
     8 | 2017-11-10 | Add Plafon    |     10
     9 | 2017-11-11 | Use Balance   |      1
    10 | 2017-11-12 | Reduce Plafon |      5
  </code></pre>
  
  <p>Basically the end result I expect look like this:</p>
  
  <pre><code>lbid | lbdate     | lbtype        | lbamount  | sumplafon | sumbalance
  -----+------------+---------------+-----------+-----------+-------------
     1 | 2017-11-01 | Add Plafon    |     20    |       20  |        20
     2 | 2017-11-02 | Use Balance   |      5    |       20  |        15
     3 | 2017-11-03 | Add Balance   |      1    |       20  |        16
     4 | 2017-11-04 | Reduce Plafon |     10    |       10  |        10
     5 | 2017-11-06 | Use Balance   |      8    |       10  |         2
     6 | 2017-11-07 | Add Balance   |      2    |       10  |         4
     7 | 2017-11-08 | Reduce Plafon |      5    |        5  |         4
     8 | 2017-11-10 | Add Plafon    |     10    |       15  |        14
     9 | 2017-11-11 | Use Balance   |      1    |       15  |        15
    10 | 2017-11-12 | Reduce Plafon |      5    |       10  |        10
  </code></pre>
  
  <p>sumplafon is sum of all lbamount which lbtype are Add Balance (positive) and Reduce Balance (negative, to substract the sumplafon).</p>
  
  <p>I already did it by doing this.</p>
  
  <pre><code>sum(
      case
          when "lbtype" = 'Add Plafon' then "lbamount"
          when "lbtype" = 'Reduce Plafon' then -1 * "lbamount"
          else 0
      end
  ) over (order by "lbdate") sumplafon
  </code></pre>
  
  <p>And the sumbalance is sum of all lbamount which lbtype are Add Plafon (postive), Use Balance (postive), Use Balance (negative), but every time lbtype Reduce Plafon is found, the sumbalance will be reset to sumplafon if the sumbalance is greater than sumplafon.</p>
  
  <p>For example lbid 4 which lbtype is Reduce Plafon, the sumbalance is 16 and it is greater than sumplafon 10, therefor the sumbalance need to be reset to its sumplafon which is 10, and then continue again the cummulative sum of the sumbalance.</p>
  
  <p>I tried it by preparing the group by first in cte with count like this.</p>
  
  <pre><code>count(
     case when "lbtype" = 'Reduce Plafon' then 1 else null end
  ) over (order by "lbdate") countplafon
  </code></pre>
  
  <p>And then in the second cte I did sum by using partition by the countplafon in the first cte, like this:</p>
  
  <pre><code>sum(
      case
          when "lbtype" = 'Add Plafon' or "lbtype" = 'Add Balance' then "lbamount"
          when "lbtype" = 'Use Balance' then -1 * "lbamount"
          else 0
      end
  ) over (partition by "countplafon" order by "lbdate") sumbalance
  </code></pre>
  
  <p>But the result is just resetting the sumbalance from beginning because it uses group by countplafon.</p>
  
  <pre><code>lbid | lbdate     | lbtype        | lbamount  | countplafon |sumplafon  | sumbalance
  -----+------------+---------------+-----------+-----------+-------------|-----------
     1 | 2017-11-01 | Add Plafon    |     20    |           0 |       20  |        20
     2 | 2017-11-02 | Use Balance   |      5    |           0 |       20  |        15
     3 | 2017-11-03 | Add Balance   |      1    |           0 |       20  |        16
     4 | 2017-11-04 | Reduce Plafon |     10    |           1 |       20  |         0
     5 | 2017-11-06 | Use Balance   |      8    |           1 |       20  |        -8
     6 | 2017-11-07 | Add Balance   |      2    |           1 |       20  |        -6
     7 | 2017-11-08 | Reduce Plafon |      5    |           2 |       20  |         0
     8 | 2017-11-10 | Add Plafon    |     10    |           2 |       20  |        10
     9 | 2017-11-11 | Use Balance   |      1    |           2 |       20  |         9
    10 | 2017-11-12 | Reduce Plafon |      5    |           3 |       20  |         0
  </code></pre>
  
  <p>Here is the <a href="http://sqlfiddle.com/#!17/63944/1" rel="nofollow noreferrer">sqlfiddle</a>.</p>
  
  <p>Here is the sql.</p>
  
  <pre><code>with
      cte_runningnumbers1
      as (
          select
              "lbid",
              "lbdate",
              "lbtype",
              "lbamount",
              count(
                  case when "lbtype" = 'Reduce Plafon' then 1 else null end
              ) over (order by "lbdate") countplafon,
              sum(
                  case
                      when "lbtype" = 'Add Plafon' then "lbamount"
                      when "lbtype" = 'Reduce Plafon' then -1 * "lbamount"
                      else 0
                  end
              ) over (order by "lbdate") sumplafon
          from    "lb"
      ),
      cte_runningnumbers2 as (
          select
              *,
              sum(
                  case
                      when "lbtype" = 'Add Plafon' or "lbtype" = 'Add Balance' then "lbamount"
                      when "lbtype" = 'Use Balance' then -1 * "lbamount"
                      else 0
                  end
              ) over (partition by "countplafon" order by "lbdate") sumbalance
          from    "cte_runningnumbers1"
      )
  select  *
  from    cte_runningnumbers2
  </code></pre>
  
  <p>I was following this <a href="https://dba.stackexchange.com/questions/104596/cummulative-sum-with-reset-value">SO question</a>, but I'm still confuse how to solve my problem.</p>
  
  <p>The last step I need to do is to add it with the previous sumbalance or sumplafon (if the sumbalance is greater than sumplafon), but I don't know how to do it. Can anyone help me?</p>