title: SQL: use WHERE clause in OVER()?
body: <p>How can I use <code>WHERE</code> clause to filter in the <code>OVER</code> clause? </p>
  
  <p>i.e.  from the following data </p>
  
  <pre><code>LoanID | Principal | Tenor | AmortizingPrincipal 
  ----------------------------------------
  1         20000       1       5000
  1         20000       2       5000
  1         20000       3       5000
  1         20000       4       5000    
  </code></pre>
  
  <p>I need a fourth virtual column with the Balance Principal in each Tenor like the following: </p>
  
  <pre><code>LoanID | Principal | Tenor | AmortizingPrincipal | BalancePrinicpal 
  -----------------------------------------------------------
  1        20000       1       5000                  20000  
  1        20000       2       5000                  15000  
  1        20000       3       5000                  10000 
  1        20000       4       5000                  5000
  </code></pre>
  
  <p>Something like this:</p>
  
  <pre><code>SELECT 
      BalancePrincipal = Principal - SUM(AmortizingPrincipal) OVER(PARTITION BY LoanID WHERE Tenor &lt; this row's tenor) 
  </code></pre>
  
  <p>UPDATE:</p>
  
  <p>The following query gives me the desired result:</p>
  
  <pre>
  SELECT 
      L1.*    
      ,BalancePrincipal = AL1.Principal - ISNULL(Cumulative.AmortizingSum,0) 
  FROM
      Loan L1
  CROSS APPLY 
      (
          SELECT 
              AmortizingSum = SUM(AmortizingPrincipal)
          FROM 
              Loan L2
          WHERE 
              L1.LoanID = L2.LoanID
              AND 
              L1.Tenor > L2.Tenor 
      ) Cumulative
  </pre>
  
  <p>Can it be bettered?</p>