Search code examples
sqlsql-serversql-agent-jobsql-agent

Create a scheduled job using MSSQL Agent

It's my first time working with SQL Agent and i don't have much experience with it!
I need to create a scheduled job that checks few columns of a table everyday and updates one of them based on today's date!

Project table's columns:

CREATE TABLE [dbo].[Project](
[projectID] [int] IDENTITY(1,1) NOT NULL,
[prID] [nvarchar](50) NULL,
[projectName] [nvarchar](20) NOT NULL,
[startDate] [datetime] NOT NULL,
[dueDate] [datetime] NOT NULL,
[progress] [int] NULL,
[status] [bit] NULL,

I need to update the status column everyday based on the progress that the project has this now using the startDate column and today's date. basically something like:

ExpectedProgress = (todayDate-startDate)*(100/(dueDate-StartDate)) Now by comparing the progress column and the ExpectedProgress i can set the value of status column true (on time) or false (late)

Thanks in Advance!

Solution

  • You need to create a stored procedure having the above mentioned query, and then add that stored procedure as a step for the job and schedule it. Your required query should be like:

    Update TableName Set ExpectedProgress = datediff(dd,startDate,todaydate)*(100/daediff(dd,StartDate,duedate))
Update TableName set status=(case when progress>= ExpectedProgress then 'true' else 'false' end.

    For creating a job here are the steps: http://technet.microsoft.com/en-us/library/ms190268.aspx