|
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 |
|
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
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:
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...):
wow! perfect. Thanks Volker. Now i can use this SELECT instead of my stored procedure output :)
(21 Aug '11, 06:36)
Kumar
|
|
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:
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)
(31 Jul '11, 09:09)
Kumar
Replies hidden
Comment Text Removed
Perhaps you should be more specific about your requirements, rather than let your sample data do your talking for you :)
(31 Jul '11, 09:50)
Breck Carter
|
Edited the question a little bit, to add the unknown factor of bullet payment