SSMS - Changing Column Values on a daily, weekly, and monthly basis

Question

I'm still fairly new to SSMS, but I'm trying to implement a weekly and monthly leaderboard for AI characters, which will be updated daily based on the average steps value in the database, with a variance of 0.5x - 1.5x that value.

I also want to reset the weekly and monthly values every week and month, respectively (not worried about storing old scores for now).

How would I do this in a database?

here is my table:

Trainer Table Id, Name, AverageSteps, WeeklySteps, MonthySteps

I'm fine on the math part of getting the values, but

• How can I run an event/script to update the weekly and monthly steps with the average steps value (this would be done once a day)?
• How can I run an event/script to reset the weekly steps each week, and monthly steps each month?

I know what I want to do is possible - I'm just not sure how - does anyone know any good tutorials on this subject?

Answer 1

I'd create a stored procedure to perform the updates. Then I'd schedule the stored proc to run daily using a SQL Server Agent job.

In your stored proc, you'd have three sections. The first section of code would be run every time the stored proc ran, and it would update the WeeklySteps and MonthlySteps values with the AverageSteps value.

The next section would be contained in an IF block, and would reset the WeeklySteps value. Your IF condition would be something like:

IF DATEPART(WEEKDAY,GETDATE()) = 1 --This is true only on Sundays

The last section would also be contained in an IF block, and would reset the MonthlySteps value. Your IF condition would be something like:

IF DATEPART(DAY,GETDATE()) = 1 --This is true only on the first of the month

Answer 2

Like @ADyson said, a SQL Agent job will take care of this just fine. You can set a job up to run on a monthly/weekly/daily basis.

Here is a link to the Microsoft doc for setting up a SQL Agent job: https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-job

There are definitely other options for scheduling jobs, but since what you're doing is contained within SQL Server, an Agent job makes the most sense.