function to get time for next job

use msdb
go

/*
select dbo.fn_next_job_time(15, null, null)
select dbo.fn_next_job_time(7, null, null)
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -','Weekend Sat')
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -',null)
select dbo.fn_next_job_time(4, 'CES Weekly Maintenance -',null)
*/
CREATE FUNCTION fn_next_job_time
(
    @interval int = 10,
    @jobname nvarchar(100) = null ,
    @schedulename nvarchar(100) = null
)
RETURNS int
AS
BEGIN

    set @jobname = @jobname + '%'
    set @schedulename = @schedulename + '%'

    declare @curr_hr int, @curr_min int, @curr_sec int, @next_hr int, @next_min int, @next_sec int
    declare @nexttime int
    --set @nexttime = 10004
    --set @nexttime = 206
    --set @nexttime = 5506
    --set @nexttime = 230847
    --set @nexttime = 145712
    --set @nexttime = 41942
    --set @nexttime = 225847
    --set @nexttime = 235847


    select top 1 @nexttime = c.active_start_time
    --a.schedule_id, b.name, c.active_start_time,c.name, *
    from sysjobschedules a
    inner join sysjobs b on a.job_id = b.job_id
    inner join sysschedules c on a.schedule_id = c.schedule_id
    where
    (b.name like @jobname or @jobname is null)
    and
    ( c.name like @schedulename or @schedulename is null)
    order by c.active_start_time desc
   
    select @curr_hr = right(convert(nvarchar(10), @nexttime/10000),2) , @curr_min = right(convert(nvarchar(10),  @nexttime/100),2),  @curr_sec = right(convert(nvarchar(10), @nexttime),2)
    set @next_sec = @curr_sec
    set @next_min = (@curr_min + @interval)%60
    set @next_hr =  @curr_hr + (@curr_min + @interval)/ 60
    set @next_hr = case when @next_hr >= 24 then 0 else @next_hr  end
    --select @nexttime, @curr_hr , @curr_min , @curr_sec
    --select @nexttime, @next_hr , @next_min , @next_sec
   
    select @nexttime = convert(int,
    (case when @next_hr  < 10 then '0'+CONVERT(nvarchar(10),@next_hr ) else convert(nvarchar(10),@next_hr ) end) +
    (case when @next_min  < 10 then '0'+CONVERT(nvarchar(10),@next_min ) else convert(nvarchar(10),@next_min ) end) +
    (case when @next_sec < 10 then '0'+CONVERT(nvarchar(10),@next_sec) else convert(nvarchar(10),@next_sec) end)
    )


   
    RETURN @nexttime

END
GO