CODEWITHSUNDEEP
sql-server-2008scheduling
Chris Cashwell
Chris Cashwell

Reputation: 22859

How would I calculate interest daily in SQL Server 2008 using a Stored Procedure?

Basically, I want to take my current Account.Balance and calculate interest once daily, based on the rate defined as Account.InterestRate.

I have table "Account" with (relevant columns) "InterestRate" and "Balance". I need to have a job run nightly (@ midnight?) that calculates the Balance field based on the current value of Balance and the InterestRate.

How might I go about doing this?

Upvotes: 0

Views: 3812

Answers (2)

Chris Cashwell
Chris Cashwell

Reputation: 22859

I figured out a strong way to do it.

Basically, since I can't edit Account.Balance directly, I'm creating a new Transaction record to credit(or debit, if the balance is negative) the interest, which is defined by Account.Balance * Account.Interest_Rate. The calculation I'm using is simple and works well for a very simple form of interest calculations. If, however, I wanted to calculate this once per day for, for instance, 12.99% APR, the calculation would be a little different. Here are examples of both.

To calculate basic daily interest, set up a job to run nightly @ 12:00:00 AM, running the following:

INSERT INTO [Transaction] ([Account_Id], [Amount], [Trans_Type_Id], [DateTime], [Comment])
SELECT Account_Id, Balance * Interest_Rate, 101, GETDATE(), 'Interest' FROM Account WHERE Interest_Rate <> 0.0000

This will create a new transaction once daily for each customer whose Account.Interest_Rate is not 0.0000%.

To calculate APR, say for accounts of CreditCard type, set up a job to run monthly on the 1st of each month @ 12:00:00 AM, running the following:

INSERT INTO [Transaction] ([Account_Id], [Amount], [Trans_Type_Id], [DateTime], [Comment])
SELECT Account_Id, ((Interest_Rate/12) * Balance), 101, GETDATE(), 'Interest' FROM Account WHERE Interest_Rate <> 0.0000 AND Account_Type = 103

(where Account_Type = 103 is "CreditCard")

Upvotes: 0

Vishal
Vishal

Reputation: 12369

You can write a procedure or just straight sql and add the logic for calculating the balance(should be an update with recalculating the balance) something like this - 

Update Account Set balance=balance*interest

and then use sql server agent to create jobs(your sql) to be executed nightly(Note: SQL Server agent won't work on Express editions...in that case you might need to use scheduled tasks on your server and run a batch(.bat) files.)

Upvotes: 1

Related Questions