UFN_STEPSUMMARY_COMPLETED_2

Returns the number of completed steps for each fundraiser back to a given date.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@STARTDATE	datetime	IN
@INCLUDESTEWARDSHIP	bit	IN
@ONLYOWNEDINTERACTIONS	bit	IN
Definition

Copy


            CREATE function dbo.UFN_STEPSUMMARY_COMPLETED_2
            (
                @STARTDATE datetime, 
                @INCLUDESTEWARDSHIP bit,
                @ONLYOWNEDINTERACTIONS bit = 0
            )
            returns @data table(
                ID uniqueidentifier,
                COUNT integer    
            )
            as begin
                insert into @data
                    select
                        DATA.ID,
                        count(*)
                    from
                    (
                        select 
                            FUNDRAISERID as ID
                        from 
                            dbo.INTERACTION
                        where 
                            COMPLETED = 1
                            and DATE > @STARTDATE
                            and (PROSPECTPLANID is not null or (dbo.UFN_GETINCLUDEGRANTS() = 1 and FUNDINGREQUESTID is not null))

                        union all

                        select 
                            INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID as ID
                        from 
                            dbo.INTERACTION
                            left join dbo.INTERACTIONADDITIONALFUNDRAISER on INTERACTION.ID = INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID
                        where 
                            @ONLYOWNEDINTERACTIONS = 0
                            and INTERACTION.COMPLETED = 1
                            and INTERACTION.DATE > @STARTDATE
                            and (INTERACTION.PROSPECTPLANID is not null or (dbo.UFN_GETINCLUDEGRANTS() = 1 and INTERACTION.FUNDINGREQUESTID is not null))
                    ) as DATA

                    group by
                        DATA.ID;

                if @INCLUDESTEWARDSHIP = 1
                    merge @data as data
                    using (
                        select
                            STEP.CONSTITUENTID ID,
                            count(*) COUNT
                        from
                            dbo.STEWARDSHIPPLANSTEP STEP                
                        where
                            not STEP.CONSTITUENTID is null
                            and STEP.COMPLETED = 1
                            and STEP.ACTUALDATE >= @STARTDATE
                        group by STEP.CONSTITUENTID
                    ) steps
                        on data.ID = steps.ID
                    when matched then update set data.COUNT = data.COUNT + steps.COUNT
                    when not matched by target then insert values(steps.ID, steps.COUNT);

                return;
            end