We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Calculate interests in SQL Server 2005

Medium Priority
294 Views
Last Modified: 2012-05-07
Hi guys,

I'm trying to do interest calculations.

-- This guy ows me 100.000. He has to pay interest after due_date which is 2009-03-10
select *
from #Money

-- Here is my interest table
select *
from #Interest


I want to get this results (let's imagine that 29.06.2009 is today so we'll stop there):

date_from       date_to         nr_or_days       interest      interest_amount
11.03.2009     31.03.2009   21                      2.0                 ( 21 / 365 ) *  2.0 * 100.000 = 11.507
01.04.2009     30.04.2009   30                      2.5                 ( 30 / 365 ) * 2.5 * 100.000 = 20.548
01.05.2009     31.05.2009   31                      3.0                 ( 31 / 365 ) * 3.0 * 100.000 = 25.479
01.06.2009     29.06.2009   29                      3.5                 ( 29  / 365 ) * 3.5 * 100.000 = 27.808

nr_of_days is easy:
select *, DATEDIFF (day , date_from , date_to ) as days
from #Interest

Not sure how it's best to do the interest calculations.

What is the best way to let SQL Server 2005 do this?

Thanks a lot ! :)
Endelm

create table #Money(id int primary key identity(1,1), due_date datetime, amount money)
insert into #Money(amount,due_date) values(100000, '2009-03-10')
 
create table #Interest(id int primary key identity(1,1), date_from datetime, date_to datetime, interest_rate float )
insert into #Interest(date_from, date_to, interest_rate) values ('2009-07-01', '2099-12-31', 4.0)
insert into #Interest(date_from, date_to, interest_rate) values ('2009-06-01', '2009-06-30', 3.5)
insert into #Interest(date_from, date_to, interest_rate) values ('2009-05-01', '2009-05-31', 3.0)
insert into #Interest(date_from, date_to, interest_rate) values ('2009-04-01', '2009-04-30', 2.5)
insert into #Interest(date_from, date_to, interest_rate) values ('2009-01-01', '2009-03-31', 2.0)
insert into #Interest(date_from, date_to, interest_rate) values ('2008-07-01', '2008-12-31', 1.5)
insert into #Interest(date_from, date_to, interest_rate) values ('2007-01-01', '2008-06-30', 1.0)
 
select *
from #Money
 
select *
from #Interest
 
 
id          due_date                amount
----------- ----------------------- ---------------------
1           2009-03-10 00:00:00.000 100000,00
 
(1 row(s) affected)
 
id          date_from               date_to                 interest_rate
----------- ----------------------- ----------------------- ----------------------
1           2009-07-01 00:00:00.000 2099-12-31 00:00:00.000 4
2           2009-06-01 00:00:00.000 2009-06-30 00:00:00.000 3,5
3           2009-05-01 00:00:00.000 2009-05-31 00:00:00.000 3
4           2009-04-01 00:00:00.000 2009-04-30 00:00:00.000 2,5
5           2009-01-01 00:00:00.000 2009-03-31 00:00:00.000 2
6           2008-07-01 00:00:00.000 2008-12-31 00:00:00.000 1,5
7           2007-01-01 00:00:00.000 2008-06-30 00:00:00.000 1
 
(7 row(s) affected)

Open in new window

Comment
Watch Question

Commented:
You can have some thing like this and join both tables on ID.
 
 

Select
I.ID
,I.Date_From
,I.Date_TO
, DATEDIFF (day , I.date_from , I.date_to ) as No_Days
,I.Interest_rate
,(DATEDIFF (day , I.date_from , I.date_to )/365) * I.Interest_rate * M.Amount As Interst_Amount
FROM #Interest I
Join #Money M
on I.id=M.ID
--Result for the example you gave is one row as shown below
1	2009-07-01 00:00:00.000	2099-12-31 00:00:00.000	33055	4	36000000

Open in new window

Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Chris LuttrellSenior Database Architect
CERTIFIED EXPERT

Commented:
Hi Endelm,
Mark's solution is calculating right but I bet the #days and interest amount should stop on the Due Date and not the end of period date so I modified it as below and get the following results.
Select Date_From, Date_TO, DATEDIFF (day , date_from , due_date ) as No_Days ,Interest_rate,(DATEDIFF (day , date_from , due_date )*1.0/365) * (Interest_rate/100.00) * Amount As Interst_Amount
FROM #Money
INNER JOIN #interest on due_date between date_from and date_to
 
Results
Date_From			Date_TO			No_Days	Interest_rate	Interst_Amount
2009-01-01 00:00:00.000	2009-03-31 00:00:00.000	68	2		372.602

Open in new window

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Good pickup... Was thinking that at the time (when possibly picking up multiple dates), but for whatever reason ignored it...
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Was also thinking that it should be GETDATE()...

In reality I still think we are missing some information. Either GETDATE() is used to get the current interest, or maybe due_date, and then the period might also need to use either getdate() or due_date...

But in the Askers question at the top, it does show :

   nr_of_days is easy:
   select *, DATEDIFF (day , date_from , date_to ) as days

so, left it as that...
Chris LuttrellSenior Database Architect
CERTIFIED EXPERT

Commented:
I agree with you Mark, that is how the question was stated, and there needs to be some clarification and direction from the asker if the answer needs to be refined, otherwise we are just speculating.
Cheers,
Chris

Author

Commented:
Thanks a lot! :)
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
A Pleasure. Very happy to have been of some assistance...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.