Solved

Calculate Interest By Day with Month End Balance

Posted on 2006-06-29
9
827 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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