UFN_STEPSUMMARY_PLANNED_2

Returns the number of planned steps for a fundraiser up until a given date.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode	Description
@ENDDATE	datetime	IN
@ONLYOWNEDINTERACTIONS	bit	IN
Definition

Copy


            CREATE function dbo.UFN_STEPSUMMARY_PLANNED_2
            (
                @ENDDATE datetime,
                @ONLYOWNEDINTERACTIONS bit = 0
            )
            returns table
            as return (
                with INCLUDEGRANTS_CTE as (
                    select top 1
                        INCLUDEGRANTS
                    from dbo.MAJORGIVINGCONFIGURATION
                )
                select
                    DATA.ID,
                    count(*) as COUNT
                from
                (
                    select
                        INTERACTION.FUNDRAISERID as ID
                    from 
                        dbo.INTERACTION
                        cross join INCLUDEGRANTS_CTE
                    where 
                        INTERACTION.COMPLETED = 0 
                        and INTERACTION.DATE < @ENDDATE
                        and (
                            INTERACTION.PROSPECTPLANID is not null
                            or (INCLUDEGRANTS_CTE.INCLUDEGRANTS = 1 and INTERACTION.FUNDINGREQUESTID is not null)
                        )

                    union all

                    select
                        INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID as ID
                    from 
                        dbo.INTERACTION
                        left join dbo.INTERACTIONADDITIONALFUNDRAISER on INTERACTION.ID = INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID
                        cross join INCLUDEGRANTS_CTE
                    where 
                        @ONLYOWNEDINTERACTIONS = 0 
                        and INTERACTION.COMPLETED = 0 
                        and INTERACTION.DATE < @ENDDATE
                        and (
                            INTERACTION.PROSPECTPLANID is not null
                            or (INCLUDEGRANTS_CTE.INCLUDEGRANTS = 1 and INTERACTION.FUNDINGREQUESTID is not null)
                        )

                ) as DATA

                group by
                    DATA.ID
            )