Solved

Calculate Interest By Day with Month End Balance

Posted on 2006-06-29
9
837 Views
Last Modified: 2012-05-05
I have the following data in 1 table:

Account      Transaction        Transaction
                Date                   Amount
1               05/01/2006        1000.00
1               05/05/2006         3000.00
1               05/20/2006         -500.00
1               05/21/2006         4000.00
1               05/24/2006         -200.00

I have the ending balance in another table:
Ending Balance      6300.00

WHAT I NEED TO GET TO IS:

                Transaction                         Ending        (.045 * Balance)/365
Account      Date                Amount        Balance       Interest
1               05/01/2006      1000.00      3800.00       .05
1               05/02/2006                        3800.00       .05
1               05/03/2006                        3800.00       .05
1               05/04/2006                        3800.00       .05
1               05/05/2006      -800.00       3000.00       .04
1               05/06/2006                        3000.00       .04
1               05/07/2006                        3000.00       .04
1               05/08/2006                        3000.00       .04
1               05/09/2006                        3000.00       .04
1               05/10/2006                        3000.00       .04
1               05/11/2006                        3000.00       .04
1               05/12/2006                        3000.00       .04
1               05/13/2006                        3000.00       .04
1               05/14/2006                        3000.00       .04
1               05/15/2006                        3000.00       .04
1               05/16/2006                        3000.00       .04
1               05/17/2006                        3000.00       .04
1               05/18/2006                        3000.00       .04
1               05/19/2006                        3000.00       .04
1               05/20/2006      -500.00       2500.00       .03
1               05/21/2006      4000.00      6500.00       .08
1               05/22/2006                        6500.00       .08
1               05/23/2006                        6500.00       .08
1               05/24/2006      -200.00       6300.00       .08
1               05/25/2006                        6300.00       .08
1               05/26/2006                        6300.00       .08
1               05/27/2006                        6300.00       .08
1               05/28/2006                        6300.00       .08
1               05/29/2006                        6300.00       .08
1               05/30/2006                        6300.00       .08

Total Monthly Interest 1.63

Any help on this is greatly appreciated!!
0
Comment
Question by:Angela_Wilcox
  • 4
  • 4
9 Comments
 
LVL 5

Expert Comment

by:MageDribble
ID: 17012191
you're best bet is to create a view.  Add a column called interest earned and put your formula as the column.

you can then summarize the data as needed.

Did you want more detail?
0
 

Author Comment

by:Angela_Wilcox
ID: 17012342
Please!  I can't figure out an easy way to generate the days that I don't have transactions for - and to calcuate the balance for each day using the ending balance for the month (I cant' get the starting balance).
0
 

Author Comment

by:Angela_Wilcox
ID: 17012413
I should have noted that there can be multiple transactions on a date (not represented in my example) and I only want to calculate interest on the daily ending balance.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 13

Accepted Solution

by:
devsolns earned 500 total points
ID: 17013269
This took me FORVER!  But I think it is what you need.




--Temp table to hold the data
CREATE TABLE #NewTable
(
MyDate datetime not null,
TranAmount int not null,
NewBalance int not null,
Interest decimal(16,0) not null,
)



DECLARE @myDateCounter int
DECLARE @EndBalance int
DECLARE @StartBalance int
DECLARE @Interest decimal(16,4)

--Will get the next months dates
SET @myDateCounter = DatePart(day,DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, getdate()) + 1, 0))) - DatePart(day,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

--Get the balance from another table.....SELECT @EndBalance = mybalance FROM mytable
SET @EndBalance = 6500

WHILE (@myDateCounter != -1)
BEGIN

SET @StartBalance =       ISNULL((
                                    SELECT Sum(TransAmt)
                                    FROM myTransactions
                                    WHERE Convert(varchar,TransDate,101) = Convert(varchar,DateAdd(day, @myDateCounter, DateAdd(month, DateDiff(month, 0, getdate()) + 1, 0)),101)
                                    GROUP BY TransDate
                                    ),0)
SET @Interest = (.045 * @EndBalance)/365


      INSERT INTO #NewTable
      VALUES(
      Convert(varchar,DateAdd(day, @myDateCounter, DateAdd(month, DateDiff(month, 0, getdate()) + 1, 0)),101),
      @StartBalance,
      @EndBalance - @StartBalance,
      @Interest
      )
      SET @myDateCounter = @myDateCounter - 1
      SET @EndBalance = @EndBalance - @StartBalance
END

SELECT * FROM #NewTable

DROP Table #NewTable



Hope it helps!

--Dustin Sterkenburg
0
 
LVL 13

Expert Comment

by:devsolns
ID: 17013302
By the way...... myTransactions is just a table with a TransDate and TransAmt column with a bunch of data.  

--DS
0
 

Author Comment

by:Angela_Wilcox
ID: 17013351
I'll give it a try using my tables.  Thank you!
0
 
LVL 13

Expert Comment

by:devsolns
ID: 17013467
No problem.......glad to help.

--DS
0
 
LVL 13

Expert Comment

by:devsolns
ID: 17013514
I just realized that this will give you all the dates for next month based on your system date....you just have to change it a little to get this months.....   :o)
0
 

Author Comment

by:Angela_Wilcox
ID: 17043301
With some modification - I was able to get the example to work with my database and views.  I'm working on improving the performance as it is very slow...
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

713 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