UFN_DESIGNATIONLEVEL_GETTOTALRECEIVEDINCURRENCY

Returns the total amount received for a given designation level in a given currency.

Return

Return Type
money

Parameters

Parameter	Parameter Type	Mode
@DESIGNATIONLEVELID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CURRENCYID	uniqueidentifier	IN

Definition

Copy



            create function dbo.UFN_DESIGNATIONLEVEL_GETTOTALRECEIVEDINCURRENCY
            (
                @DESIGNATIONLEVELID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @CURRENCYID uniqueidentifier = null
            ) 
            returns money
            with execute as caller
            as begin

                declare @RESULT money;

                select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                        @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                with DESIGNATIONS_CTE as (
                    select ID 
                    from dbo.DESIGNATION D
                    where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID or 
                          D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID
                )
                select @RESULT = coalesce(sum(
                    dbo.UFN_DESIGNATION_GETTOTALRECEIVED_INCURRENCY(
                        DESIGNATIONS_CTE.ID,
                        @STARTDATE,
                        @ENDDATE,
                        0,
                        @CURRENCYID
                    )), 0)
                from DESIGNATIONS_CTE

                return @RESULT;
            end