USP_KPI_BUSINESSPROCESSDURATION

CREATE procedure dbo.[USP_KPI_BUSINESSPROCESSDURATION]
(
  @VALUE decimal(10, 2) output,
  @ASOFDATE datetime, 
  @BUSINESSPROCESSCATALOGID uniqueidentifier = null,
  @PARAMETERSETID uniqueidentifier
)
as
  set nocount on;

  declare @TABLENAME nvarchar(128);
  declare @SQL nvarchar(max);

  select
    @TABLENAME = isnull([BP].[PARAMETERTABLENAME] , '')
  from dbo.[V_BUSINESSPROCESSPARAMETERSETS] as [V]
  inner join dbo.[BUSINESSPROCESSCATALOG] as [BP] on [V].[BUSINESSPROCESSID] = [BP].[ID] 
  where [V].[PARAMETERSETID] = @PARAMETERSETID;

  if @TABLENAME <> ''
    begin
      begin try
        set @VALUE = 0;
        set @TABLENAME += 'STATUS';

        set @SQL = 'select top 1' + char(13) +
                   '  @VALUE = cast(datediff(s, [BUSINESSPROCESSSTATUS].[STARTEDON], [BUSINESSPROCESSSTATUS].[ENDEDON]) as decimal) / 60' + char(13) +
                   'from dbo.[' + @TABLENAME + '] as [STATUSTABLE]' + char(13) +
                   'inner join dbo.[BUSINESSPROCESSSTATUS] ON [BUSINESSPROCESSSTATUS].[ID] = [STATUSTABLE].[ID]' + char(13) +
                   'where [STATUSTABLE].[PARAMETERSETID] = @PARAMETERSETID' + char(13) +
                   --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

                   'and cast([STATUSTABLE].[DATECHANGED] as date) <= @ASOFDATE' + char(13) +
                   'order by [STATUSTABLE].[DATECHANGED] desc';

        --The @ASOFDATE parameter is intentionally implicitly cast to a "date" here so that we don't have to add another cast in the dynamic SQL above...

        exec sp_executesql @SQL, N'@ASOFDATE date, @VALUE money output, @PARAMETERSETID uniqueidentifier', @ASOFDATE = @ASOFDATE, @VALUE = @VALUE output, @PARAMETERSETID = @PARAMETERSETID;

        if @VALUE is null
          set @VALUE = 0;
      end try

      begin catch
        exec dbo.[USP_RAISE_ERROR];
        return 1;
      end catch
    end
  else
    begin
      raiserror('No PARAMETERTABLENAME value found in the BUSINESSPROCESSCATALOG table', 16, 11)
    end

  return 0;