Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Calculate Interest By Day with Month End Balance

Posted on 2006-06-29
9
833 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

829 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