0

Everyday on first run of sql job, I need to update some columns. My job will start at 4am everyday.

so I can do this:

IF(CONVERT(VARCHAR(8),GETDATE(),108) = '04:00:00')
BEGIN
 // Update
END

But, if because of some reason my job couldn't run at 4am. How can I reset these columns. Any idea, suggestion, help.

My requirement is as follows: My system gets tasks from my end users. This job needs to assign these tasks to employees based on their capability. So,I have a table with EmpId, MaxAssignments(Maximum number of tasks can be assigned in a day) & AssignmentsCount(Number of tasks that got assigned) column. Everyday on 1st run I want to set this "AssignmentsCount" value to 0.

User 4989588
  • 113
  • 3
  • 12
  • 2
    Maybe do that update in a separate job that only runs once a day? – GolezTrol Dec 01 '15 at 09:42
  • why you choose 4am and not 00.01 am? what should happen if the schedule is moved at 3am? should the sql code be altered to match? please explain the requirement and not the solution you are trying to put in place... – Paolo Dec 01 '15 at 10:34
  • @Paolo I added my requirement to question – User 4989588 Dec 01 '15 at 11:01
  • 1
    Related: http://stackoverflow.com/questions/2112838/get-date-of-last-successful-job-run – Anthony Grist Dec 01 '15 at 11:07
  • 1
    Is there not a table containing details of the actual assignments? If so, could this not have (if it doesn't already) a datetime column recording when the assignments were made? Thereby making `AssignmentsCount` redundant since you can just add up the actual totals whenever you need them, and you also don't need to do this maintenance task. – Damien_The_Unbeliever Dec 01 '15 at 11:37

1 Answers1

0

You should be able to use code similar what is shown below within your job step. The SQL statements are broken apart with comments for explanation. For brevity you could combine them or even make then into a separate scalar function that takes a job identifier and returns a true or false if its the first run.

DECLARE @job_id uniqueidentifier
DECLARE @run_requested_date DATETIME
DECLARE @first_run_today DATETIME

-- Use Tokens in Job Steps
-- https://msdn.microsoft.com/en-us/library/ms175575.aspx
-- Within your SQL Agent job step you can use this tokenized
-- statement to get the job_id.
--SET @job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))

-- You can use this statement instead, but it will break if the
-- job name is changed and you forget to update the [name]
-- parameter below ('Test' is the job name in this example)
SELECT @job_id = job_id
  FROM msdb.dbo.sysjobs
 WHERE name = 'Test'

-- Debug statement
PRINT 'job_id = ' + CONVERT(VARCHAR(255), @job_id)

-- Get the scheduled run time for this currently
-- executing job (can alternatively use the
-- start_execution_date column instead)
SELECT @run_requested_date = run_requested_date 
  FROM msdb.dbo.sysjobactivity 
 WHERE job_id = @job_id

-- Debug statement
PRINT 'run_requested_date = ' + CONVERT(VARCHAR(50), @run_requested_date, 126)

-- For the given job, find any job history row that
-- was successfully execute at an earlier time on 
-- this date.
--
-- The msdb.dbo.agent_datetime() function is a built-in
-- function that will values from the separate date and time
-- columns in the job history table and convert them to a
-- single datetime value.
SELECT @first_run_today = msdb.dbo.agent_datetime(run_date, run_time) 
  FROM msdb.dbo.sysjobhistory 
 WHERE JOB_ID = @job_id 
   AND run_status = 1 -- Successful
   AND run_date = CONVERT(VARCHAR(10), @run_requested_date, 112) -- YYYYMMDD format compare
   AND msdb.dbo.agent_datetime(run_date, run_time) < @run_requested_date

-- Debug statement
PRINT 'first_run_today = ' + CONVERT(VARCHAR(50), @first_run_today, 126)

-- If the first_run_today variable is null then
-- that means for the current date we did not
-- any successful runs of this job before now.
IF @first_run_today IS NULL
BEGIN
  PRINT 'This *is* the first run.'
  -- First run statements go here.
END
ELSE
BEGIN
  PRINT 'This is *not* the first run.'
  -- Non first run statements go here.
END