USP_REPORT_VSECOLLEGE_SECTION4A_PART2

Returns VSE college report Section 4a part 2 data.
Parameters

Parameter	Parameter Type	Mode
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@INCLUDEINACTIVE	smallint	IN
@INCLUDEDECEASED	smallint	IN
@USEGIFTDATE	smallint	IN
Definition

Copy


            CREATE procedure dbo.USP_REPORT_VSECOLLEGE_SECTION4A_PART2
            (
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @INCLUDEINACTIVE smallint = 0,
                @INCLUDEDECEASED smallint = 0,
                @USEGIFTDATE smallint = 0
            )
            with execute as owner
            as
                set nocount on;

                declare @RESULTS table
                (
                    LABEL nvarchar(150),
                    NUMBER_DONORS int,
                    AMOUNT money
                );

                insert into @RESULTS
                (LABEL, NUMBER_DONORS, AMOUNT)
                (
                    select
                        '1. Personal and Family',
                        coalesce(count(distinct REPORT_VSECOLLEGE.CONSTITUENTID), 0),
                        coalesce(sum(REPORT_VSECOLLEGE.REVENUESPLITAMOUNT), 0)
                    from
                        dbo.VSECONSTITUENCY
                        left outer join (
                            select
                                REPORT_VSECOLLEGE.REVENUESPLITAMOUNT,
                                REPORT_VSECOLLEGE.CONSTITUENTID,
                                REPORT_VSECOLLEGE.VSECONSTITUENCYCODE
                            from
                                dbo.VSECONSTITUENCY
                            left outer join dbo.UFN_REPORT_VSECOLLEGE(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSECOLLEGE
                                on REPORT_VSECOLLEGE.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                            where
                                VSECONSTITUENCY.VSECONSTITUENCYCODE = 12

                            union all

                            select
                                case when REPORT_VSECOLLEGE.FACEVALUE = 0 then 0 else (REPORT_VSECOLLEGE.PRESENTVALUE * (REPORT_VSECOLLEGE.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSECOLLEGE.FACEVALUE)) end as REVENUESPLITAMOUNT,
                                REPORT_VSECOLLEGE.CONSTITUENTID,
                                REPORT_VSECOLLEGE.VSECONSTITUENCYCODE
                            from
                                dbo.VSECONSTITUENCY
                            left outer join dbo.UFN_REPORT_VSECOLLEGE_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSECOLLEGE
                                on REPORT_VSECOLLEGE.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                            where
                                VSECONSTITUENCY.VSECONSTITUENCYCODE = 12

                        ) REPORT_VSECOLLEGE
                        on REPORT_VSECOLLEGE.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                );

                insert into @RESULTS
                (LABEL, NUMBER_DONORS, AMOUNT)
                (
                    select
                        '2. Other foundations and trusts, excluding corporate',
                        coalesce(count(distinct REPORT_VSECOLLEGE.CONSTITUENTID), 0),
                        coalesce(sum(REPORT_VSECOLLEGE.REVENUESPLITAMOUNT), 0)
                    from
                        dbo.VSECONSTITUENCY
                        left outer join (
                            select
                                REPORT_VSECOLLEGE.REVENUESPLITAMOUNT,
                                REPORT_VSECOLLEGE.CONSTITUENTID,
                                REPORT_VSECOLLEGE.VSECONSTITUENCYCODE
                            from
                                dbo.VSECONSTITUENCY
                            left outer join dbo.UFN_REPORT_VSECOLLEGE(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSECOLLEGE
                                on REPORT_VSECOLLEGE.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                            where
                                VSECONSTITUENCY.VSECONSTITUENCYCODE = 13

                            union all

                            select
                                case when REPORT_VSECOLLEGE.FACEVALUE = 0 then 0 else (REPORT_VSECOLLEGE.PRESENTVALUE * (REPORT_VSECOLLEGE.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSECOLLEGE.FACEVALUE)) end as REVENUESPLITAMOUNT,
                                REPORT_VSECOLLEGE.CONSTITUENTID,
                                REPORT_VSECOLLEGE.VSECONSTITUENCYCODE
                            from
                                dbo.VSECONSTITUENCY
                            left outer join dbo.UFN_REPORT_VSECOLLEGE_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSECOLLEGE
                                on REPORT_VSECOLLEGE.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                            where
                                VSECONSTITUENCY.VSECONSTITUENCYCODE = 13

                        ) REPORT_VSECOLLEGE
                        on REPORT_VSECOLLEGE.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                );

                select
                    LABEL,
                    NUMBER_DONORS,
                    AMOUNT
                from @RESULTS;