sql windowing functions - how to generate a payment schedule summary

[Former Member]

Hi,

I have a monthly repayment schedule, with a bullet payment at the end of every year (usually at the end of every year, but it can be anywhere in the loan period) . The bullet payment may vary each year.I am trying to generate a summary of it, using sql windowing functions, but with no luck so far. Please help.

(Sql Anywhere 9.0.2)

I would like to see the output like this :

month_from  month_to    amount
1   11  2000
12  12  10000
13  23  2000
24  24  10000
25  35  2000
36  36  10000
37  47  2000
48  48  30000

Sample data :

month,id_loan,amount
1,1,2000
2,1,2000
3,1,2000
4,1,2000
5,1,2000
6,1,2000
7,1,2000
8,1,2000
9,1,2000
10,1,2000
11,1,2000    
12,1,10000

13,1,2000
14,1,2000
15,1,2000
16,1,2000
17,1,2000
18,1,2000
19,1,2000
20,1,2000
21,1,2000
22,1,2000
23,1,2000
24,1,10000
...

Thanks in advance

[Former Member]

Edited the question a little bit, to add the unknown factor of bullet payment

[VolkerBarth · Accepted solution]

Alright, given the still somewhat unspecific requirements I'll try another approach:

Contrary to the sample data (what really does question their worth...), let's assume it's not known in what month a "bullet payment" might come. So we might try to find out which month does have a different payment than the month before. That can be done with window functions - cf. this question.

The following query (based on the table and data from my first answer) does return both data from the current and the previous row. It does make use of two windows for the previous row - note the dummy sum() aggregate, simply as windows need an aggregate function - the sum is just a sum of one value each time...

select month_id,
   amount as current_amount,
   sum(amount) over (rows between 1 preceding and 1 preceding) as prev_amount,
   sum(month_id) over (rows between 1 preceding and 1 preceding) as prev_month_id,
   current_amount - prev_amount as diff_amount
from payment_schedule
order by 1

This will return data like

**month_id,current_amount,prev_amount,prev_month_id,diff_amount
1,2000,,,
2,2000,2000,1,0
3,2000,2000,2,0
...
12,10000,2000,11,8000
13,2000,10000,12,-8000
14,2000,2000,13,0
...
24,10000,2000,23,8000

In the next step, we use this query as a derived query to get only those rows that have a different payment than the row before (or do not have a preceding row - by adding isnull(.., -1) in the WHERE clause):

select month_id, current_amount, prev_month_id
from (select month_id,
   amount as current_amount,
   sum(amount) over (rows between 1 preceding and 1 preceding) as prev_amount,
   sum(month_id) over (rows between 1 preceding and 1 preceding) as prev_month_id,
   current_amount - prev_amount as diff_amount
from payment_schedule) DT_0
where isnull(diff_amount, -1) <> 0
order by 1

This will return:

month_id,current_amount,prev_month_id
1,2000,(null)
12,10000,11
13,2000,12
24,10000,23

Finally, we want to *add the previous month id from the next row (so, for row 1 the value 11 from the 2nd row) as the "month_to" value. There may be better ways, but I made use of another window function to get that value - adding another derived query. Note the double isnull() call in the select list - this is necessary to handle the last row which doesn't have a following row.

select month_id as month_from,
   isnull(sum(isnull(prev_month_id, month_id))
      over (rows between 1 following and 1 following), month_id) as month_to,
   current_amount
from (select month_id, current_amount, prev_month_id
   from (select month_id,
      amount as current_amount,
      sum(amount) over (rows between 1 preceding and 1 preceding) as prev_amount,
      sum(month_id) over (rows between 1 preceding and 1 preceding) as prev_month_id,
      current_amount - prev_amount as diff_amount
   from payment_schedule) DT_0
   where isnull(diff_amount, -1) <> 0
) DT_1
order by 1

This will return the desired output (and if not, at least I've trained my OLAP understanding...):

month_from,month_to,current_amount
1,11,2000
12,12,10000
13,23,2000
24,24,10000

[Former Member · Accepted solution]

wow! perfect. Thanks Volker. Now i can use this SELECT instead of my stored procedure output 🙂

[VolkerBarth]

I don't think windows functions will help here. Instead, you can use a quite simple GROUP BY. I'm not clear to the id_loan column, however given the fact that you seem to number the monthes in according order, a GROUP BY month_id / 12 will group the according year's data.

This is a working solution, if I do understand the requirements correctly:

-- create test data
create table payment_schedule (
   month_id int primary key,
   id_loan  int,
   amount int);

-- Note: the values() clause with multiple rows is a v12 feature...
insert payment_schedule values
(1,1,2000),
(2,1,2000),
(3,1,2000),
(4,1,2000),
(5,1,2000),
(6,1,2000),
(7,1,2000),
(8,1,2000),
(9,1,2000),
(10,1,2000),
(11,1,2000),
(12,1,10000),
(13,1,2000),
(14,1,2000),
(15,1,2000),
(16,1,2000),
(17,1,2000),
(18,1,2000),
(19,1,2000),
(20,1,2000),
(21,1,2000),
(22,1,2000),
(23,1,2000),
(24,1,10000);

-- query with simple group by 
select min(month_id) as month_from, max(month_id) as month_to, amount
from payment_schedule
group by month_id / 12, amount
order by 1

According output:

month_from,month_to,amount
1,11,2000
12,12,10000
13,23,2000
24,24,10000

[Former Member]

Thanks! But that "group by month_id/12" may not be ideal. On the payment schedule, the bullet payments are not always on an yearly frequency. It varies.

I am trying to do the grouping on every change on the Amount column. But i dont know how.

My table structure is like this: (id "primary key", id_loan "foreign key", ps_due_dt date, ps_installment numeric)

[Breck_Carter]

Perhaps you should be more specific about your requirements, rather than let your sample data do your talking for you 🙂