USP_KPI_REVENUE_SELECTIONMONEYTOTAL_INTERNAL_2

            create procedure dbo.USP_KPI_REVENUE_SELECTIONMONEYTOTAL_INTERNAL_2

            @ASOFDATE datetime,
            @TOTAL money = 0 output,
            @AGGREGATENAME nvarchar(10) = 'SUM', --Possible values SUM,AVG,MIN,MAX

            @CURRENCYID uniqueidentifier = null
            as    

            set nocount on;

            declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
            declare @DECIMALDIGITS tinyint;
            declare @ROUNDINGTYPECODE tinyint;

            if @CURRENCYID is null
                set @CURRENCYID = @ORGANIZATIONCURRENCYID;

            select 
                @DECIMALDIGITS = DECIMALDIGITS,
                @ROUNDINGTYPECODE = ROUNDINGTYPECODE
            from 
                dbo.CURRENCY
            where
                CURRENCY.ID = @CURRENCYID;

            if @AGGREGATENAME='SUM'
                select @TOTAL = coalesce(SUM(REVENUE.AMOUNTINCURRENCY), 0) from dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) AS REVENUE            
                /*#IDSETEXTENSION*/
                where REVENUE.DATE <= @ASOFDATE

            else if @AGGREGATENAME='AVG'
                select @TOTAL = coalesce(AVG(REVENUE.AMOUNTINCURRENCY), 0) from dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) AS REVENUE                
                /*#IDSETEXTENSION*/
                where REVENUE.DATE <= @ASOFDATE

            else if @AGGREGATENAME='MIN'
                select @TOTAL = coalesce(MIN(REVENUE.AMOUNTINCURRENCY), 0) from dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) AS REVENUE                
                /*#IDSETEXTENSION*/
                where REVENUE.DATE <= @ASOFDATE

            else if @AGGREGATENAME='MAX'
                select @TOTAL = coalesce(MAX(REVENUE.AMOUNTINCURRENCY), 0) from dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) AS REVENUE                
                /*#IDSETEXTENSION*/
                where REVENUE.DATE <= @ASOFDATE
            else 
                BEGIN
                    SET @TOTAL =0;
                    RAISERROR('Valid values for @AGGREGATENAME are SUM, AVG, MIN, and MAX.',16,10);
                END