USP_KPI_EMAILPROCESS_DURATION

          create procedure dbo.USP_KPI_EMAILPROCESS_DURATION(
              @VALUE decimal(20,4) output, 
              @ASOFDATE datetime, 
            @MONITORAVGORMAX integer,
              @INCLUDETRANSACTIONALPROCESSES bit = 1,
            @INCLUDEGENERALPURPOSEPROCESSES bit = 1,
            @INCLUDESTATUSPROCESSES bit = 1
          )
          as
            begin
            set nocount on;

            select @ASOFDATE = dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE);

            declare @GENERALPURPOSEID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
            declare @TRANSACTIONALID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
            declare @STATUSID uniqueidentifier = '00000000-0000-0000-0000-000000000000';

            if @INCLUDETRANSACTIONALPROCESSES = 1
              set @TRANSACTIONALID = 'F79575E5-FE4F-47BD-954C-AB056EF109D9';


            if @INCLUDEGENERALPURPOSEPROCESSES = 1
              set @GENERALPURPOSEID = '5066A489-6282-400C-96AF-1ECC1B7F281F';

            if @INCLUDESTATUSPROCESSES = 1
              set @STATUSID = '8C3ECDB1-BE50-4898-AD1B-98ECB6D07812';

            if @MONITORAVGORMAX = 0
            begin
              select @VALUE = max(datediff(s, BPS.STARTEDON, coalesce(BPS.ENDEDON, getdate())))
                from dbo.BUSINESSPROCESSSTATUS as BPS
                inner join dbo.GLOBALCHANGESTATUS as STATUS on STATUS.ID = BPS.ID
                inner join dbo.GLOBALCHANGE as GC on GC.ID = STATUS.PARAMETERSETID
                inner join dbo.GLOBALCHANGECATALOG as GCC on GCC.ID = GC.GLOBALCHANGECATALOGID and GCC.ID in(@TRANSACTIONALID, @GENERALPURPOSEID, @STATUSID)
                where BPS.STARTEDON <= @ASOFDATE;
            end
            else
            begin
              select @VALUE = avg(cast(datediff(s, BPS.STARTEDON, coalesce(BPS.ENDEDON, getdate())) as decimal(20,4)))
                from dbo.BUSINESSPROCESSSTATUS as BPS
                inner join dbo.GLOBALCHANGESTATUS as STATUS on STATUS.ID = BPS.ID
                inner join dbo.GLOBALCHANGE as GC on GC.ID = STATUS.PARAMETERSETID
                inner join dbo.GLOBALCHANGECATALOG as GCC on GCC.ID = GC.GLOBALCHANGECATALOGID and GCC.ID in(@TRANSACTIONALID, @GENERALPURPOSEID, @STATUSID)
                where BPS.STARTEDON <= @ASOFDATE;
            end

            end