Find conflicting SQL job schedules -- Best Script

http://education.sqlfarms.com/education/ShowPost.aspx?PostID=1225
=========================================================================

The following script is useful to job scheduling conflicts: If a job has more than one schedule, then it is possible that to have conlicting schedules, in which case the job may be invoked multiple times in a span of a few seconds. Schedule conflicts are found by finding all the run-dates and run-times for all jobs (or one job if requested by the user) and then finding whether two schedules run in less than X amount of seconds from one another.

Example: If a job has two schedules, one is monthly and another is weekly, and some dates and times coincide, then there would be conflicts in the job schedules.

Notes: The script is version-aware and works on SQL Server 2000 and 2005.
The script only examines enabled jobs and enabled schedules.

The actions taken by the script are as follows: The script accepts a start-time (@StartDateTimeToCalculate) and an end-time (@EndDateTimeToCalculate), which should be specified by the user. By default, the script analyzes all jobs on the server. If you wish to only analyze a single job, then specify the job_id for the desired job in the variable @JobID.

Step 1 - The script captures a snapshot of all needed SQL job information, including the job schedules, first step to run, active start & end time, creation date, and so on - everything that is needed to determine the dates and times when the jobs should have been run.

Step 2 - Reconstruct and revese engineer all the dates in which all jobs (or only the job in @JobID) are scheduled to run, between the dates @StartDateTimeToCalculate and @EndDateTimeToCalculate (note - if the script finds that these dates are two far apart,
it will normalize the time window and only consider relevant times). This is done in several steps:

Step 2.1 - jobs that are scheduled to run once
Step 2.2 - daily jobs
Step 2.3 - monthly (fixed) jobs
Step 2.4 - monthly (relative) jobs.
For more information about fixed and relative monthly
jobs, please see the SQL Server Books Online
(look up sysjobschedules in SQL 2000 or sysschedules in 2005).

The reconstructed run-dates found in Step 2 are stored in a temp table called #JobScheduledDates.

Step 3 - Find the run-times (times in which jobs are scheduled to run) for all the run-dates found in Step 2. The dates and times in which the SQL jobs are scheduled to run are stored in the temp table called #JobScheduledDatesAndTimes.

Step 4 - Find all scheduling conflicts by comparing the dates and times found in Step 3. If any two schedules fire less than X amount of seconds from one another (and X is configurable as the variable @TimeGapInSeconds), then all such encounters are considered
as schedule conflicts and will be reported back to the user.

User parameters:
@StartDateTimeToCalculate - start time to examine
@EndDateTimeToCalculate - end time to examine
@JobID - if users wish to analyze one job, then
set this variable to the wanted job_id value.
To get info for all server jobs - leave @JobID NULL.
@TimeGapInSeconds - see explanation for Step 4 above.

Note: The script is optimized for readability. Not for speed. To use the script effectively, the user should specify a wide enough time range, in which case it may takes several minutes for the script to complete.
-------------------------------------------
/* ============================================================================
Copyright: SQL Farms, Inc., www.sqlfarms.com. All rights reserved.
This code can be used only for non-redistributable purposes.
The code can be used for free as long as this copyright notice is not removed.
===============================================================================

The following script is useful to job scheduling conflicts:
If a job has more than one schedule, then it is possible that
to have conlicting schedules, in which case the job may
be invoked multiple times in a span of a few seconds.
Schedule conflicts are found by finding all the run-dates
and run-times for all jobs (or one job if requested by the
user) and then finding whether two schedules run in less
than X amount of seconds from one another.

Example: If a job has two schedules, one is monthly and another
is weekly, and some dates and times coincide, then there would
be conflicts in the job schedules.

Notes: The script is version-aware and works on SQL Server 2000 and 2005.
The script only examines enabled jobs and enabled schedules.

The actions taken by the script are as follows:
The script accepts a start-time (@StartDateTimeToCalculate)
and an end-time (@EndDateTimeToCalculate), which should be specified
by the user. By default, the script analyzes all jobs on the server.
If you wish to only analyze a single job, then specify the job_id
for the desired job in the variable @JobID.

Step 1 - The script captures a snapshot of all needed SQL job
information, including the job schedules, first step to run,
active start & end time, creation date, and so on - everything
that is needed to determine the dates and times when the jobs
should have been run.

Step 2 - Reconstruct and revese engineer all the dates in which
all jobs (or only the job in @JobID) are scheduled to run, between
the dates @StartDateTimeToCalculate and @EndDateTimeToCalculate
(note - if the script finds that these dates are two far apart,
it will normalize the time window and only consider relevant times).
This is done in several steps:

Step 2.1 - jobs that are scheduled to run once
Step 2.2 - daily jobs
Step 2.3 - monthly (fixed) jobs
Step 2.4 - monthly (relative) jobs.
For more information about fixed and relative monthly
jobs, please see the SQL Server Books Online
(look up sysjobschedules in SQL 2000 or sysschedules in 2005).

The reconstructed run-dates found in Step 2 are stored in a temp
table called #JobScheduledDates.

Step 3 - Find the run-times (times in which jobs are scheduled
to run) for all the run-dates found in Step 2. The dates and times
in which the SQL jobs are scheduled to run are stored in the temp
table called #JobScheduledDatesAndTimes.

Step 4 - Find all scheduling conflicts by comparing the
dates and times found in Step 3.
If any two schedules fire less than X amount of seconds from
one another (and X is configurable as the variable
@TimeGapInSeconds), then all such encounters are considered
as schedule conflicts and will be reported back to the user.

User parameters:
@StartDateTimeToCalculate - start time to examine
@EndDateTimeToCalculate - end time to examine
@JobID - if users wish to analyze one job, then
set this variable to the wanted job_id value.
To get info for all server jobs - leave @JobID NULL.
@TimeGapInSeconds - see explanation for Step 4 above.

Note: The script is optimized for readability. Not for speed.
To use the script effectively, the user should specify a wide
enough time range, in which case it may takes several minutes
for the script to complete.


Date Developer Notes Version
========= =============== ================================= ===========
06/15/2007 Omri Bahat Initial release 1.00
==================================================================================
Copyright: SQL Farms, Inc., www.sqlfarms.com. All rights reserved.
This code can be used only for non-redistributable purposes.
The code can be used for free as long as this copyright notice is not removed.
==================================================================================*/

DECLARE @StartDateTimeToCalculate DATETIME
DECLARE @EndDateTimeToCalculate DATETIME
DECLARE @TimeGapInSeconds INT
DECLARE @JobID NVARCHAR(64)

-- Time window to find conflicting schedules.
SET @StartDateTimeToCalculate = '10/1/2007'
SET @EndDateTimeToCalculate = '12/1/2007'

-- If two schedules have a gap of X seconds between 2 runs,
-- then the schedules are considered as conflicting.
-- X is stored as the variable @TimeGapInSeconds.
SET @TimeGapInSeconds = 30

-- To look at a specific job, specify the job id below.
-- To look at all jobs- leave it NULL.
SET @JobID = NULL -- valid values are NULL or job id, e.g., 'E37DD871-F0B7-44B9-9646-37144E03DC14'


-- Validate start and end times
IF @StartDateTimeToCalculate > @EndDateTimeToCalculate
BEGIN
RAISERROR('The start and end dates entered are not valid. Aborting.', 16, 1)
RETURN
END


-- Validate that the request job exists (if @JobID or @JobName are non-null)
IF @JobID IS NOT NULL AND NOT EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE job_id = @JobID)
BEGIN
RAISERROR('The requested job id does not exist on the server.', 16, 1)
RETURN
END

SET NOCOUNT ON

/* ========================================================
Step 1 - Take a snapshot of all jobs and schedules.

We start by recording the info for all jobs and schedules.
This info is first taken from the msdb database. Some fields
in msdb may not be accurate, so we also override the msdb
info (if needed) with information from the SQL Agent itself.

There are 2 goals here:
1. Take a snapshot of all jobs so if any new jobs and schedules
are changed on the server when the script runs, they will
not be considered.
2. Calculate a next_run_date and next_run_time for each job.
We only need this info to identify a date & time value
where the job was run or was scheduled to run. This
will help us to reverse-engineer the schedule of the job
between @StartDateTimeToCalculate and @EndDateTimeToCalculate.
This info is not used for any other purposes.
====================================================== */

DECLARE @sSPID VARCHAR(32)
DECLARE @SQLStr NVARCHAR(4000)

-- Get a snapshot of all enabled jobs and their associated schedule-parameters.
-- Note: The next run date stored in SQL Server (sysjobschedules) may not be accurate.
-- We begin by taking the values of next_run_date and next_run_time from sysjobschedules
-- and then override this value with the information return from the SQL Server Agent, if available.
IF OBJECT_ID('tempdb..#JobScheduleInfo') IS NOT NULL
DROP TABLE #JobScheduleInfo

CREATE TABLE #JobScheduleInfo (
job_id VARCHAR(64),
job_name NVARCHAR(128) COLLATE DATABASE_DEFAULT,
job_owner NVARCHAR(128) COLLATE DATABASE_DEFAULT,
schedule_id INT,
schedule_name NVARCHAR(128) COLLATE DATABASE_DEFAULT,
freq_type INT,
freq_interval INT,
freq_subday_type INT,
freq_subday_interval INT,
freq_relative_interval INT,
freq_recurrence_factor INT,
active_start_date INT,
active_end_date INT,
active_start_time INT,
active_end_time INT,
date_created DATETIME,
min_datetime_to_consider DATETIME, -- Indicates the min datetime to consider when returning results.
max_datetime_to_consider DATETIME, -- Indicates the max datetime to consider when returning results.
start_step_id INT,
next_run_date INT, -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
next_run_time INT) -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.


EXEC('CREATE UNIQUE CLUSTERED INDEX CI_#JobScheduleInfo_' + @sSPID + '
ON #JobScheduleInfo (job_id, schedule_name) ')


-- The code to get the job and schedule info is different
-- between SQL 2K and 2K5.
IF CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), 2) AS DECIMAL) < 9
SET @SQLStr = @SQLStr + '
INSERT INTO #JobScheduleInfo
SELECT CAST(a.job_id AS VARCHAR(64)),
a.name,
SUSER_SNAME(a.owner_sid),
b.schedule_id,
b.name,
b.freq_type,
b.freq_interval,
b.freq_subday_type,
b.freq_subday_interval,
b.freq_relative_interval,
b.freq_recurrence_factor,
b.active_start_date,
b.active_end_date,
b.active_start_time,
b.active_end_time,
a.date_created,
-- Initial value (updated later)
DATEADD(ss,
CAST(active_start_time/10000 AS INT)*3600 + (CAST(active_start_time/100 AS INT)%100)*60 + (active_start_time % 100),
CAST(CAST(active_start_date AS VARCHAR(8)) AS DATETIME)
),
-- Initial value (updated later)
DATEADD(ss,
CAST(active_end_time/10000 AS INT)*3600 + (CAST(active_end_time/100 AS INT)%100)*60 + (active_end_time % 100),
CAST(CAST(active_end_date AS VARCHAR(8)) AS DATETIME)
),
a.start_step_id,
b.next_run_date, -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
b.next_run_time -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysjobschedules b
ON a.job_id = b.job_id
AND a.enabled = 1
AND b.enabled = 1 '
ELSE
SET @SQLStr = @SQLStr + '
INSERT INTO #JobScheduleInfo
SELECT CAST(a.job_id AS VARCHAR(64)),
a.name,
SUSER_SNAME(a.owner_sid),
b.schedule_id,
b.name,
b.freq_type,
b.freq_interval,
b.freq_subday_type,
b.freq_subday_interval,
b.freq_relative_interval,
b.freq_recurrence_factor,
b.active_start_date,
b.active_end_date,
b.active_start_time,
b.active_end_time,
a.date_created,
-- Initial value (updated later)
DATEADD(ss,
CAST(active_start_time/10000 AS INT)*3600 + (CAST(active_start_time/100 AS INT)%100)*60 + (active_start_time % 100),
CAST(CAST(active_start_date AS VARCHAR(8)) AS DATETIME)
),
-- Initial value (updated later)
DATEADD(ss,
CAST(active_end_time/10000 AS INT)*3600 + (CAST(active_end_time/100 AS INT)%100)*60 + (active_end_time % 100),
CAST(CAST(active_end_date AS VARCHAR(8)) AS DATETIME)
),
a.start_step_id,
z.next_run_date, -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
z.next_run_time -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysjobschedules z
ON a.job_id = z.job_id
INNER JOIN msdb.dbo.sysschedules b
ON z.schedule_id = b.schedule_id
AND a.enabled = 1
AND b.enabled = 1'

IF @JobID IS NOT NULL
BEGIN
SET @SQLStr = @SQLStr + CHAR(10) + CHAR(13) + ' WHERE a.job_id = ' + @JobID
SET @SQLStr = 'IF EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE job_id = ' + @JobID
END

EXEC(@SQLStr)


-- Update min_datetime_to_consider - update occurs if date_created is greater than the active_start_date + time
-- or when @StartDateTimeToCalculate is greater than both of these values.
UPDATE #JobScheduleInfo
SET min_datetime_to_consider = CASE
WHEN date_created < @StartDateTimeToCalculate THEN @StartDateTimeToCalculate
ELSE date_created
END
WHERE -- Only update if the min_datetime_to_consider is smaller than the other dates.
min_datetime_to_consider < CASE
WHEN date_created < @StartDateTimeToCalculate THEN @StartDateTimeToCalculate
ELSE date_created
END

-- Similarly, update the max_date_to_consider
UPDATE #JobScheduleInfo
SET max_datetime_to_consider = @EndDateTimeToCalculate
WHERE max_datetime_to_consider > @EndDateTimeToCalculate -- Only update if the max_datetime_to_consider is greater than the other date.

-- Check for bogus values- when max_datetime_to_consider <= min_datetime_to_consider
UPDATE #JobScheduleInfo
SET max_datetime_to_consider = min_datetime_to_consider
WHERE max_datetime_to_consider <= min_datetime_to_consider


-- In order to get an accurate next_run_date or next_run_time,
-- we refer to the SQL agent and get whatever information is available there.
-- This is done by calling the (undocumented) extended stored proc
-- xp_sqlagent_enum_jobs. Note that xp_sqlagent_enum_jobs will only give
-- us the next run date & time for one schedule for the job.
-- In spite of this fact, we still call it because it may contain better
-- info than sysjobschedules, even if it only for a few jobs and schedules.

IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
DROP TABLE #xp_results

CREATE TABLE #xp_results (
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id SYSNAME COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)



-- Prerequisites: In SQL 2K5 and above, the 'Agent XPs' server configuration
-- option must be set to true. The script assumes that it is true.
-- See sp_configure for additional information.

DECLARE @CurrJobOwner NVARCHAR(128)

SELECT @CurrJobOwner = MIN(job_owner)
FROM #JobScheduleInfo

WHILE @CurrJobOwner IS NOT NULL
BEGIN
INSERT INTO #xp_results
EXEC master.dbo.xp_sqlagent_enum_jobs 1, @CurrJobOwner

SELECT @CurrJobOwner = MIN(job_owner)
FROM #JobScheduleInfo
WHERE job_owner > @CurrJobOwner
END


-- Update #JobScheduleInfo with the information from #xp_results
-- for the relevant jobs and schedules. We do not update records
-- where the next_run_date is null or zero in #xp_results.
-- Those entries mean that the SQL Agent has not yet calculated
-- the next run date & time.

UPDATE a
SET next_run_date = b.next_run_date,
next_run_time = b.next_run_time
FROM #JobScheduleInfo a
INNER JOIN #xp_results b
ON a.job_id = b.job_id
AND a.schedule_id = b.next_run_schedule_id
AND b.next_run_date IS NOT NULL
AND b.next_run_time IS NOT NULL
AND b.next_run_date > 0



/* ========================================================================
Step 2 - Find all dates between @StartDateTimeToCalculate
and @EndDateTimeToCalculate in which the job is
scheduled to run.

For each job and every schedule in #JobScheduleInfo, we now get the dates
and the first time in each date in which the job is scheduled to run.
We create the temp table #JobScheduledDates for this purpose, which includes
the following fields:

RunDate - dates to run (datetime)
StartTimeEx (normalized format for active_start_time)
StartTimeInSecFromStartOfDay (# of seconds from the
beginning of the day when the job runs for the first time
in that day)
=========================================================================== */

IF OBJECT_ID('tempdb..#JobScheduledDates') IS NOT NULL
DROP TABLE #JobScheduledDates

CREATE TABLE #JobScheduledDates (
job_id VARCHAR(64),
schedule_name NVARCHAR(128),
RunDate DATETIME,
StartTimeEx CHAR(6),
StartTimeInSecFromStartOfDay INT)

EXEC('CREATE UNIQUE CLUSTERED INDEX CI_#JobScheduledDates_' + @sSPID + '
ON #JobScheduledDates (job_id, schedule_name, RunDate) ')

/* =========================================================
Step 2.1 - jobs that run once - freq_type = 1

freq_type = 1 for jobs that are scheduled to run once,
so calculating the RunDate and StartTime is not difficult
and can be done for all server jobs in one statement.
-- ======================================================= */

INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT job_id,
schedule_name,
CAST(CAST(active_start_date AS VARCHAR(8)) AS DATETIME),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(active_start_time AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(active_start_time AS VARCHAR(6))))
FROM #JobScheduleInfo
WHERE freq_type = 1
AND CAST(CAST(active_start_date AS VARCHAR(8)) AS DATETIME) BETWEEN @StartDateTimeToCalculate AND @EndDateTimeToCalculate


/* ==========================================================================
Step 2.2 - daily jobs - freq_type = 4 (freq_subday_type = 1, 4, 8)

This case is when a job runs every X days (freq_interval = X).
Calculating the dates for these jobs is not straightforward.
In order to figure out when the job was scheduled to run in the past
or will run in the future, we need to look at the next_run_date.
The reason is- if I schedule a job to run (say) every 5 days,
the first date in which the job will run is non-deterministic
and depends on the job creation date, the computer clock, and other
parameters. In other words, we look at next_run_date to latch
onto a deterministic date that is associated with the job.
This is done in a loop for each job and schedule.

Note: We only need to calculate scheduled run dates that fall between
MAX(active_start_date, @StartDateTimeToCalculate)
and MIN(active_end_date + 1, @EndDateTimeToCalculate).
========================================================================== */

DECLARE @CurrJobID UNIQUEIDENTIFIER
DECLARE @CurrScheduleName NVARCHAR(128)
DECLARE @CurrNextRunDate DATETIME
DECLARE @CurrActiveStartTime INT
DECLARE @CurrRunsEveryXDays INT -- this variable holds X.
DECLARE @i INT
DECLARE @CurrMinDate DATETIME
DECLARE @CurrMaxDate DATETIME


-- @MinDate and @MaxDate reflect the time window in which
-- we need to calculate the run dates for each job.
-- These dates depend on active_start_date therefore may vary for each job.


-- We loop for each job_id and each schedule_name.
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 4
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrJobID IS NOT NULL
BEGIN
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 4
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrScheduleName IS NOT NULL
BEGIN
SELECT @CurrActiveStartTime = active_start_time,
@CurrRunsEveryXDays = freq_interval,
@CurrNextRunDate = CAST(CAST(next_run_date AS NVARCHAR(8)) AS DATETIME),
@CurrMinDate = min_datetime_to_consider,
@CurrMaxDate = max_datetime_to_consider
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName

-- Record all dates between @CurrMinDate and @CurrMaxDate
-- in which the job is scheduled to run. This is done as follows:
-- Starting with the next_run_date, we add/subtract @CurrRunsEveryXDays until
-- we fill all dates between @CurrMinDate and @CurrMaxDate.

SET @i = 0

-- First, get all dates between next_run_date up to @CurrMaxDate
WHILE DATEADD(dd, @i*@CurrRunsEveryXDays, @CurrNextRunDate) <= @CurrMaxDate
BEGIN
-- Check that we need to record this date
IF DATEADD(dd, @i*@CurrRunsEveryXDays, @CurrNextRunDate) >= @CurrMinDate
BEGIN
-- Check that it has not yet been recorded.
IF NOT EXISTS(SELECT * FROM #JobScheduledDates WHERE job_id = @CurrJobID AND schedule_name = @CurrScheduleName AND RunDate = DATEADD(dd, @i*@CurrRunsEveryXDays, @CurrNextRunDate) )
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(dd, @i*@CurrRunsEveryXDays, @CurrNextRunDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS(SELECT TOP 1 * FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(dd, @i*@CurrRunsEveryXDays, @CurrNextRunDate))
END
END

SET @i = @i + 1
END

SET @i = 0

-- Now, get all the dates between @CurrMinDate and next_run_date
WHILE DATEADD(dd, -@i*@CurrRunsEveryXDays, @CurrNextRunDate) >= @CurrMinDate
BEGIN
-- Check that we need to record this date
IF DATEADD(dd, -@i*@CurrRunsEveryXDays, @CurrNextRunDate) <= @CurrMaxDate
BEGIN
-- Check that it has not yet been recorded.
IF NOT EXISTS(SELECT * FROM #JobScheduledDates WHERE job_id = @CurrJobID AND schedule_name = @CurrScheduleName AND RunDate = DATEADD(dd, @i*@CurrRunsEveryXDays, @CurrNextRunDate) )
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(dd, -@i*@CurrRunsEveryXDays, @CurrNextRunDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS(SELECT TOP 1 * FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(dd, -@i*@CurrRunsEveryXDays, @CurrNextRunDate))
END
END

SET @i = @i + 1
END

-- Move on to the next daily schedule for the same job
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 4
AND schedule_name > @CurrScheduleName
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0
END

SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 4
AND job_id > @CurrJobID
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL AND next_run_date > 0
END




/* ==========================================================================
Step 2.2 - weekly jobs - freq_type = 8 (freq_subday_type = 1, 4, 8)

This case is when a job runs every X weeks in specific days in each week.
Calculating the dates for these jobs is not straightforward: Say that a
job runs every 3 weeks on Sunday and Friday, and that the job was created
on Thursday. Will the next run be on the next Sunday, or since the job
runs on the following Friday- will it not run on the next Sunday?

The following logic is used to get the schedule for weekly jobs:
Check the next run date for the job. That week is when the job is run.
Then, look at every day of that week (we can ignore DATEFIRST since
it doesn't apply here) and conclude all days in that week in which
the job should run (this info is captured by freq_interval).
This allows us to latch on to deterministic dates for the job schedule.
Then, we calculate dates before and after that date in which the job should run.
========================================================================== */

DECLARE @CurrRunEveryXWeeks INT -- This variable captures X.
DECLARE @CurrDayVector INT -- This variable captures the day codes when the job runs
-- (1-Sun, 2-Mon, ..., 7-Sat), as taken from freq_interval.
DECLARE @DayCodeForNextRunDate INT
DECLARE @tmpMinDate DATETIME
DECLARE @tmpMaxDate DATETIME


-- @MinDate and @MaxDate reflect the time window in which
-- we need to calculate the run dates for each job.
-- These dates depend on active_start_date therefore may vary for each job.

-- First, we create a temp table that holds the week days.
-- This will help us to find the scheduled dates for weekly jobs later.
IF OBJECT_ID('tempdb..#DaysAndDates') IS NOT NULL
DROP TABLE #DaysAndDates

CREATE TABLE #DaysAndDates (
DayCode INT PRIMARY KEY CLUSTERED,
DayDesc VARCHAR(16), -- Has no functional use. For readability only.
TempFlag BIT, -- If this value is 1 for a given job, then the job runs in scheduled to run that day.
TempDate DATETIME) -- If this value is non-null for a given job, then the job runs in this scheduled date.

SET @i = 1

WHILE @i <= 7
BEGIN
INSERT INTO #DaysAndDates (DayCode, DayDesc, TempFlag)
VALUES(@i, CASE
WHEN @i = 1 THEN 'Sunday'
WHEN @i = 2 THEN 'Monday'
WHEN @i = 3 THEN 'Tuesday'
WHEN @i = 4 THEN 'Wednesday'
WHEN @i = 5 THEN 'Thursday'
WHEN @i = 6 THEN 'Friday'
WHEN @i = 7 THEN 'Saturday' END,
0)

SET @i = @i + 1
END

-- We loop for each job_id and each schedule_name.
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 8
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrJobID IS NOT NULL
BEGIN
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 8
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrScheduleName IS NOT NULL
BEGIN
SELECT @CurrActiveStartTime = active_start_time,
@CurrDayVector = freq_interval,
@CurrNextRunDate = CAST(CAST(next_run_date AS NVARCHAR(8)) AS DATETIME),
@CurrRunEveryXWeeks = freq_recurrence_factor,
@CurrMinDate = min_datetime_to_consider,
@CurrMaxDate = max_datetime_to_consider
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName

-- Record all dates between @CurrMinDate and @CurrMaxDate
-- in which the job is scheduled to run. This is done as follows:
-- Starting with the next_run_date, we add/subtract @CurrRunsEveryXWeeks
-- for each day in which the job is scheduled to run, until
-- we fill all dates between @CurrMinDate and @CurrMaxDate.

-- Get the days in which the job runs - Reinitialize #DaysAndDates
-- and then set TempFlag = 1 in #DaysAndDates for each run day.
UPDATE #DaysAndDates
SET TempFlag = 0,
TempDate = NULL

-- The POWER function here translates between the day codes (1-7)
-- to the day vector (1, 2, 4, ..., 64) in freq_interval.
UPDATE #DaysAndDates
SET TempFlag = 1
WHERE @CurrDayVector & POWER(2, DayCode - 1) > 0

-- Find the code of the day that corresponds to next_run_date
SET @DayCodeForNextRunDate = 1 + (DATEDIFF (dd, CONVERT(DATETIME, '1899/12/31', 120), @CurrNextRunDate) % 7 )

-- Mark next_run_date as the TempDate for the appropriate day in #DaysAndDates.
-- Here we also update the TempDates for all days in which the job runs.
UPDATE #DaysAndDates
SET TempDate = DATEADD(dd, DayCode - @DayCodeForNextRunDate, @CurrNextRunDate)
WHERE TempFlag = 1

SELECT @tmpMinDate = MIN(TempDate),
@tmpMaxDate = MAX(TempDate)
FROM #DaysAndDates
WHERE TempFlag = 1


-- At this point we are ready to loop and see which dates fall between @CurrMinTime and @CurrMaxTime
SET @i = 0

-- First, get all dates between the initial dates in #DaysAndDates up to @CurrMaxDate
WHILE DATEADD(dd, 7*@i*@CurrRunEveryXWeeks, @tmpMinDate) <= @CurrMaxDate
BEGIN
-- Check that the max date in #JobScheduledDates is greater that the @CurrMinDate.
-- If it is not- none of the values in that table are in the time window
-- so there's nothing to write.
IF DATEADD(dd, 7*@i*@CurrRunEveryXWeeks, @tmpMaxDate) >= @CurrMinDate
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(dd, 7*@i*@CurrRunEveryXWeeks, a.TempDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
FROM #DaysAndDates a
LEFT OUTER JOIN #JobScheduledDates b
ON b.job_id = @CurrJobID
AND b.schedule_name = @CurrScheduleName
AND b.RunDate = DATEADD(dd, 7*@i*@CurrRunEveryXWeeks, a.TempDate)
AND a.TempFlag = 1
WHERE b.job_id IS NULL
AND DATEADD(dd, 7*@i*@CurrRunEveryXWeeks, a.TempDate) <= @CurrMaxDate
AND DATEADD(dd, 7*@i*@CurrRunEveryXWeeks, a.TempDate) >= @CurrMinDate
END

SET @i = @i + 1
END

-- Now, look for all dates between @CurrMinDate and the initial dates in #DaysAndDates.
-- We start with the dates in #DaysAndDates an subtract X weeks at a time.
SET @i = 0

WHILE DATEADD(dd, -7*@i*@CurrRunEveryXWeeks, @tmpMaxDate) >= @CurrMinDate
BEGIN
-- Check that the min date in #JobScheduledDates is greater that the @CurrMaxDate.
-- If it is not- none of the values in that table are in the time window
-- so there's nothing to write.
IF DATEADD(dd, -7*@i*@CurrRunEveryXWeeks, @tmpMinDate) <= @CurrMaxDate
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(dd, -7*@i*@CurrRunEveryXWeeks, a.TempDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
FROM #DaysAndDates a
LEFT OUTER JOIN #JobScheduledDates b
ON b.job_id = @CurrJobID
AND b.schedule_name = @CurrScheduleName
AND b.RunDate = DATEADD(dd, -7*@i*@CurrRunEveryXWeeks, a.TempDate)
AND a.TempFlag = 1
WHERE b.job_id IS NULL
AND DATEADD(dd, -7*@i*@CurrRunEveryXWeeks, a.TempDate) <= @CurrMaxDate
AND DATEADD(dd, -7*@i*@CurrRunEveryXWeeks, a.TempDate) >= @CurrMinDate
END

SET @i = @i + 1
END

-- Move on to the next weekly schedule for the same job
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name > @CurrScheduleName
AND freq_type = 8
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0
END

-- Get the next job with a weekly schedule
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 8
AND job_id > @CurrJobID
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0
END


/* ==========================================================================
Step 2.3 - monthly (fixed) jobs - freq_type = 16 (freq_subday_type = 1, 4, 8)

This case is when a job runs every X months, at the Rth day of the month.
The Rth day can be the 29th, 30th, or 31st of the month, which may not exist
for some months. If the Rth day of the month indeed does not exist, then
that month is skipped.

The following logic is used to get the schedule for monthly (fixed) jobs:
Check the next run date for the job. That month is one when the job is run.
Then, add or subtract X month to find all dates in the requested time window
where the job should be run. For each month found - check whether the Rth date
of the month exist. If not- skip another X months to the next schedule.
(Checking whether the Rth day of the month exists is only done when R > 28).
========================================================================== */

DECLARE @CurrRunEveryXMonths INT -- This variable captures X.
DECLARE @CurrDayOfMonthToRun INT -- This variable captures the day codes when the job runs
-- (1-Sun, 2-Mon, ..., 7-Sat), as taken from freq_interval.


-- @MinDate and @MaxDate reflect the time window in which
-- we need to calculate the run dates for each job.
-- These dates depend on active_start_date therefore may vary for each job.

-- We loop for each job_id and each schedule_name.
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 16
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrJobID IS NOT NULL
BEGIN
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 16
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrScheduleName IS NOT NULL
BEGIN
SELECT @CurrActiveStartTime = active_start_time,
@CurrDayOfMonthToRun = freq_interval,
@CurrNextRunDate = CAST(CAST(next_run_date AS NVARCHAR(8)) AS DATETIME),
@CurrRunEveryXMonths = freq_recurrence_factor,
@CurrMinDate = min_datetime_to_consider,
@CurrMaxDate = max_datetime_to_consider
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName

-- Record all dates between @CurrMinDate and @CurrMaxDate
-- in which the job is scheduled to run. This is done as follows:
-- Starting with the next_run_date, we add/subtract @CurrRunsEveryXMonths
-- for each day in which the job is scheduled to run, until
-- we fill all dates between @CurrMinDate and @CurrMaxDate.

-- At this point we are ready to loop and see which dates fall between @CurrMinTime and @CurrMaxTime.
-- While looping, we need to make sure that the day at which the job runs
-- indeed exists for the month (e.g., when the day is the 29th, 30th, or 31st,
-- which may not exist for some months).
SET @i = 0

-- We start by looping forward, from @CurrNextRunDate to @CurrMaxDate
WHILE DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate) <= @CurrMaxDate
BEGIN
-- Check that we need to record the calculated date.
IF DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate) >= @CurrMinDate
BEGIN
-- Validate that the day to run exists for the month.
IF (@CurrDayOfMonthToRun > 28 AND DATEPART(day, DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate)) = @CurrDayOfMonthToRun)
OR @CurrDayOfMonthToRun <= 28
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS(SELECT TOP 1 * FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate))
END
END

SET @i = @i + 1
END

-- Now, loop backwards from @CurrNextRunDate to @CurrMinDate.
SET @i = 0

WHILE DATEADD(mm, -@i*@CurrRunEveryXMonths, @CurrNextRunDate) >= @CurrMinDate
BEGIN
-- Check that we need to record the calculated date.
IF DATEADD(mm, -@i*@CurrRunEveryXMonths, @CurrNextRunDate) <= @CurrMaxDate
BEGIN
-- Validate that the day to run exists for the month.
IF (@CurrDayOfMonthToRun > 28 AND DATEPART(day, DATEADD(mm, -@i*@CurrRunEveryXMonths, @CurrNextRunDate)) = @CurrDayOfMonthToRun)
OR @CurrDayOfMonthToRun <= 28
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(mm, -@i*@CurrRunEveryXMonths, @CurrNextRunDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS(SELECT TOP 1 * FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(mm, -@i*@CurrRunEveryXMonths, @CurrNextRunDate))
END
END

SET @i = @i + 1
END

-- Move on to the next monthly schedule for the same job
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name > @CurrScheduleName
AND freq_type = 16
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0
END

-- Get the next job with a monthly (fixed) schedule
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 16
AND job_id > @CurrJobID
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0
END


/* ==========================================================================
Step 2.4 - monthly (relative) jobs - freq_type = 32 (freq_subday_type = 1, 4, 8)

This case is divided into two options:
1. A job runs every X months, at the 1st, 2nd, 3rd, 4th or last day
(e.g., Sun, Mon, etc.) day of the month. Here, the day is fixed and
is associated with a day code (1-Sun, 2-Mon, ..., 7-Sat).
2. A job runs every X months, at the 1st, 2nd, 3rd, 4th or last
day of the month, weekday, or weekend day. This is a little tricky
since the last two (weekday or weekend day) depend on the datefirst
settings on the server.

The logic here is as before- we find the next_run_date, then calculate
the previous and next run dates that fall in the observed time window,
and record all dates in #JobScheduledDates.
========================================================================== */

DECLARE @CurrCalcRunDate DATETIME
DECLARE @CurrMonth INT
DECLARE @CurrYear INT
DECLARE @CurrDayCode INT
DECLARE @CurrDayCodeFor1stOfMonth INT
DECLARE @CurrDateOfFirstYthDayOfMonth DATETIME
DECLARE @CurrFirstOfMonth DATETIME
DECLARE @CurrLastOfMonth DATETIME
DECLARE @CurrScheduledDate DATETIME
DECLARE @j INT
DECLARE @k INT
DECLARE @tmpDate DATETIME



-- Type 1: Algorithm to get the next/prev scheduled run date.
-- @CurrDayToRun:
-- 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday
-- 8 = Day, 9 = Weekday, 10 = Weekend day
DECLARE @CurrDayToRun INT
-- @CurrRelativeInterval:
-- 1 = First, 2 = Second, 4 = Third, 8 = Fourth, 16 = Last
DECLARE @CurrRelativeInterval INT


-- Examples:
-- If @CurrDayToRun = 1 AND @CurrRelativeInterval = 4
-- the job runs on the third Sunday every X months.
-- If @CurrDayToRun = 9 AND @CurrRelativeInterval = 16
-- the job runs on the last weekday of the month, every X months.


-- We loop for each job_id and each schedule_name.
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 32
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrJobID IS NOT NULL
BEGIN
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 32
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrScheduleName IS NOT NULL
BEGIN
SELECT @CurrActiveStartTime = active_start_time,
@CurrDayToRun = freq_interval,
@CurrNextRunDate = CAST(CAST(next_run_date AS NVARCHAR(8)) AS DATETIME),
@CurrRunEveryXMonths = freq_recurrence_factor,
@CurrRelativeInterval = freq_relative_interval,
@CurrMinDate = min_datetime_to_consider,
@CurrMaxDate = max_datetime_to_consider
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName


-- Record all dates between @CurrMinDate and @CurrMaxDate
-- in which the job is scheduled to run. This is done as follows:
-- Starting with the next_run_date, we add/subtract @CurrRunsEveryXMonths
-- for each day in which the job is scheduled to run, until
-- we fill all dates between @CurrMinDate and @CurrMaxDate.

-- The key challenge here is to find the dates in which the job is scheduled
-- to run. We do this on a case by case basis (according to the @CurrDayToRun
-- and @CurrRelativeInterval. Only after finding the appropriate schedule we check
-- if the dates found fall in the time window.

-- First, we subtract from @CurrNextRunDate to move our starting point
-- to be earlier than @CurrMinDate.

SET @i = 0

WHILE DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate) >= CAST(CAST(DATEPART(month, @CurrMinDate) AS VARCHAR(2)) + '/1/' + CAST(DATEPART(year, @CurrMinDate) AS VARCHAR(4)) AS DATETIME)
SET @i = @i - 1

-- Componesate for the last redundant subtraction.
IF @i < 0
SET @i = @i + 1


-- At this point we are ready to loop and see which dates fall between @CurrMinTime and @CurrMaxTime.
-- The while checks that we have not passed the last day in the month of @CurrMaxDate.
WHILE DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate) <= DATEADD(dd, -1, DATEADD(mm, 1, CAST(CAST(DATEPART(month, @CurrMaxDate) AS VARCHAR(2)) + '/1/' + CAST(DATEPART(year, @CurrMaxDate) AS VARCHAR(4)) AS DATETIME)))
BEGIN
-- From here on, we have an algorithm to determine the scheduled dates
-- for the job in the examined month.

-- Get the next/prev month.
SET @CurrMonth = DATEPART(month, DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate))
SET @CurrYear = DATEPART(year, DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate))

-- Get the date for the first of the month.
SET @CurrFirstOfMonth = CAST(CAST(@CurrMonth AS VARCHAR(2)) + '/1/' + CAST(@CurrYear AS VARCHAR(4)) AS DATETIME)

-- Get the date for the last day of the month.
SET @CurrLastOfMonth = DATEADD(dd, -1, DATEADD(mm, 1, CAST(CAST(@CurrMonth AS VARCHAR(2)) + '/1/' + CAST(@CurrYear AS VARCHAR(4)) AS DATETIME)))



IF @CurrDayToRun BETWEEN 1 AND 7
BEGIN
-- Get the day code for the first day of the month.
SET @CurrDayCodeFor1stOfMonth = CASE DATENAME(dw, @CurrFirstOfMonth) WHEN 'Sunday' THEN 1 WHEN 'Monday' THEN 2 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 4 WHEN 'Thursday' THEN 5 WHEN 'Friday' THEN 6 WHEN 'Saturday' THEN 7 END
-- Get the date of the first encounter of the day in @CurrDayToRun for the month.
SET @CurrDateOfFirstYthDayOfMonth = DATEADD(dd, (@CurrDayToRun + 7 - @CurrDayCodeFor1stOfMonth) % 7, @CurrFirstOfMonth)

-- Get the date in which the job is scheduled to run.
SET @CurrScheduledDate = CASE @CurrRelativeInterval
WHEN 1 THEN @CurrDateOfFirstYthDayOfMonth
WHEN 2 THEN DATEADD(dd, 7, @CurrDateOfFirstYthDayOfMonth)
WHEN 4 THEN DATEADD(dd, 2*7, @CurrDateOfFirstYthDayOfMonth)
WHEN 8 THEN DATEADD(dd, 3*7, @CurrDateOfFirstYthDayOfMonth)
-- Last day Y of the month.
-- There are no more than 5 encounters of day Y per month.
-- The last day Y can therefore be DATEADD(dd, 3*7, @CurrDateOfFirstYthDayOfMonth)
-- or DATEADD(dd, 4*7, @CurrDateOfFirstYthDayOfMonth).
WHEN 16 THEN
CASE WHEN DATEPART(month, DATEADD(dd, 4*7, @CurrDateOfFirstYthDayOfMonth)) <> @CurrMonth
THEN DATEADD(dd, 3*7, @CurrDateOfFirstYthDayOfMonth)
ELSE DATEADD(dd, 4*7, @CurrDateOfFirstYthDayOfMonth)
END
END
END
ELSE
BEGIN
-- This is the case where @CurrDayToRun is between 8 and 10.

IF @CurrDayToRun = 8 -- Job runs on the 1st - 4th or last day of month.
BEGIN
SET @CurrScheduledDate = CASE @CurrRelativeInterval
WHEN 1 THEN @CurrFirstOfMonth
WHEN 2 THEN DATEADD(dd, 1, @CurrFirstOfMonth)
WHEN 4 THEN DATEADD(dd, 2, @CurrFirstOfMonth)
WHEN 8 THEN DATEADD(dd, 3, @CurrFirstOfMonth)
WHEN 16 THEN @CurrLastOfMonth
END
END

IF @CurrDayToRun IN (9, 10) -- Job runs on the 1st - 4th or last weekday or weekend day of the month.
BEGIN
IF @CurrRelativeInterval IN (1, 2, 4, 8)
BEGIN
SET @tmpDate = @CurrFirstOfMonth

SET @k = 0

-- @j holds the Nth encounter of a business or weekend day.
SET @j = CASE @CurrRelativeInterval
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 4 THEN 3
WHEN 8 THEN 4
END

SET @CurrScheduledDate = NULL

WHILE @CurrScheduledDate IS NULL
BEGIN
-- Here we check if we need to track week or weekend days
-- and count when they are encountered.
IF ( @CurrDayToRun = 9 AND ( (((@@DATEFIRST - 1) + (DATEPART(weekday, @tmpDate) - 1)) % 7) NOT IN (5, 6)) )
OR ( @CurrDayToRun = 10 AND ( (((@@DATEFIRST - 1) + (DATEPART(weekday, @tmpDate) - 1)) % 7) IN (5, 6)) )
-- Track the fact that a day of the desired type is encountered.
SET @k = @k + 1

-- Loop to the next date unless the @tmpDate is already the Nth encounter.
IF @k < @j
SET @tmpDate = DATEADD(dd, 1, @tmpDate)
ELSE
SET @CurrScheduledDate = @tmpDate
END
END
ELSE
BEGIN
-- Here we are looking for the last business or weekend day for the month.
SET @tmpDate = @CurrLastOfMonth

SET @CurrScheduledDate = NULL

WHILE @CurrScheduledDate IS NULL
BEGIN
-- Check if the job runs on the last business day and the last date of the month
-- is indeed a business day, or if the job runs on the last weekend day
-- and the last day is indeed a weekend day
IF (@CurrDayToRun = 9 AND (((@@DATEFIRST - 1) + (DATEPART(weekday, @tmpDate) - 1)) % 7) NOT IN (5, 6) )
OR (@CurrDayToRun = 10 AND (((@@DATEFIRST - 1) + (DATEPART(weekday, @tmpDate) - 1)) % 7) IN (5, 6) )

SET @CurrScheduledDate = @tmpDate
ELSE
SET @tmpDate = DATEADD(dd, -1, @tmpDate)
END
END
END
END


-- Here we have the @CurrScheduledDate - date of the month when the job
-- is scheduled to run. Check that we need to record the calculated date before we do so.
IF @CurrScheduledDate BETWEEN @CurrMinDate AND @CurrMaxDate
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
@CurrScheduledDate,
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS(SELECT TOP 1 * FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = @CurrScheduledDate )
END

SET @i = @i + 1

END

SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name > @CurrScheduleName
AND freq_type = 32
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0

END

SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 32
AND job_id > @CurrJobID
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL AND next_run_date > 0
END


/* ========================================================================
Step 3 - Find the times at which the job is scheduled to run,
for all dates found in Step 2 above.

In Step 2 we found all the dates in which the job was scheduled to run.
For all those dates, we now find the scheduled times.
====================================================== */

-- In #JobScheduledDates we already recorded the first time of the day
-- in which jobs are run (column StartTimeEx). First, we update
-- that table to get the number of seconds from the start of the day
-- for the first run (column StartTimeInSecFromStartOfDay).

UPDATE #JobScheduledDates
SET StartTimeInSecFromStartOfDay = CAST(LEFT(StartTimeEx, 2) AS INT)*3600
+ CAST(SUBSTRING(StartTimeEx, 3, 2) AS INT)*60
+ CAST(RIGHT(StartTimeEx, 2) AS INT)


-- Run times have to be constructed for all jobs that run
-- daily, weekly, or monthly. Also, if a job is scheduled
-- run once per day instead of every X minutes or hours,
-- then we already have the run time in #JobScheduledDates.

-- In order to distinguish between the run-dates and run dates & times,
-- we create a new temp table that holds the date + time info.

IF OBJECT_ID('tempdb..#JobScheduledDatesAndTimes') IS NOT NULL
DROP TABLE #JobScheduledDatesAndTimes

CREATE TABLE #JobScheduledDatesAndTimes (
job_id VARCHAR(64),
schedule_name NVARCHAR(128),
RunDateTime DATETIME,
ConflictingRunDateTime DATETIME)

EXEC('CREATE UNIQUE CLUSTERED INDEX CI_#JobScheduledDatesAndTimes_' + @sSPID + '
ON #JobScheduledDatesAndTimes (job_id, schedule_name, RunDateTime) ')

-- The scheduled times are determined as follows (for daily, weekly, and monthly jobs):
-- freq_subday_type:
-- 1 - run once (not interesting. we already have the run-time for this case).
-- 4 - run every X minutes. X is stored in freq_subday_interval.
-- 8 - run every Y hours. Y is stored in freq_subday_interval.

-- It is now left to calculate the run times.

-- We start by populating #JobScheduledDatesAndTimes with all jobs
-- that run once.
INSERT INTO #JobScheduledDatesAndTimes (job_id, schedule_name, RunDateTime)
SELECT b.job_id,
b.schedule_name,
DATEADD(ss, b.StartTimeInSecFromStartOfDay, b.RunDate)
FROM #JobScheduleInfo a
INNER JOIN #JobScheduledDates b
ON a.job_id = b.job_id
AND a.schedule_name = b.schedule_name
WHERE a.freq_subday_type NOT IN (4, 8)
AND DATEADD(ss, b.StartTimeInSecFromStartOfDay, b.RunDate) >= a.min_datetime_to_consider
AND DATEADD(ss, b.StartTimeInSecFromStartOfDay, b.RunDate) <= a.max_datetime_to_consider

-- Next, we handle all jobs that are scheduled to run every X minutes in a day.
-- Here, we simply add @i x X minutes for all schedules at once until
-- we cover the entire day.

DECLARE @Cnt INT

SET @i = 0
SET @Cnt = 1

WHILE @Cnt > 0
BEGIN
INSERT INTO #JobScheduledDatesAndTimes (job_id, schedule_name, RunDateTime)
SELECT b.job_id, b.schedule_name, DATEADD(ss, @i*60*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate)
FROM #JobScheduleInfo a
INNER JOIN #JobScheduledDates b
ON a.job_id = b.job_id
AND a.schedule_name = b.schedule_name
WHERE a.freq_subday_type = 4
-- We need to make sure that we don't cross over to the next day.
AND DATEPART(day, DATEADD(ss, @i*60*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate)) = DATEPART(day, b.RunDate)
-- Also consider the min/max_datetime_to_consider
AND DATEADD(ss, @i*60*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) >= a.min_datetime_to_consider
AND DATEADD(ss, @i*60*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) <= a.max_datetime_to_consider

SET @Cnt = @@ROWCOUNT
SET @i = @i + 1
END

-- Finally, we do the same as above for all jobs that run every Y hours.

SET @i = 0
SET @Cnt = 1

WHILE @Cnt > 0
BEGIN
INSERT INTO #JobScheduledDatesAndTimes (job_id, schedule_name, RunDateTime)
SELECT b.job_id, b.schedule_name, DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate)
FROM #JobScheduleInfo a
INNER JOIN #JobScheduledDates b
ON a.job_id = b.job_id
AND a.schedule_name = b.schedule_name
WHERE a.freq_subday_type = 8
-- We need to make sure that we don't cross over to the next day.
AND DATEPART(day, DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate)) = DATEPART(day, b.RunDate)
-- Also consider the min/max_datetime_to_consider
AND DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) >= a.min_datetime_to_consider
AND DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) <= a.max_datetime_to_consider

SET @Cnt = @@ROWCOUNT
SET @i = @i + 1
END



/* ========================================================================
Step 3 - Find conflicting job schedules in the requested time window.

Here, we compare the run date-time values found for each schedule
in #JobScheduledDatesAndTimes to see if there is a time gap
lower than @TimeGapInSeconds between job schedules.
====================================================== */

IF OBJECT_ID('tempdb..#ConflictingSchedules') IS NOT NULL
DROP TABLE #ConflictingSchedules

SELECT a.job_id AS JobID,
(SELECT TOP 1 job_name FROM #JobScheduleInfo WHERE job_id = a.job_id) AS JobName,
a.schedule_name AS ScheduleName,
a.RunDateTime AS ScheduledDateTime,
b.schedule_name AS ConflictingScheduleTimeScheduleName,
b.RunDateTime AS ConflictingScheduleDateTime
INTO #ConflictingSchedules
FROM #JobScheduledDatesAndTimes a
INNER JOIN #JobScheduledDatesAndTimes b
ON a.job_id = b.job_id
AND a.schedule_name <> b.schedule_name
AND a.RunDateTime >= DATEADD(ss, -@TimeGapInSeconds, b.RunDateTime)
AND a.RunDateTime <= b.RunDateTime


-- Remove duplicates from #ConflictingSchedules
-- If Schedule1 and Schedule2 conflict, then #ConflictingSchedules
-- will report the Schedule1 conflicts with 2 and that 2
-- conflicts with 1. The DELETE statement below removes
-- these duplicates.
DELETE a
FROM #ConflictingSchedules a
INNER JOIN #ConflictingSchedules b
ON a.ScheduleName = b.ConflictingScheduleTimeScheduleName
AND a.ScheduledDateTime = b.ConflictingScheduleDateTime
AND a.ConflictingScheduleTimeScheduleName = b.ScheduleName
AND a.ConflictingScheduleDateTime = b.ScheduledDateTime
WHERE a.ScheduleName > a.ConflictingScheduleTimeScheduleName



SELECT *
FROM #ConflictingSchedules
ORDER BY ScheduledDateTime



-- Cleanup - drop temp tables used in the script.
IF OBJECT_ID('tempdb..#JobScheduleInfo') IS NOT NULL
DROP TABLE #JobScheduleInfo

IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
DROP TABLE #xp_results

IF OBJECT_ID('tempdb..#JobScheduledDates') IS NOT NULL
DROP TABLE #JobScheduledDates

IF OBJECT_ID('tempdb..#DaysAndDates') IS NOT NULL
DROP TABLE #DaysAndDates

IF OBJECT_ID('tempdb..#JobScheduledDatesAndTimes') IS NOT NULL
DROP TABLE #JobScheduledDatesAndTimes

IF OBJECT_ID('tempdb..#ConflictingSchedules') IS NOT NULL
DROP TABLE #ConflictingSchedules