Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL: use WHERE clause in OVER()?

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?

like image 942
Sreerag Avatar asked Apr 05 '12 10:04

Sreerag


1 Answers

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.

like image 187
Damien_The_Unbeliever Avatar answered Oct 20 '22 18:10

Damien_The_Unbeliever