Solved

Calculate interests in SQL Server 2005

Posted on 2009-07-10
8
259 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

0
Comment
Question by:Endelm
8 Comments
 
LVL 8

Expert Comment

by:Hadush
ID: 24825044
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

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 24825820
Well you appear to be missing some vital pieces of information... Like a start date, or, if the MONEY amount is just outstanding, or, if it is a retrospective calculation showing total interest over the various periods etc...

So, let us assume it is the amount outstanding and you need to select the most appropriate interest rate... Let us also assume there is no overlapping periods... And finally that the interest rate represents a percentage, and it is obviously a simple interest calculation.

Right then... Please try :

Select Date_From, Date_TO, DATEDIFF (day , date_from , date_to ) as No_Days ,Interest_rate,(DATEDIFF (day , date_from , date_to )*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






0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24826895
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

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24827451
Good pickup... Was thinking that at the time (when possibly picking up multiple dates), but for whatever reason ignored it...
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 51

Expert Comment

by:Mark Wills
ID: 24827483
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...
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24828077
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
0
 

Author Closing Comment

by:Endelm
ID: 31602143
Thanks a lot! :)
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24840735
A Pleasure. Very happy to have been of some assistance...
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now