0

I have a database with the following columns:

CurrentCredits, CreditsPerDate, RefillDate, RefillFrequency, RefillDayMonth

I'd like to make it automatically update the CreditsPerDate and RefillDate depending on the frequency selected.

For example, 

if RefillFrequency = 5 and RefillDayMonth = "day" and CreditsPerMonth = 2

I want it to update CurrentCredits and RefillDate every 5 days such that 

CurrentCredits = CurrentCredits + CreditsPerMonth and 
RefillDate = RefillDate + the frequency date (here add 5 days)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sylvain Elias
  • 21
  • 1
  • 6
  • 1
    Have a look at [SQL Server Agent](https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent?view=sql-server-2017). – sticky bit May 25 '19 at 23:04

2 Answers2

0

Did you try with a SQL job?. Here you have an example: how to schedule a job for sql query

Inside your job you can create a update or command with stored procedure like 

exec (your stored procedure name) (and possibly add parameters)

just updating your parameters and values.

Let us know if this helps.

Regards.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Patricio
  • 7
  • 4
0

You can't get a table to do that by itself. I would say go with Patricio's idea and schedule a job to run. Write a stored proc and have that run daily to run calculations. You will need additional columns probably to help facilitate this as you will need to know when a row was last updated and use that date (ie, UpdateDt) to determine if some values will need to be recalculated.

superztnt
  • 51
  • 4