i hava table of statement and it has the date,credit,debit and balance column through these i want to find total interest.
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| +----+--------+-------+