title: Query to Find the "Balance Amount after each Payment of corresponding Bill" in SQL Server
body: <pre><code>PaymentID  SupplyInvoiceID   Date     TotalBill   BillPaidAmount    Remaining Bill
  
  1           1              05-04-2018   2,10,000    20,000             1,90,000
  2           1              10-05-2018   2,10,000    60,000             1,30,000
  3           1              13-06-2018   2,10,000    1,30,000           0 
  4           2              10-05-2018   80,000      40,000             40,000
  5           2              13-06-2018   80,000      20,000             20,000
  6           2              13-06-2018   80,000      20,000             0   
  </code></pre>
  
  <p>The payment of each Bill is paid in installments in different dates as shown above. How to find the remaining Bill amount each time when the partial payment of each bill is made? </p>
  
  <p>I used the following Query:</p>
  
  <pre><code>SELECT siph.SupplyPaymentID,si.SupplyInvoiceID, 
         siph.DateOfPayment,si.TotalBill, siph.BillPaidAmount,
         si.TotalBill - SUM(siph.BillPaidAmount) over(order by siph.SupplyPaymentID asc) as RemainingBillAmount,
         siph.PaymentMode 
  from SupplyInvoicePaymentHistory siph inner join 
       SupplyInvoice si 
       on siph.SupplyInvoiceID = si.SupplyInvoiceID 
  </code></pre>
  
  <p>But it works fine for only bill payments of 1st SupplyInvoiceID. As i enter the bill payments of 2nd and onward SupplyInvoiceID, i gets the wrong result as follows:</p>
  
  <pre><code> PaymentID  SupplyInvoiceID   Date     TotalBill   BillPaidAmount    Remaining Bill
  
      1           1              05-04-2018   2,10,000    20,000             1,90,000
      2           1              10-05-2018   2,10,000    60,000             1,30,000
      3           1              13-06-2018   2,10,000    1,30,000           0 
      4           2              10-05-2018   80,000      40,000             -1,70,000
      5           2              13-06-2018   80,000      20,000             -1,90,000  
      6           2              15-06-2018   80,000      20,000             -2,10,000  
  </code></pre>
  
  <p>..please help to find the correct result as tabulated at the first para of the above question.</p>