15

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?

Improve this question
4
  • 2
    Which DB are you using? Are you trying to get a cumulative sum? Commented Apr 5, 2012 at 10:51
  • Take a look at Calculate a Running Total in SqlServer. Accepted answer has some benchmarks over few ways to calculate running sum. Commented Apr 5, 2012 at 11:51
  • Is BalancePrincipal not just Principal - ((Tenor - 1) * AmoritizingPrincipal)? Commented Apr 5, 2012 at 12:49
  • @jodrell With the given sample data the computation works, but it's definitely the running total i need. BalancePrincipal is Pricipal - Summation of AmortizingPrincipal till the previous tenor. Just in case if the AmortizingPrincipal were not the same numbers this woudnt work. Commented Apr 5, 2012 at 13:25

2 Answers 2

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

A link that might be helpful sqlandme.com/2011/08/17/sql-server-denali-over-rows-range
That's probably the one i think i'll need here. How can we accomplish the same in SQL 2008?
0

For the sample posted, it doesn't look like a filter is needed:

SELECT LoanID, Principal, Tenor, AmortizingPrincipal ,SUM(AmortizingPrincipal) OVER(PARTITION BY LoanID ORDER BY Tenor Desc) AS BalancePrincipal FROM loan ORDER BY LoanID, Principal, Tenor

UPDATE:

Seems Sql Server 2008 does not have the windowing clause? I didn't even think you could create an analytic function without a windowing clause. The above sql was run on Oracle and Postgres without issue. By default the window clause is UNBOUNDED PRECEDING AND CURRENT ROW (from - to). But you could change the order and go from CURRENT ROW to UNBOUNDED FOLLOWING.

UPDATE2:

So I puzzled: what meaning would a (cumulative) SUM have in an analytic function if you are unable to order the rows within the partition? Is there an implicit ordering? I can change the window (below) and get the same result, but must provide the ORDER BY (in Oracle and Postgres). I can't see how the analytic SUM would have any meaning without the ORDER BY.

SELECT LoanID, Principal, Tenor, AmortizingPrincipal ,SUM(AmortizingPrincipal) OVER(PARTITION BY LoanID ORDER BY tenor RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS BalancePrincipal FROM loan ORDER BY LoanID, Principal, Tenor
Improve this answer

7 Comments

What would the combination of SUM and ORDER BY even mean, given that addition is commutative?
@Damien_The_Unbeliever By default the SUM is between UNBOUNDED PRECEDING and CURRENT ROW. So ordering the rows in descending order leaves a running total from highest tenor to lowest tenor, which seems to be what is really being sought.
I can only get it to accept the ORDER BY on 2012. On 2008 it errors ("incorrect syntax near 'order'")
@Glenn: this gives a syntax error near 'order'. FYI: im using SQL 2008.
SQL Server up to 2008 R2 only had a very limited implementation of windowing functions. Running "totals" are only supported since SQL SErver 2012
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.