UFN_DESIGNATIONLEVEL_REVENUERECEIVED_BYPERIOD

This function returns revenue received by period for fundraising purposes.
Return

Return Type
table
Parameters

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

Copy


            CREATE function dbo.UFN_DESIGNATIONLEVEL_REVENUERECEIVED_BYPERIOD
                (
                    @STARTDATE datetime, 
                    @ENDDATE datetime, 
                    @PERIODOPTION tinyint,
                    @CAMPAIGNHIERARCHYPATH hierarchyid = null
                )
                returns table
                as
                return(
                    select DL.ID DESIGNATIONLEVELID,
                            PERIODS.SEQUENCE,
                            PERIODS.PERIODYEAR,
                            PERIODS.STARTDATE,
                            PERIODS.ENDDATE,
                            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
                        inner join dbo.UFN_DESIGNATIONREPORT_GETPERIODS(dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),dbo.UFN_DATE_GETLATESTTIME(@ENDDATE),@PERIODOPTION) PERIODS on R.DATE >= PERIODS.STARTDATE and R.DATE <= PERIODS.ENDDATE
                    where (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, PERIODS.PERIODYEAR,PERIODS.STARTDATE,PERIODS.ENDDATE,PERIODS.SEQUENCE 

                )