USP_DATABASEGROWTHANALYSISPROCESSSCHEDULE_LOAD

create procedure dbo.[USP_DATABASEGROWTHANALYSISPROCESSSCHEDULE_LOAD]
(
  @BUSINESSPROCESSPARAMETERSETID uniqueidentifier
)
as begin
  set nocount on;

  declare @RECENTRUNS table ([STARTEDON] datetime, [ENDEDON] datetime);
  declare @LASTRUNON datetime;
  declare @AVERAGERUNSECONDS integer;
  declare @STATUSCODE tinyint;

  insert into @RECENTRUNS
  select top 5
    [BUSINESSPROCESSSTATUS].[STARTEDON],
    [BUSINESSPROCESSSTATUS].[ENDEDON]
  from dbo.[DATABASEGROWTHANALYSISPROCESS]
  left outer join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] = [DATABASEGROWTHANALYSISPROCESS].[ID]
  left outer join dbo.[APPUSER] on [APPUSER].[ID] = [BUSINESSPROCESSSTATUS].[STARTEDBYUSERID]
  where [DATABASEGROWTHANALYSISPROCESS].[ID] = @BUSINESSPROCESSPARAMETERSETID
  and [BUSINESSPROCESSSTATUS].[STATUSCODE] = 0
  order by [BUSINESSPROCESSSTATUS].[ENDEDON] desc;

  select
    @LASTRUNON = max([STARTEDON]),
    @AVERAGERUNSECONDS = avg(datediff(second, [STARTEDON], [ENDEDON]))
  from @RECENTRUNS;

  set @STATUSCODE = 3;

  if exists (select top 1 1 from dbo.[BUSINESSPROCESSSTATUS] where [BUSINESSPROCESSCATALOGID] = '23C3CAE9-FEC7-4EAB-BA79-E50C997A5BBF' and [BUSINESSPROCESSPARAMETERSETID] = @BUSINESSPROCESSPARAMETERSETID)
    select top 1
      @STATUSCODE = [STATUSCODE]
    from dbo.[BUSINESSPROCESSSTATUS]
    where [BUSINESSPROCESSCATALOGID] = '23C3CAE9-FEC7-4EAB-BA79-E50C997A5BBF'
    and [BUSINESSPROCESSPARAMETERSETID] = @BUSINESSPROCESSPARAMETERSETID
    order by [STARTEDON] desc;

  select
    [DATABASEGROWTHANALYSISPROCESS].[TSLONG],
    [SCHEDULE].[TYPECODE] as [SCHEDULETYPECODE],
    [SCHEDULE].[DAYOFMONTH] as [SCHEDULEMONTHLYDAYOFMONTH],
    [SCHEDULE].[WEEKCODE] as [SCHEDULEMONTHLYWEEKCODE],
    [SCHEDULE].[DAYOFWEEKCODE] as [SCHEDULEMONTHLYDAYOFWEEKCODE],
    [SCHEDULE].[DAYSOFWEEK] as [SCHEDULEDAYOFWEEKCODE],
    [SCHEDULE].[TIME] as [SCHEDULETIME],
    @LASTRUNON as [LASTRUNON],
    @AVERAGERUNSECONDS as [AVERAGERUNSECONDS],
    @STATUSCODE as [STATUSCODE]
  from dbo.[SCHEDULE]
  inner join dbo.[BUSINESSPROCESSSCHEDULE] on [BUSINESSPROCESSSCHEDULE].[SCHEDULEID] = [SCHEDULE].[ID]
  inner join dbo.[DATABASEGROWTHANALYSISPROCESS] on [DATABASEGROWTHANALYSISPROCESS].[ID] = [BUSINESSPROCESSSCHEDULE].[BUSINESSPROCESSPARAMETERSETID]
  where dbo.[DATABASEGROWTHANALYSISPROCESS].[ID] = @BUSINESSPROCESSPARAMETERSETID;

  return 0;
end