SQL: use WHERE clause in OVER()?
sql, sql-server-2008
Solution
If you're using SQL Server 2012, you'd be looking to specify `ROWS`/`RANGE` in your `OVER`:
Further limits the rows within the partition by specifying start and end points within the partition. This is done by specifying a range of rows with respect to the current row either by logical association or physical association. Physical association is achieved by using the ROWS clause.
Other database systems may have similar features. This feature is new in the 2012 version of SQL Server.
Problem
How can I use `WHERE` clause to filter in the `OVER` clause? i.e. from the following data ``` LoanID | Principal | Tenor | AmortizingPrincipal ---------------------------------------- 1 20000 1 5000 1 20000 2 5000 1 20000 3 5000 1 20000 4 5000 ``` I need a fourth virtual column with the Balance Principal in each Tenor like the following: ``` 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 ``` Something like this: ``` SELECT BalancePrincipal = Principal - SUM(AmortizingPrincipal) OVER(PARTITION BY LoanID WHERE Tenor < this row's tenor) ``` UPDATE: The following query gives me the desired result: ``` 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 ``` Can it be bettered?