USP_REPORT_VSECOLLEGE_SECTION4A_PART1

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

        with VSECOLLEGECONSTITUENTS_CTE as (
          select
            REPORT_VSECOLLEGE.CONSTITUENTID,
            REPORT_VSECOLLEGE.REVENUESPLITAMOUNT,
            REPORT_VSECOLLEGE.VSECONSTITUENCYCODE,
            ACADEMICCATALOGPROGRAM.PROGRAMCODE,
            case 
              when ACADEMICCATALOGPROGRAM.PROGRAMCODE = 1 or ACADEMICCATALOGPROGRAM.PROGRAMCODE = 2 then 0
              when ACADEMICCATALOGPROGRAM.PROGRAMCODE = 0 then 1
              when ACADEMICCATALOGPROGRAM.PROGRAMCODE = 3 then 2
              else 3
            end as ACADEMICPRIORITY
          from
            dbo.UFN_REPORT_VSECOLLEGE(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) REPORT_VSECOLLEGE
            left outer join dbo.EDUCATIONALHISTORY on EDUCATIONALHISTORY.CONSTITUENTID = REPORT_VSECOLLEGE.CONSTITUENTID and EDUCATIONALHISTORY.ISPRIMARYRECORD = 1
            left outer join dbo.EDUCATIONALINSTITUTION on EDUCATIONALINSTITUTION.ID = EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID
            left outer join dbo.ACADEMICCATALOGPROGRAM on EDUCATIONALHISTORY.ACADEMICCATALOGPROGRAMID = ACADEMICCATALOGPROGRAM.ID
          where
            REPORT_VSECOLLEGE.VSECONSTITUENTTYPECODE = 0

          union all

          select
            REPORT_VSECOLLEGE.CONSTITUENTID,
            case
              when REPORT_VSECOLLEGE.FACEVALUE = 0 then 0
              else (REPORT_VSECOLLEGE.PRESENTVALUE * (REPORT_VSECOLLEGE.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSECOLLEGE.FACEVALUE))
            end as REVENUESPLITAMOUNT,
            REPORT_VSECOLLEGE.VSECONSTITUENCYCODE,
            ACADEMICCATALOGPROGRAM.PROGRAMCODE,
            case 
              when ACADEMICCATALOGPROGRAM.PROGRAMCODE = 1 or ACADEMICCATALOGPROGRAM.PROGRAMCODE = 2 then 0
              when ACADEMICCATALOGPROGRAM.PROGRAMCODE = 0 then 1
              when ACADEMICCATALOGPROGRAM.PROGRAMCODE = 3 then 2
              else 3
            end as ACADEMICPRIORITY
          from
            dbo.UFN_REPORT_VSECOLLEGE_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) REPORT_VSECOLLEGE
            left outer join dbo.EDUCATIONALHISTORY on EDUCATIONALHISTORY.CONSTITUENTID = REPORT_VSECOLLEGE.CONSTITUENTID and EDUCATIONALHISTORY.ISPRIMARYRECORD = 1
            left outer join dbo.EDUCATIONALINSTITUTION on EDUCATIONALINSTITUTION.ID = EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID
            left outer join dbo.ACADEMICCATALOGPROGRAM on EDUCATIONALHISTORY.ACADEMICCATALOGPROGRAMID = ACADEMICCATALOGPROGRAM.ID
          where
            REPORT_VSECOLLEGE.VSECONSTITUENTTYPECODE = 0
        )
        select 
          SUBSEL.VSECONSTITUENCYCODE,
          SUBSEL.PROGRAMCODE,
          SUBSEL.LABEL,
          SUM(SUBSEL.NUMBER_RECORDS) NUMBER_RECORDS,
          SUM(SUBSEL.NUMBER_SOLICITED) NUMBER_SOLICITED,
          SUM(SUBSEL.NUMBER_DONORS) NUMBER_DONORS,
          SUM(SUBSEL.TOTAL_AMOUNT) TOTAL_AMOUNT
        from
        (
          select
            case
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 0 and REPORT_VSECOUNTS.PROGRAMCODE is null then null
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 0 and REPORT_VSECOUNTS.PROGRAMCODE = 0 then 1
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 0 and REPORT_VSECOUNTS.PROGRAMCODE = 1 then 1
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 0 and REPORT_VSECOUNTS.PROGRAMCODE = 2 then 1
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 0 and REPORT_VSECOUNTS.PROGRAMCODE = 3 then 1
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 4 then 2
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 8 then 3
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 9 then 4
              when VSECONSTITUENCY.VSECONSTITUENCYCODE in (10, 11) then 5
            end as VSECONSTITUENCYCODE,
            case when REPORT_VSECOUNTS.PROGRAMCODE = 2 then 1 else REPORT_VSECOUNTS.PROGRAMCODE end as PROGRAMCODE,
            case
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 0 and REPORT_VSECOUNTS.PROGRAMCODE = 0 then '1a. Alumni-Undergraduate'
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 0 and REPORT_VSECOUNTS.PROGRAMCODE = 1 then '1b. Alumni-Graduate'
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 0 and REPORT_VSECOUNTS.PROGRAMCODE = 2 then '1b. Alumni-Graduate'
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 0 and REPORT_VSECOUNTS.PROGRAMCODE = 3 then '1c. Alumni-Non degree'
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 4 then '2. Parents'
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 8 then '3. Faculty and Staff'
              when VSECONSTITUENCY.VSECONSTITUENCYCODE = 9 then '4. Students'
              when VSECONSTITUENCY.VSECONSTITUENCYCODE in (10, 11) then '5. Other Individuals'
            end as LABEL,
            isnull(REPORT_VSECOUNTS.NUMRECORDS, 0) as NUMBER_RECORDS,
            isnull(REPORT_VSECOUNTS.NUMSOLICIT, 0) as NUMBER_SOLICITED,
            isnull(REPORT_VSECOLLEGE.NUMDONOR, 0) as NUMBER_DONORS,
            isnull(REPORT_VSECOLLEGE.TOTAMT, 0) as TOTAL_AMOUNT
          from
            dbo.VSECONSTITUENCY
            left outer join
            (
              select
                count(distinct VSECOLLEGECONSTITUENTS_CTE1.CONSTITUENTID) as NUMDONOR,
                sum(VSECOLLEGECONSTITUENTS_CTE1.REVENUESPLITAMOUNT) as TOTAMT,
                VSECOLLEGECONSTITUENTS_CTE1.VSECONSTITUENCYCODE,
                VSECOLLEGECONSTITUENTS_CTE1.PROGRAMCODE
              from
                VSECOLLEGECONSTITUENTS_CTE as VSECOLLEGECONSTITUENTS_CTE1
              where
                VSECOLLEGECONSTITUENTS_CTE1.ACADEMICPRIORITY = (
                  select MIN(VSECOLLEGECONSTITUENTS_CTE2.ACADEMICPRIORITY)
                  from VSECOLLEGECONSTITUENTS_CTE as VSECOLLEGECONSTITUENTS_CTE2
                  where VSECOLLEGECONSTITUENTS_CTE1.CONSTITUENTID = VSECOLLEGECONSTITUENTS_CTE2.CONSTITUENTID)
              group by VSECOLLEGECONSTITUENTS_CTE1.VSECONSTITUENCYCODE, VSECOLLEGECONSTITUENTS_CTE1.PROGRAMCODE
            ) as REPORT_VSECOLLEGE
              on REPORT_VSECOLLEGE.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
            left outer join dbo.UFN_REPORT_VSECOUNTS(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, 1) as REPORT_VSECOUNTS
              on REPORT_VSECOUNTS.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
              and (REPORT_VSECOUNTS.PROGRAMCODE = REPORT_VSECOLLEGE.PROGRAMCODE or (REPORT_VSECOUNTS.PROGRAMCODE is null and REPORT_VSECOLLEGE.PROGRAMCODE is null))
            where
              VSECONSTITUENCY.TYPECODE = 0
              and VSECONSTITUENCY.USAGETYPECODE <> 1

          union all
          select 1, 0, '1a. Alumni-Undergraduate', 0, 0, 0, 0
          union all
          select 1, 1, '1b. Alumni-Graduate', 0, 0, 0, 0
          union all
          select 1, 3, '1c. Alumni-Non degree', 0, 0, 0, 0
        ) as SUBSEL
        group by VSECONSTITUENCYCODE, PROGRAMCODE, LABEL