USP_BBNC_CONSTITEDUCATIONHISTORYDATA_FOR_GIVEN_CONSTITIDS

    CREATE procedure [dbo].[USP_BBNC_CONSTITEDUCATIONHISTORYDATA_FOR_GIVEN_CONSTITIDS](@IDs nvarchar(max), @IncludeUnaffiliated bit = 0)
      as
          set NOCOUNT on;

          declare @USEACADEMICCATALOG bit;
              select top 1
                  @USEACADEMICCATALOG = USEACADEMICCATALOG
              from
                  dbo.EDUCATIONALCONFIGURATION;

              set @USEACADEMICCATALOG = coalesce(@USEACADEMICCATALOG,0);

            declare @IDS_TABLE table (
                SEQUENCEID int,
                CONSTITUENTID uniqueidentifier
            );

            insert into
                @IDS_TABLE (SEQUENCEID)
            select
                ID
            from
                dbo.fnMakeIDsTableFromString(@IDs, ',');

            update
                @IDS_TABLE
            set
                CONSTITUENTID = C.ID
            from
                @IDS_TABLE IDT
            inner join
                dbo.CONSTITUENT C on C.SEQUENCEID = IDT.SEQUENCEID;

        --Adding CTE to better handle exclusion of unaffiliated data.

        with EDUHISTORY_CTE as (
            select
                EH.ID as 'EduHistoryID',
                EDUCATIONALINSTITUTION.NAME,
                EH.STARTDATE,
                EH.DATELEFT,
                ACADEMICCATALOGDEGREE.NAME as 'Degree',            
                ACADEMICCATALOGPROGRAM.PROGRAM as 'Program',
                EH.CLASSOF
            from
                @IDS_TABLE IDT
            inner join
                dbo.EDUCATIONALHISTORY EH on EH.CONSTITUENTID = IDT.CONSTITUENTID
            inner join dbo.EDUCATIONALINSTITUTION on EH.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
            left join dbo.ACADEMICCATALOGPROGRAM on EH.ACADEMICCATALOGPROGRAMID = ACADEMICCATALOGPROGRAM.ID
            left join dbo.ACADEMICCATALOGDEGREE on EH.ACADEMICCATALOGDEGREEID = ACADEMICCATALOGDEGREE.ID
            left join dbo.SCHOOL on EDUCATIONALINSTITUTION.ID = SCHOOL.ID
            where
                not SCHOOL.ID is null --A record in the school table indicates that this institution is affiliated

                and @USEACADEMICCATALOG = 1

            union all

            select
                EH.ID as 'EduHistoryID',
                EDUCATIONALINSTITUTION.NAME,
                EH.STARTDATE,
                EH.DATELEFT,  
                EDUCATIONALDEGREECODE.DESCRIPTION as 'DEGREE',            
                EDUCATIONALPROGRAMCODE.DESCRIPTION as 'PROGRAM',
                EH.CLASSOF
            from
                @IDS_TABLE IDT
            inner join
                dbo.EDUCATIONALHISTORY EH on EH.CONSTITUENTID = IDT.CONSTITUENTID
            inner join dbo.EDUCATIONALINSTITUTION on EH.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
            left join dbo.EDUCATIONALPROGRAMCODE on EH.EDUCATIONALPROGRAMCODEID = EDUCATIONALPROGRAMCODE.ID
            left join dbo.EDUCATIONALDEGREECODE on EH.EDUCATIONALDEGREECODEID = EDUCATIONALDEGREECODE.ID
            where 
                @USEACADEMICCATALOG = 0

            union all

            select
                EH.ID as 'EduHistoryID',
                EDUCATIONALINSTITUTION.NAME,
                EH.STARTDATE,
                EH.DATELEFT,  
                EDUCATIONALDEGREECODE.DESCRIPTION as 'DEGREE',            
                EDUCATIONALPROGRAMCODE.DESCRIPTION as 'PROGRAM',
                EH.CLASSOF
            from
                @IDS_TABLE IDT
            inner join
                dbo.EDUCATIONALHISTORY EH on EH.CONSTITUENTID = IDT.CONSTITUENTID
            inner join dbo.EDUCATIONALINSTITUTION on EH.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
            left join dbo.EDUCATIONALPROGRAMCODE on EH.EDUCATIONALPROGRAMCODEID = EDUCATIONALPROGRAMCODE.ID
            left join dbo.EDUCATIONALDEGREECODE on EH.EDUCATIONALDEGREECODEID = EDUCATIONALDEGREECODE.ID
            left join dbo.SCHOOL on EDUCATIONALINSTITUTION.ID = SCHOOL.ID
            where
                @USEACADEMICCATALOG = 1 and
                @IncludeUnaffiliated = 1 and
                SCHOOL.ID is null --No record in the school table indicates that this institution is *not* affiliated

        ) 

          select 
              EDUHISTORY_CTE.NAME,
              EDUCATIONALHISTORYTYPECODE.DESCRIPTION as SCHOOLTYPE,
              EDUCATIONALHISTORYSTATUSCODE.DESCRIPTION as STATUS,
          EDUHISTORY_CTE.DEGREE,
              EDUHISTORY_CTE.STARTDATE,
              EDUCATIONALHISTORY.ENDDATE,
              (select top 1 MAJORCODE.DESCRIPTION from dbo.EDUCATIONALMAJOR as MAJOR inner join dbo.EDUCATIONALMAJORCODE as MAJORCODE on MAJOR.EDUCATIONALMAJORCODEID = MAJORCODE.ID where MAJOR.EDUCATIONALHISTORYID = EDUCATIONALHISTORY.ID) as MAJOR,
              (select top 1 MAJORCODE.DESCRIPTION from dbo.EDUCATIONALMINOR as MINOR inner join dbo.EDUCATIONALMAJORCODE as MAJORCODE on MINOR.EDUCATIONALMAJORCODEID = MAJORCODE.ID where MINOR.EDUCATIONALHISTORYID = EDUCATIONALHISTORY.ID) as MINOR,
              EDUCATIONALHISTORY.GPA, 
          EDUHISTORY_CTE.CLASSOF,
          EDUCATIONALHISTORY.CONSTITUENCYSTATUSCODE,
              EDUCATIONALHISTORY.KNOWNNAME,
              EDUCATIONALHISTORY.FRATERNITY,
              case 
                when (EDUCATIONALHISTORY.ISPRIMARYRECORD = 1)
                then '-1'
                else '0'
              end as ISPRIMARYRECORD,
              CONSTITUENT.SEQUENCEID as CONSTITUENTSEQUENCEID,
          EDUCATIONALINSTITUTION.ISAFFILIATED,
          EDUCATIONALHISTORY.ID,
          EDUHISTORY_CTE.DATELEFT,
          EDUHISTORY_CTE.Program
          from
              dbo.CONSTITUENT
            inner join dbo.EDUCATIONALHISTORY on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
            inner join dbo.EDUCATIONALINSTITUTION on EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
            left join dbo.EDUCATIONALHISTORYTYPECODE on EDUCATIONALHISTORY.EDUCATIONALHISTORYTYPECODEID = EDUCATIONALHISTORYTYPECODE.ID
            left join dbo.EDUCATIONALHISTORYSTATUSCODE on EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSCODEID = EDUCATIONALHISTORYSTATUSCODE.ID
            inner join EDUHISTORY_CTE on EDUCATIONALHISTORY.ID = EDUHISTORY_CTE.EduHistoryID
            inner join @IDS_TABLE IDs on IDs.CONSTITUENTID = CONSTITUENT.ID
        order by EDUCATIONALHISTORY.ISPRIMARYRECORD desc