if object_id('dbo.uspCheckJobFutureSchedule', 'P') is not null

drop proc dbo.uspCheckJobFutureSchedule;

go

create proc dbo.uspCheckJobFutureSchedule

@Start_DT datetime

, @End_DT datetime

, @JobName varchar(128)=''

, @ScheduleName varchar(128)=''

as

begin --proc

if object_id('tempdb.dbo.#tmp', 'U') is not null

drop table #tmp;

if object_id('tempdb.dbo.#job', 'U') is not null

drop table dbo.#job;

set nocount on;

if object_id('tempdb..#tblCalendar', 'U') is not null

drop table #tblCalendar;

create table #tblCalendar (dt smalldatetime, [year] smallint, [month] tinyint, [day] tinyint, [weekday] tinyint, [week_of_month] tinyint)

declare @tbl table (dt smalldatetime, [year] smallint, [month] tinyint, [day] tinyint, [weekday] tinyint, [week_of_month] tinyint);

--declare @start_dt datetime, @end_dt datetime;

declare @diff_days int, @calc_start_dt datetime, @i int, @inner_loop_dt datetime;

declare @current_dt datetime, @current_week datetime;

declare @dw int;

declare @calc_end_dt datetime;

declare @min_end_date datetime, @max_start_date datetime, @dt datetime;

declare @next_run_date int, @next_run_time int, @next_run_dt datetime;

--select @start_dt = '2014-02-21', @end_dt = '2014-04-28';

-- first get all jobs that are scheduled, with job name and the schedule details

create table #tmp (

schedule_id int

, jobname sysname

,schedule_name sysname

,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 char(8)

,active_end_date char(8)

,active_start_time char(8)

,active_end_time char(8)

,active_start_date_int int

,active_end_date_int int

,active_start_time_int int

,active_end_time_int int

,next_run_date int

,next_run_time int

)

insert into #tmp (schedule_id, jobname, schedule_name, freq_type

,freq_interval

,freq_subday_type

,freq_subday_interval

,freq_relative_interval

,freq_recurrence_factor

,active_start_date

,active_end_date

,active_start_time

,active_end_time

,active_start_date_int

,active_end_date_int

,active_start_time_int

,active_end_time_int

,next_run_date

,next_run_time

)

select s.schedule_id, j.name, s.name, s.freq_type

,s.freq_interval

,s.freq_subday_type

,s.freq_subday_interval

,s.freq_relative_interval

,s.freq_recurrence_factor

,cast(s.active_start_date as char(8))

,cast(s.active_end_date as char(8))

,substring(right('00000' + cast(s.active_start_time as varchar(6)), 6),1,2)+':'+ substring(right('00000' + cast(s.active_start_time as varchar(6)), 6),3,2)+':'+substring(right('00000' + cast(s.active_start_time as varchar(6)), 6),5,2)

,substring(right('00000' + cast(s.active_end_time as varchar(6)), 6),1,2)+':'+ substring(right('00000' + cast(s.active_end_time as varchar(6)), 6),3,2)+':'+substring(right('00000' + cast(s.active_end_time as varchar(6)), 6),5,2)

, s.active_start_date

, s.active_end_date

, s.active_start_time

, s.active_end_time

, js.next_run_date

, js.next_run_time

from msdb.dbo.sysschedules s

inner join msdb.dbo.sysjobschedules js

on js.schedule_id = s.schedule_id

inner join msdb.dbo.sysjobs j

on j.job_id = js.job_id

where s.enabled=1

and j.enabled =1

and js.next_run_date > 0

and s.name = case @ScheduleName when '' then s.name else @ScheduleName end

and j.name= case @JobName when '' then j.name else @JobName end

-- delete all job schedules that are beyond the boundary of the @start_dt and @end_dt

-- for example, if a job is scheduled to be run in Jan, 2020, and if @start_dt='2018-01-01' and @end_dt = '2018-02-01',

-- then we do not need to consider this job schedule (i.e. Jan 2020)

delete from #tmp

where convert(datetime, active_start_date +' '+ active_start_time ) > @end_dt

or convert(datetime, active_end_date +' '+ active_end_time ) < @start_dt;

-- create a temp table to hold those jobs that will be started between @start_dt and @end_dt window

create table dbo.#job (

ServerName sysname default @@servername

, jobname sysname

, ScheduleName sysname

, run_datetime datetime

);

declare @schedule_id int, @schedule_name sysname, @active_start_dt datetime, @active_end_dt datetime;

declare @freq_type int, @freq_interval int

, @freq_subday_type int

, @freq_subday_interval int

, @freq_relative_interval int

, @freq_recurrence_factor int

, @active_start_time_int int

, @active_end_time_int int;

declare curSch cursor for

select distinct schedule_id, schedule_name, active_start_dt=convert(datetime, active_start_date +' '+ active_start_time )

, active_end_dt=convert(datetime, active_end_date +' '+ active_end_time )

, freq_type, freq_interval

, freq_subday_type

, freq_subday_interval

, freq_relative_interval

, freq_recurrence_factor

, active_start_time_int

, active_end_time_int

, next_run_date

, next_run_time

from #tmp;

open curSch;

fetch next from curSch into @schedule_id, @schedule_name, @active_start_dt, @active_end_dt

, @freq_type, @freq_interval

, @freq_subday_type

, @freq_subday_interval

, @freq_relative_interval

, @freq_recurrence_factor

, @active_start_time_int

, @active_end_time_int

, @next_run_date

, @next_run_time;

while @@fetch_status = 0

begin -- loop

if @freq_type = 1 -- one time only

begin -- one time only

if @active_start_dt between @start_dt and @end_dt

insert into #job (jobname, ScheduleName, run_datetime)

select jobname, @schedule_name, convert(datetime, active_start_date +' '+ active_start_time)

from #tmp

where schedule_id = @schedule_id

end -- one time only

else

begin -- else not one time only

select @next_run_dt = convert(datetime, cast(@next_run_date as varchar(10)) +' ' + substring(right('00000' + cast(@next_run_time as varchar(6)), 6),1,2)+':'+ substring(right('00000' + cast(@next_run_time as varchar(6)), 6),3,2)+':'+substring(right('00000' + cast(@next_run_time as varchar(6)), 6),5,2))

select @max_start_date = case when @next_run_dt >= @start_dt then @next_run_dt else @start_dt end,

@min_end_date = case when @active_end_dt >= @end_dt then @end_dt else @active_end_dt end;

select @calc_start_dt = dateadd(month, datediff(month, 0, @max_start_date), 0); -- switch to the month's first day for @max_start_date

select @calc_end_dt = dateadd(month, datediff(month, 0, @min_end_date)+1, 0)-1; -- witch to the month's last day for @min_end_date

-- generate a calendar table btw @calc_start_dt and @calc_end_dt

set @inner_loop_dt = @calc_start_dt

truncate table #tblCalendar;

begin tran

while @inner_loop_dt <= @calc_end_dt

begin

insert into #tblCalendar (dt, [year], [month], [day], [weekday], [week_of_month])

select @inner_loop_dt, year(@inner_loop_dt), month(@inner_loop_dt), day(@inner_loop_dt), datepart(dw, @inner_loop_dt + @@datefirst -1)

, datepart(week, @inner_loop_dt) - datepart(week, dateadd(month, datediff(month, 0, @inner_loop_dt), 0)) + 1 ;

set @inner_loop_dt = @inner_loop_dt + 1;

end

commit tran

delete from @tbl;

if @freq_type = 4 -- daily

begin -- daily

insert into @tbl (dt)

select dt from #tblCalendar

where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0

and datediff(day, @active_start_dt, dt)%@freq_interval = 0;

end -- daily

if @freq_type = 8 -- weekly

begin -- weekly

insert into @tbl (dt)

select dt from #tblCalendar

where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0

and datediff(week, @next_run_dt, dt)%@freq_recurrence_factor = 0

and power(2, datepart(dw, dt+@@datefirst-1)%7) & @freq_interval >=1;

end -- weekly

if @freq_type = 16

begin -- monthly, @freq_type = 16

insert into @tbl (dt)

select dt from #tblCalendar

where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0

and datediff(month, @active_start_dt, dt)%@freq_recurrence_factor = 0

and [day]=@freq_interval;

end -- monthly, @freq_type = 16

if @freq_type = 32

begin -- monthly, @freq_type = 32

-- find the days that the scheduled jobs will be run (later, we will loop through time to figure out the exact date/time)

if @freq_relative_interval in (1, 2, 4, 8)

begin

if @freq_interval < 8

begin

; with c as (

select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by dt asc)

from #tblCalendar

where dt between @calc_start_dt and @calc_end_dt

and [weekday] = case @freq_interval when 1 then 7 else @freq_interval -1 end

)

insert into @tbl (dt)

select dt from c

where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0

and rn = log(@freq_relative_interval)/log(2)+1

and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

end

if @freq_interval = 8

insert into @tbl

select * from #tblCalendar

where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0

and [day] = @freq_relative_interval

and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

if @freq_interval=9 -- weekday

insert into @tbl

select * from #tblCalendar

where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0

and week_of_month = log(@freq_relative_interval)/log(2)+1

and [weekday] <6

and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

if @freq_interval=10 -- weekend

insert into @tbl

select * from #tblCalendar

where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0

and week_of_month = log(@freq_relative_interval)/log(2)+1

and [weekday] in (6, 7)

and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

end

else -- @freq_relative_interval = 16 -- last

begin -- @freq_relative_interval = 16

if @freq_interval < 8

begin

; with c as (

select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by week_of_month desc)

from #tblCalendar

where dt between @calc_start_dt and @calc_end_dt

)

insert into @tbl

select dt, [year], [month], [weekday], [day], [week_of_month] from c

where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0

and week_of_month = log(@freq_relative_interval)/log(2)+1

and [weekday] = case @freq_interval when 1 then 7 else @freq_interval -1 end

and rn=1

and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

end

if @freq_interval = 8

begin

; with c as (

select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by [day] desc)

from #tblCalendar

where dt between @calc_start_dt and @calc_end_dt

)

insert into @tbl

select dt, [year], [month], [weekday], [day], [week_of_month] from c

where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0

and rn =1

and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

end

if @freq_interval=9 -- weekday

begin

; with c as (

select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by week_of_month desc)

from #tblCalendar

where dt between @calc_start_dt and @calc_end_dt

)

insert into @tbl

select dt, [year], [month], [weekday], [day], [week_of_month] from c

where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0

and [weekday] <6 and rn=1

and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

end

if @freq_interval=10 -- weekend

begin

; with c as (

select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by week_of_month desc)

from #tblCalendar

where dt between @calc_start_dt and @calc_end_dt

)

insert into @tbl

select dt, [year], [month], [weekday], [day], [week_of_month] from c

where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0

and [weekday] in (6, 7) and rn=1

and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

end

end -- @freq_relative_interval = 16

end -- monthly, @freq_type = 32

if exists (select * from @tbl) -- we do have days that match the schedules, now we need to loop through time to check whether the job will be run

begin

declare curT_daily cursor for

select dt from @tbl;

open curT_daily;

fetch next from curT_daily into @dt;

while @@fetch_status =0

begin -- while loop

set @inner_loop_dt = dateadd(second, @active_start_time_int/10000*3600+@active_start_time_int%10000/100*60+@active_start_time_int%100, @dt);

while @inner_loop_dt <= dateadd(second, @active_end_time_int/10000*3600+@active_end_time_int%10000/100*60+@active_end_time_int%100, @dt)

begin

if @inner_loop_dt between @max_start_date and @min_end_date

insert into #job (jobname, ScheduleName, run_datetime)

select jobname, @Schedule_Name, @inner_loop_dt

from #tmp

where schedule_id = @schedule_id

if @freq_subday_type = 1

begin

set @inner_loop_dt = @inner_loop_dt + 1;

continue;

end

if @freq_subday_type = 2

set @inner_loop_dt = dateadd(second, @freq_subday_interval, @inner_loop_dt);

else if @freq_subday_type = 4

set @inner_loop_dt = dateadd(MINUTE, @freq_subday_interval, @inner_loop_dt);

else

set @inner_loop_dt = dateadd(HOUR, @freq_subday_interval, @inner_loop_dt);

end

fetch next from curT_daily into @dt;

end -- while loop

close curT_daily;

deallocate curT_daily;

end

end -- -- else not one time only

fetch next from curSch into @schedule_id, @schedule_name, @active_start_dt, @active_end_dt

, @freq_type, @freq_interval

, @freq_subday_type

, @freq_subday_interval

, @freq_relative_interval

, @freq_recurrence_factor

, @active_start_time_int

, @active_end_time_int

, @next_run_date

, @next_run_time;

end -- loop

close curSch;

deallocate curSch;

select ServerName, JobName, ScheduleName, Run_DateTime from #job

end -- proc

go