dbo.QBM_FGIScheduleNextRun

1        create   function dbo.QBM_FGIScheduleNextRun (@oldnextrun datetime, @lastrun datetime,  @JustNow datetime,  @StartTime varchar(256),  @FrequencyType
2 nvarchar(16)  , @Frequency int , @FrequencySubType nvarchar(256) , @CurrentUTCOffset int  ) returns datetime as begin  declare @nextrun datetime  declare
3 @starttimes_intern QBM_YParameterlist declare @lastrun_intern datetime declare @oldnextrun_intern datetime declare @Abends nvarchar(16) declare @JustNowLocal
4 datetime declare @StartofPeriod datetime declare @StartofNextPeriod datetime declare @Wochentag varchar(16) select @Abends = N' 23:59:59.999' select @JustNowLocal
5 = dateadd(ss, @CurrentUTCOffset, @JustNow) if @FrequencyType in ('Day', 'Week', 'Month', 'Year') or @FrequencyType like 'WD[_]__' begin if @StartTime 
6= 'LB:ST' begin  insert into @starttimes_intern(Parameter1) select case x.MyOffsetMinutes when 1440 then '23:59' else concat(dbo.QBM_FCVStringPadLeft( 
7dbo.QBM_FCVIntToString( x.MyOffsetMinutes / 60) , 2, '0') , ':' , dbo.QBM_FCVStringPadLeft( dbo.QBM_FCVIntToString(x.MyOffsetMinutes % 60), 2, '0') ) end
8 as StartTime from ( select convert(int, isnull(sum(MinutesAvailable), 0)) as MyOffsetMinutes from ( select e.dbid, e.name, e.SizePerDB, e.SizePerDB / 
9a.SizeOverAll as percentOfSize, 24.0 * 60.0 * (e.SizePerDB / a.SizeOverAll) as MinutesAvailable from ( select d.dbid, d.name,  convert(float, sum(f.size
10)) as SizePerDB  from sys.sysdatabases d join sys.master_files f on f.database_id = d.dbid where d.name not in ('master', 'tempdb', 'model', 'msdb') group
11 by d.dbid, d.name ) as e cross join ( select convert(float, sum(f.size)) as SizeOverAll from sys.sysdatabases d join sys.master_files f on f.database_id
12 = d.dbid where d.name not in ('master', 'tempdb', 'model', 'msdb') ) as a ) as parts where parts.dbid < db_id() ) as x end else  begin insert into @starttimes_intern
13(Parameter1) select s.ParameterValue from QBM_FCVStringToList(@StartTime, char(7), 1, 0) s where dbo.QBM_FGITimeIsValid(s.ParameterValue) = 1 if @@ROWCOUNT
14 = 0 begin insert into @starttimes_intern(Parameter1) select '23:59' end end  end select @lastrun_intern = @lastrun if isnull(@lastrun_intern , N'1899-12-30'
15) < N'1900-01-01' begin select @lastrun_intern = convert(datetime, N'1900-01-01', 121) end select @oldnextrun_intern = @oldnextrun if isnull(@oldnextrun_intern
16 , N'1899-12-30') < N'1900-01-01' begin select @oldnextrun_intern = convert(datetime, N'1900-01-01', 121)  select @lastrun_intern = convert(datetime, N'1900-01-01'
17, 121) end if @FrequencyType in ('Week', 'Month', 'Year') begin select @StartofPeriod = null select top 1 @StartofPeriod = convert(datetime, MAX(isodate
18), 121) from DialogCalendar where CONVERT(datetime, isodate, 121) <= @lastrun_intern and ( @FrequencyType = 'Week' and isWeek = 1 or @FrequencyType = 'Month'
19 and isMonth = 1 or @FrequencyType = 'Year' and isYear = 1 )   if @StartofPeriod is null begin select top 1 @StartofPeriod = convert(datetime, MAX(isodate
20), 121) from DialogCalendar where CONVERT(datetime, isodate, 121) <= @JustNowLocal and ( @FrequencyType = 'Week' and isWeek = 1 or @FrequencyType = 'Month'
21 and isMonth = 1 or @FrequencyType = 'Year' and isYear = 1 ) end if @FrequencyType = 'Week' begin select @StartofNextPeriod = DATEADD(wk, @frequency, @StartofPeriod
22) end if @FrequencyType = 'Month' begin select @StartofNextPeriod = DATEADD(mm, @frequency, @StartofPeriod) end if @FrequencyType = 'Year' begin select
23 @StartofNextPeriod = DATEADD(yy, @frequency, @StartofPeriod) end end if @FrequencyType like 'WD[_]__' begin  select @StartofPeriod = null select top 1
24 @StartofPeriod = convert(datetime, MAX(c.IsoDate), 121) from DialogCalendar c where CONVERT(datetime, c.IsoDate, 121) <= @lastrun_intern and c.isMonth
25 = 1 if @StartofPeriod is null begin select top 1 @StartofPeriod = convert(datetime, MAX(isodate), 121) from DialogCalendar where CONVERT(datetime, isodate
26, 121) <= @JustNowLocal and isMonth = 1 end select @Wochentag = case @FrequencyType when 'WD_Mo' then 'Monday' when 'WD_Tu' then 'Tuesday' when 'WD_We'
27 then 'Wednesday' when 'WD_Th' then 'Thursday' when 'WD_Fr' then 'Friday' when 'WD_Sa' then 'Saturday' when 'WD_Su' then 'Sunday' else '' end end select
28 @nextrun = @oldnextrun_intern    if @FrequencyType in ('Week', 'Year') begin  select top 1 @nextrun = min(convert(datetime, c.IsoDate + ' ' + st.Parameter1
29 , 121)) from DialogCalendar c, (select dbo.QBM_FCVStringToInt(mvp.parameterValue , 0) -1  as Fsubtype from dbo.QBM_FCVStringToList(@Frequencysubtype ,
30 nchar(7), 0, 0 ) mvp ) as Fsubtypes , @starttimes_intern st where (DATEADD(dd, Fsubtypes.Fsubtype, @StartofPeriod) = CONVERT(datetime, c.IsoDate, 121)
31  or DATEADD(dd, Fsubtypes.Fsubtype, @StartofNextPeriod) = CONVERT(datetime, c.IsoDate, 121) ) and (convert(datetime, c.IsoDate + ' ' + st.Parameter1 ,
32 121) > @JustNowLocal and @StartTime <> 'LB:ST'  or convert(datetime, c.IsoDate , 121) > @JustNowLocal and @StartTime = 'LB:ST' ) and @oldnextrun_intern
33 <= @JustNowLocal  end if @FrequencyType like 'WD[_]__' begin select top 1 @nextrun = min(convert(datetime, x.IsoDate + ' ' + st.Parameter1 , 121)) from
34 ( select c.IsoDate, c.CalendarYear, c.CalendarMonth, c.CalendarDay, c.DayOfWeek , row_number() over (partition by c.CalendarYear, c.CalendarMonth order
35 by c.CalendarDay) as nter , COUNT(*) over (partition by c.CalendarYear, c.CalendarMonth ) as letzter from DialogCalendar c where c.DayOfWeek = @Wochentag
36 and c.IsoDate >= @StartofPeriod and ( isnull(@FrequencySubType, '') = '' or c.CalendarMonth in (select dbo.QBM_FCVStringToInt(mvp.ParameterValue, 0) as
37 monat from dbo.QBM_FCVStringToList(@Frequencysubtype , nchar(7), 0, 0 ) mvp ) ) ) as x cross join @starttimes_intern st where (@Frequency > 0 and x.nter
38 = @Frequency or @Frequency < 0 and x.nter = x.letzter + @Frequency + 1 ) and convert(datetime, x.IsoDate + ' ' + st.Parameter1 , 121) > @JustNowLocal 
39and @oldnextrun_intern <= @JustNowLocal end if @FrequencyType in ('Month')  begin  select top 1 @nextrun = min(convert(datetime, c.IsoDate + ' ' + st.Parameter1
40 , 121)) from DialogCalendar c, (select dbo.QBM_FCVStringToInt(mvp.parameterValue , 0) -1  as Fsubtype from dbo.QBM_FCVStringToList(@Frequencysubtype ,
41 nchar(7), 0, 0 ) mvp ) as Fsubtypes , @starttimes_intern st where ( CONVERT(datetime, c.IsoDate, 121) = case when month(DATEADD(dd, Fsubtypes.Fsubtype
42, @StartofPeriod)) = month(@StartofPeriod) then DATEADD(dd, Fsubtypes.Fsubtype, @StartofPeriod) when month(DATEADD(dd, Fsubtypes.Fsubtype-1, @StartofPeriod
43)) = month(@StartofPeriod) then DATEADD(dd, Fsubtypes.Fsubtype-1, @StartofPeriod) when month(DATEADD(dd, Fsubtypes.Fsubtype-2, @StartofPeriod)) = month
44(@StartofPeriod) then DATEADD(dd, Fsubtypes.Fsubtype-2, @StartofPeriod) else DATEADD(dd, Fsubtypes.Fsubtype-3, @StartofPeriod)  end  or CONVERT(datetime
45, c.IsoDate, 121) = case when month(DATEADD(dd, Fsubtypes.Fsubtype, @StartofNextPeriod)) = month(@StartofNextPeriod) then DATEADD(dd, Fsubtypes.Fsubtype
46, @StartofNextPeriod) when month(DATEADD(dd, Fsubtypes.Fsubtype-1, @StartofNextPeriod)) = month(@StartofNextPeriod) then DATEADD(dd, Fsubtypes.Fsubtype-1
47, @StartofNextPeriod) when month(DATEADD(dd, Fsubtypes.Fsubtype-2, @StartofNextPeriod)) = month(@StartofNextPeriod) then DATEADD(dd, Fsubtypes.Fsubtype-2
48, @StartofNextPeriod) else DATEADD(dd, Fsubtypes.Fsubtype-3, @StartofNextPeriod)  end ) and (convert(datetime, c.IsoDate + ' ' + st.Parameter1 , 121) >
49 @JustNowLocal and @StartTime <> 'LB:ST'  or convert(datetime, c.IsoDate , 121) > @JustNowLocal and @StartTime = 'LB:ST' ) and @oldnextrun_intern <= @JustNowLocal
50 end if @FrequencyType = 'Day' begin select top 1 @nextrun = x.datum from (select convert(datetime, min(c.IsoDate + ' ' + st.Parameter1), 121) as datum
51 from DialogCalendar c , @starttimes_intern st where ( ( convert(datetime, c.IsoDate + ' ' + st.Parameter1, 121) > @JustNowLocal  and datediff(d, convert
52(datetime, c.IsoDate, 121), convert(datetime, @JustNowLocal, 121)) = 0 )  or datediff(d, @justnowlocal, convert(datetime, c.IsoDate + ' ' + st.Parameter1
53, 121)) >= @Frequency ) ) as x where @oldnextrun_intern <= @JustNowLocal end if @FrequencyType = 'Hour' begin  select top 1 @nextrun = dateadd(hh, @Frequency
54, @lastrun_intern) where @oldnextrun_intern <= @JustNowLocal  select top 1 @nextrun = dateadd(hh, @Frequency, @JustNowLocal) where @nextrun < @JustNowLocal
55 end if @FrequencyType = 'Min' begin  select top 1 @nextrun = dateadd(mi, @Frequency, @lastrun_intern) where @oldnextrun_intern <= @JustNowLocal  select
56 top 1 @nextrun = dateadd(mi, @Frequency, @JustNowLocal) where @nextrun < @JustNowLocal end  select @nextrun = convert(datetime, convert(nvarchar(16), 
57@nextrun, 121), 121) if right(convert(nvarchar(16), @nextrun, 121), 5) = N'00:00' begin  select @nextrun = dateadd(ms, 3, @nextrun) end ende: return (@nextrun
58) end 
59