Find Interest rate on account statement in sql
0
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.
sql oracle
edited Jan 19 at 19:34
a_horse_with_no_name
296k46451546
asked Jan 19 at 16:15
sanketsanket
43
|
show 3 more comments
0
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.
sql oracle
edited Jan 19 at 19:34
a_horse_with_no_name
296k46451546
asked Jan 19 at 16:15
sanketsanket
43
1
"..if count_day=next_date-current_date then". What do you mean by that ?
– Tony
Jan 19 at 16:43
it gets difference between each row to next row of trx_date
– sanket
Jan 19 at 16:50
select (trx_date-lag(trx_date)over(order by trx_date))as dd from int
– sanket
Jan 19 at 16:51
DD ---------- 4 3 7 10 5 5 5 10 5 it gives me output like this to multiply with bal
– sanket
Jan 19 at 16:52
It would help, if you showed the expected result. You want to multiply 0 by four (days) and 7500 by 3 (days), etc. Yes? And interest is this amount * 4 / 36500? And you want the interest column in your results cumulated? 0, 0, 2.47, 6.30, ...?
– Thorsten Kettner
Jan 19 at 16:58
|
show 3 more comments
0
0
0
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.
sql oracle
edited Jan 19 at 19:34
a_horse_with_no_name
296k46451546
asked Jan 19 at 16:15
sanketsanket
43
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.
sql oracle
sql oracle
edited Jan 19 at 19:34
a_horse_with_no_name
296k46451546
asked Jan 19 at 16:15
sanketsanket
43
edited Jan 19 at 19:34
a_horse_with_no_name
296k46451546
asked Jan 19 at 16:15
sanketsanket
43
edited Jan 19 at 19:34
a_horse_with_no_name
296k46451546
edited Jan 19 at 19:34
a_horse_with_no_name
296k46451546
edited Jan 19 at 19:34
a_horse_with_no_name
296k46451546
296k46451546
asked Jan 19 at 16:15
sanketsanket
43
asked Jan 19 at 16:15
sanketsanket
43
asked Jan 19 at 16:15
sanketsanket
43
43
1
"..if count_day=next_date-current_date then". What do you mean by that ?
– Tony
Jan 19 at 16:43
it gets difference between each row to next row of trx_date
– sanket
Jan 19 at 16:50
select (trx_date-lag(trx_date)over(order by trx_date))as dd from int
– sanket
Jan 19 at 16:51
DD ---------- 4 3 7 10 5 5 5 10 5 it gives me output like this to multiply with bal
– sanket
Jan 19 at 16:52
It would help, if you showed the expected result. You want to multiply 0 by four (days) and 7500 by 3 (days), etc. Yes? And interest is this amount * 4 / 36500? And you want the interest column in your results cumulated? 0, 0, 2.47, 6.30, ...?
– Thorsten Kettner
Jan 19 at 16:58
|
show 3 more comments
1
"..if count_day=next_date-current_date then". What do you mean by that ?
– Tony
Jan 19 at 16:43
it gets difference between each row to next row of trx_date
– sanket
Jan 19 at 16:50
select (trx_date-lag(trx_date)over(order by trx_date))as dd from int
– sanket
Jan 19 at 16:51
DD ---------- 4 3 7 10 5 5 5 10 5 it gives me output like this to multiply with bal
– sanket
Jan 19 at 16:52
It would help, if you showed the expected result. You want to multiply 0 by four (days) and 7500 by 3 (days), etc. Yes? And interest is this amount * 4 / 36500? And you want the interest column in your results cumulated? 0, 0, 2.47, 6.30, ...?
– Thorsten Kettner
Jan 19 at 16:58
1
1
"..
if count_day=next_date-current_date then ". What do you mean by that ?– Tony
Jan 19 at 16:43
"..
if count_day=next_date-current_date then ". What do you mean by that ?– Tony
Jan 19 at 16:43
it gets difference between each row to next row of trx_date
– sanket
Jan 19 at 16:50
it gets difference between each row to next row of trx_date
– sanket
Jan 19 at 16:50
select (trx_date-lag(trx_date)over(order by trx_date))as dd from int
– sanket
Jan 19 at 16:51
select (trx_date-lag(trx_date)over(order by trx_date))as dd from int
– sanket
Jan 19 at 16:51
DD ---------- 4 3 7 10 5 5 5 10 5 it gives me output like this to multiply with bal
– sanket
Jan 19 at 16:52
DD ---------- 4 3 7 10 5 5 5 10 5 it gives me output like this to multiply with bal
– sanket
Jan 19 at 16:52
It would help, if you showed the expected result. You want to multiply 0 by four (days) and 7500 by 3 (days), etc. Yes? And interest is this amount * 4 / 36500? And you want the interest column in your results cumulated? 0, 0, 2.47, 6.30, ...?
– Thorsten Kettner
Jan 19 at 16:58
It would help, if you showed the expected result. You want to multiply 0 by four (days) and 7500 by 3 (days), etc. Yes? And interest is this amount * 4 / 36500? And you want the interest column in your results cumulated? 0, 0, 2.47, 6.30, ...?
– Thorsten Kettner
Jan 19 at 16:58
|
show 3 more comments
1 Answer
1
active
oldest
votes
0
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|
+----+--------+-------+
answered Jan 19 at 17:57
Joakim DanielsonJoakim Danielson
8,3723724
Thank you so much...@joakim_danielson
– sanket
Jan 19 at 18:14
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54269009%2ffind-interest-rate-on-account-statement-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
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|
+----+--------+-------+
answered Jan 19 at 17:57
Joakim DanielsonJoakim Danielson
8,3723724
Thank you so much...@joakim_danielson
– sanket
Jan 19 at 18:14
add a comment |
0
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|
+----+--------+-------+
answered Jan 19 at 17:57
Joakim DanielsonJoakim Danielson
8,3723724
Thank you so much...@joakim_danielson
– sanket
Jan 19 at 18:14
add a comment |
0
0
0
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|
+----+--------+-------+
answered Jan 19 at 17:57
Joakim DanielsonJoakim Danielson
8,3723724
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|
+----+--------+-------+
answered Jan 19 at 17:57
Joakim DanielsonJoakim Danielson
8,3723724
edited Jan 19 at 18:10
answered Jan 19 at 17:57
Joakim DanielsonJoakim Danielson
8,3723724
answered Jan 19 at 17:57
Joakim DanielsonJoakim Danielson
8,3723724
answered Jan 19 at 17:57
Joakim DanielsonJoakim Danielson
8,3723724
8,3723724
Thank you so much...@joakim_danielson
– sanket
Jan 19 at 18:14
add a comment |
Thank you so much...@joakim_danielson
– sanket
Jan 19 at 18:14
Thank you so much...@joakim_danielson
– sanket
Jan 19 at 18:14
Thank you so much...@joakim_danielson
– sanket
Jan 19 at 18:14
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54269009%2ffind-interest-rate-on-account-statement-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
"..
if count_day=next_date-current_date then". What do you mean by that ?– Tony
Jan 19 at 16:43
it gets difference between each row to next row of trx_date
– sanket
Jan 19 at 16:50
select (trx_date-lag(trx_date)over(order by trx_date))as dd from int
– sanket
Jan 19 at 16:51
DD ---------- 4 3 7 10 5 5 5 10 5 it gives me output like this to multiply with bal
– sanket
Jan 19 at 16:52
It would help, if you showed the expected result. You want to multiply 0 by four (days) and 7500 by 3 (days), etc. Yes? And interest is this amount * 4 / 36500? And you want the interest column in your results cumulated? 0, 0, 2.47, 6.30, ...?
– Thorsten Kettner
Jan 19 at 16:58