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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With