USP_DATALIST_PROSPECTRESEARCHDASHBOARDEDUCATIONALHISTORY

                    CREATE procedure dbo.USP_DATALIST_PROSPECTRESEARCHDASHBOARDEDUCATIONALHISTORY
                    (
                        @CONSTITUENTID uniqueidentifier,
                        @ISVISIBLE bit = 1
                    )
                    as
                        set nocount on;

                        if @ISVISIBLE = 1
                        begin
                            declare @USEACADEMICCATALOG bit;                        
                            select top 1
                                @USEACADEMICCATALOG = EC.USEACADEMICCATALOG
                            from
                                dbo.EDUCATIONALCONFIGURATION EC;

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

                            select
                                HISTORY.ID,
                                INSTITUTION.NAME,
                                HISTORY.ISPRIMARYRECORD,
                                '' as DESCRIPTION, -- TYPE.DESCRIPTION

                                EDUCATIONALHISTORYSTATUS.DESCRIPTION as CONSTITUENCYSTATUS,
                                case when @USEACADEMICCATALOG = 1 and INSTITUTION.ISAFFILIATED = 1 then ACADEMICCATALOGDEGREE.NAME else dbo.UFN_EDUCATIONALDEGREECODE_GETDESCRIPTION(HISTORY.EDUCATIONALDEGREECODEID) end as DEGREE,
                                HISTORY.STARTDATE,
                                HISTORY.DATELEFT as ENDDATE,
                                '' as MAJOR, -- dbo.UFN_EDUCATIONALMAJOR_GETNAME(HISTORY.ID) as MAJOR,

                                '' as MINOR, -- dbo.UFN_EDUCATIONALMINOR_GETNAME(HISTORY.ID) as MINOR,

                                '' as GPA, -- case when HISTORY.GPA = 0 then '' else cast(HISTORY.GPA as nvarchar(5)) end as GPA,

                                case when HISTORY.CLASSOF = 0 then '' else cast(HISTORY.CLASSOF as nvarchar(4))    end as CLASSOF,
                                '' as KNOWNNAME, -- HISTORY.KNOWNNAME,

                                '' as FRATERNITY, -- HISTORY.FRATERNITY


                                case when @USEACADEMICCATALOG = 1 and INSTITUTION.ISAFFILIATED = 1 then ACADEMICCATALOGPROGRAM.PROGRAM else dbo.UFN_EDUCATIONALPROGRAMCODE_GETDESCRIPTION(HISTORY.EDUCATIONALPROGRAMCODEID) end as PROGRAM,
                                HISTORY.CONSTITUENCYSTATUSCODE,
                                dbo.UFN_EDUCATIONALHISTORYLEVELCODE_GETDESCRIPTION(HISTORY.EDUCATIONALHISTORYLEVELCODEID) as EDUCATIONALHISTORYLEVELCODE,
                                case when HISTORY.PREFERREDCLASSYEAR = 0 then '' else CAST(HISTORY.PREFERREDCLASSYEAR as nvarchar(4)) end as PREFERREDCLASSYEAR,
                                dbo.UFN_EDUCATIONALAWARDCODE_GETDESCRIPTION(HISTORY.EDUCATIONALAWARDCODEID) as EDUCATIONALAWARD,
                                dbo.UFN_EDUCATIONALSOURCECODE_GETDESCRIPTION(HISTORY.EDUCATIONALSOURCECODEID) as EDUCATIONALSOURCE,
                                HISTORY.EDUCATIONALSOURCEDATE,
                                HISTORY.COMMENT,
                                dbo.UFN_EDUCATIONALHISTORY_ADDITIONALINFORMATIONLIST(HISTORY.ID) as ADDITIONALINFORMATION,
                EDUCATIONALHISTORYREASONCODE.DESCRIPTION as REASONCODE
                            from dbo.EDUCATIONALHISTORY as HISTORY
                                inner join dbo.EDUCATIONALINSTITUTION as INSTITUTION on HISTORY.EDUCATIONALINSTITUTIONID = INSTITUTION.ID
                                left join dbo.ACADEMICCATALOGPROGRAM on HISTORY.ACADEMICCATALOGPROGRAMID = ACADEMICCATALOGPROGRAM.ID
                                left join dbo.ACADEMICCATALOGDEGREE on HISTORY.ACADEMICCATALOGDEGREEID = ACADEMICCATALOGDEGREE.ID
                                left join dbo.EDUCATIONALHISTORYSTATUS on HISTORY.EDUCATIONALHISTORYSTATUSID = EDUCATIONALHISTORYSTATUS.ID
                left join dbo.EDUCATIONALHISTORYREASONCODE on HISTORY.EDUCATIONALHISTORYREASONCODEID = EDUCATIONALHISTORYREASONCODE.ID
                            where HISTORY.CONSTITUENTID = @CONSTITUENTID

                            order by HISTORY.ISPRIMARYRECORD desc, HISTORY.CLASSOF desc;
                        end