i hava table of statement and it has the date,credit,debit and balance column through these i want to find total interest.
x
TRX_DATE CREDIT DEBIT BAL
-------- ---------- ---------- ----------
01-11-18 0
05-11-18 7500 7500
08-11-18 2500 5000
15-11-18 10000 15000
25-11-18 9000 6000
30-11-18 1000 5000
05-12-18 7100 12100
10-12-18 2100 10000
20-12-18 20000 30000
25-12-18 15000 15000
for finding interest :
- if count_day=next_date-current_date then
- (4*(sum(count*bal)))/36500
I tried and get row of each trx
interest but didn’t get total or sum of the rows. and while trying sum() on the query error shows ORA-30483: window functions are not allowed here
select ((4*(bal*(trx_date-((lag(trx_date) over (order by trx_date))))))/36500)as interest
from int i;
interest
------------
78.
Advertisement
Answer
Here is my solution using LEAD
SELECT ROUND(4.0 * SUM(bal * days / 36500), 2) as Interest
FROM (
SELECT bal, NVL(LEAD(trx_date) OVER (ORDER BY trx_date) - trx_date, 0) days
FROM transaction)
This gives an accrued interest of 62.79 for the sample data. Below are the individual interest amounts
+----+--------+-------+
|DAYS| BAL| INT|
+----+--------+-------+
| 4| 0| 0|
| 3| 7500| 2.47|
| 7| 5000| 3.84|
| 10| 15000| 16.44|
| 5| 6000| 3.29|
| 5| 5000| 2.74|
| 5| 12100| 6.63|
| 10| 10000| 10.96|
| 5| 30000| 16.44|
| 0| 15000| 0|
+----+--------+-------+