UFN_DESIGNATIONLEVEL_REVENUERECEIVED

This function returns revenue received for fundraising purposes.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CAMPAIGNHIERARCHYPATH	hierarchyid	IN
Definition

Copy


            create function dbo.UFN_DESIGNATIONLEVEL_REVENUERECEIVED
            (
                @STARTDATE datetime, 
                @ENDDATE datetime,
                @CAMPAIGNHIERARCHYPATH hierarchyid = null
            )
            returns table
            as
            return(
                select DL.ID DESIGNATIONLEVELID,
                        sum(RS.AMOUNT) TOTALRECEIVED
                from dbo.DESIGNATIONLEVEL DL
                    inner join dbo.DESIGNATION D on (D.DESIGNATIONLEVEL1ID = DL.ID or
                                 D.DESIGNATIONLEVEL2ID = DL.ID or
                                 D.DESIGNATIONLEVEL3ID = DL.ID or             
                                 D.DESIGNATIONLEVEL4ID = DL.ID or
                                 D.DESIGNATIONLEVEL5ID = DL.ID)
                    inner join dbo.REVENUESPLIT RS on D.ID = RS.DESIGNATIONID
                    inner join dbo.REVENUE R on RS.REVENUEID = R.ID
                where (R.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) or @STARTDATE is null) and
                        (R.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE) or @ENDDATE is null) and
                        (R.TRANSACTIONTYPECODE = 0) and
                        (
                            @CAMPAIGNHIERARCHYPATH is null or
                            exists (
                                select 
                                    REVENUESPLITCAMPAIGN.REVENUESPLITID
                                from 
                                    dbo.REVENUESPLITCAMPAIGN
                                    inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                                where 
                                    REVENUESPLITCAMPAIGN.REVENUESPLITID = RS.ID and
                                    CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1
                            )
                        )
                group by DL.ID

            )