• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 868
  • Last Modified:

Calculate Interest By Day with Month End Balance

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
Angela_Wilcox
Asked:
Angela_Wilcox
  • 4
  • 4
1 Solution
 
MageDribbleCommented:
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
 
Angela_WilcoxAuthor Commented:
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
 
Angela_WilcoxAuthor Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
devsolnsCommented:
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
 
devsolnsCommented:
By the way...... myTransactions is just a table with a TransDate and TransAmt column with a bunch of data.  

--DS
0
 
Angela_WilcoxAuthor Commented:
I'll give it a try using my tables.  Thank you!
0
 
devsolnsCommented:
No problem.......glad to help.

--DS
0
 
devsolnsCommented:
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
 
Angela_WilcoxAuthor Commented:
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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now