title: SQL Server : summing (or difference) each group values of a column until a condition is met on another column
body: <p>I'm pretty new to SQL and using SQL Server 2012. I have a sample from the dataset I'm using below. It's an accounting  dataset of bank transactions and invoice amounts with the following values customer ID, date of invoice, VADEKONTROL as indication whether the invoice maturity expired-not expired-not invoice, BORC is the invoice amount, ALACAK is the amount of payment received and BAKIYE is the total balance. Not invoice transactions are bank receipts.</p>
  
  <p>What I want to do for each customer is to subtract from the last amount of BAKIYE the values in BORC column starting from the last entry until the difference is &lt;= 0. Also it should stop subtracting if there's no more entry of the grouped customer found. So for M01516, 999,59 should be subtracted from 1982,40 and since the difference is not 0, keep on subtracting 982,81 and further results (if there was any) until difference is &lt;= 0. </p>
  
  <p>Also no need for the subtraction if the last value of BAKIYE equals 0 for a certain customer.</p>
  
  <p>The basic logic is above and with that:</p>
  
  <ol>
  <li><p>I want to select the entries which were subtracted from BAKIYE (until BAKIYE reaches 0 or the difference between sum of selected entries and bakiye reach 0, whichever way you look at it.</p></li>
  <li><p>Count and sum the selected entries for each customer most likely with group by.</p></li>
  </ol>
  
  <p>edit:</p>
  
  <p>Keep in mind that if BORC value is 0 then transaction is a credit and if ALACAK value is 0 then transaction is a debt. So in order to find the current balance starting from the last, we might have to subtract both BORC and ALACAK values from BAKIYE. </p>
  
  <p>Basically what I want is for each customer group starting from the last record: get last row of BAKIYE value, subtract  BORC+ALACAK from BAKIYE for same row. Repeat this going up one row until BAKIYE is &lt;= 0 for each customer. With this I can select the invoice amounts and number of invoices that will cover the remaining balance for a customer.   </p>
  
  <p>How can I go on solving this problem?</p>
  
  <p>Any help would be greatly appreciated. </p>
  
  <pre><code>ACCID   BDATE       VADEKONTROL     BORC    ALACAK  BAKIYE
  ------------------------------------------------------------
  M01518  12.12.2018  expired         64,51     0,00   64,51
  M01518  14.01.2019  expired         69,00     0,00  133,51
  M01518  12.02.2019  not expired     69,00     0,00  202,51
  M01518  18.02.2019  not invoice      0,00   203,00   -0,49
  M01517  14.12.2018  expired         93,49     0,00   93,49
  M01517  14.01.2019  expired         93,49     0,00  186,98
  M01517  12.02.2019  not expired     93,49     0,00  280,47
  M01516  25.12.2018  expired         982,81    0,00  982,81
  M01516  21.01.2019  expired         999,59    0,00  1982,40
  M01514  11.12.2018  expired          25,10    0,00    25,10
  M01514  10.01.2019  not invoice       0,00   25,10     0,00
  M01514  14.01.2019  expired          25,10    0,00    25,10
  M01514  24.01.2019  not invoice       0,00   25,10     0,00
  M01514  11.02.2019  not expired      25,10    0,00    25,10
  </code></pre>
  
  <p>edit2 output with Dwight's code:</p>
  
  <pre><code>ACCID   BDATE        VADEKONTROL    BORC    ALACAK  BAKIYE  BAKIYE_less_BORC
  M01518  2018-12-12  Vadesi Geçmiş   64,51   0,00    64,51   0,00
  M01518  2019-01-14  Vadesi Geçmiş   69,00   0,00    133,51  64,51
  M01518  2019-02-12  Vadesi Gelmemiş 69,00   0,00    202,51  NULL
  M01518  2019-02-18  FaturaDegil     0,00    203,00  -0,49   NULL
  M01517  2018-12-14  Vadesi Geçmiş   93,49   0,00    93,49   0,00
  M01517  2019-01-14  Vadesi Geçmiş   93,49   0,00    186,98  93,49
  M01517  2019-02-12  Vadesi Gelmemiş 93,49   0,00    280,47  NULL
  M01516  2018-12-25  Vadesi Geçmiş   982,81  0,00    982,81  0,00
  M01516  2019-01-21  Vadesi Geçmiş   999,59  0,00    1982,40 982,81
  M01514  2018-12-11  Vadesi Geçmiş   25,10   0,00    25,10   0,00
  M01514  2019-01-10  FaturaDegil     0,00    25,10   0,00    NULL
  M01514  2019-01-14  Vadesi Geçmiş   25,10   0,00    25,10   0,00
  M01514  2019-01-24  FaturaDegil     0,00    25,10   0,00    NULL
  M01514  2019-02-11  Vadesi Gelmemiş 25,10   0,00    25,10   NULL
  </code></pre>
  
  <p>I also used the following code (FNM00_ACC_CODE is ACCID) and got an almost correct result.</p>
  
  <pre><code>select FNM00_ACC_CODE,borc,alacak,BAKIYE,bdate,vadekontrol,
  (cast(((SELECT TOP 1 BAKIYE FROM GRID_Temp_Current_Accounts_All_Can2 ac2 
  WHERE ac2.FNM00_ACC_CODE=c.FNM00_ACC_CODE ORDER BY ac2.ID desc)-
              (select ISNULL(sum(convert(decimal(18,2),BORC)),'0.00') as money 
  from GRID_Temp_Current_Accounts_All_Can2 as ac
          WHERE ac.FNM00_ACC_CODE=c.FNM00_ACC_CODE
              and ac.id &gt;= c.id)) as money)) BAKIYE2 from 
  GridTelekom_MetaData.dbo.GRID_Temp_Current_Accounts_All_Can2 c
  
  
  
  
    FNM00_ACC_CODE      borc     alacak        BAKIYE    bdate    vadekontrol BAKIYE2
            M00385      2228,75   0,00     7689,75    2018-11-19  Vadesi Geçmiş   -7039,00
            M00385      2545,34   0,00     10235,09   2018-11-29  Vadesi Geçmiş   -4810,25
            M00385      2256,00   0,00     12491,09   2018-12-18  Vadesi Geçmiş   -2264,91
            M00385      0,00     3000,00   9491,09    2018-12-20  FaturaDegil -8,91
            M00385      0,00     3500,00   5991,09    2018-12-28  FaturaDegil -8,91
            M00385      2969,42   0,00     8960,51    2018-12-31  Vadesi Geçmiş   -8,91
            M00385      2244,15   0,00     11204,66   2019-01-18  Vadesi Geçmiş   2960,51  
            M00385      0,00     6000,00   5204,66    2019-01-24  FaturaDegil 5204,66
            M00385      2237,34   0,00     7442,00    2019-01-29  Vadesi Geçmiş   5204,66
            M00385      2217,11   0,00     9659,11    2019-02-18  Vadesi Gelmemiş 7442,00
  </code></pre>
  
  <p>Whenever BAKIYE2 reaches minus value, I want the transactions up and including to that point. So for the example above I would need the last 5 rows since sum of BORC for last 5 rows equals to 9668.02 which is 8.91 more than the last current BAKIYE which i 9659.11. I can then distinguish between expired maturities and not expired maturities. </p>
  
  <p>With this method though I was thinking of selecting all entries where BAKIYE2 >= 0 , however this would skip the last row where it takes the BAKIYE into minuses (in this case it would not select 2969.42).</p>