USP_DATALIST_STUDENTPROGRESSION

            CREATE procedure dbo.USP_DATALIST_STUDENTPROGRESSION(@ENROLLMENTID uniqueidentifier)
            as
                set nocount on;

                select dbo.STUDENTPROGRESSION.[ID],
                       dbo.SCHOOLGRADELEVEL.[SCHOOLID],
                       dbo.SCHOOLGRADELEVEL.[GRADELEVELID],
                       dbo.STUDENTPROGRESSION.[HOMEROOMTEACHERID],
                       SCHOOL_CONSTIT.[KEYNAME],
                       ACADEMICYEARNAMECODE.DESCRIPTION as [ACADEMICYEAR],
                       dbo.GRADELEVEL.[ABBREVIATION],
                       ADVISOR_CONSTIT.[NAME],
                       HOMEROOM_CONSTIT.[NAME],
                       dbo.STUDENTADVISOR.[FACULTYID] as [ADVISORID],
                       dbo.UFN_STUDENTPROGRESSION_RECORDCOUNT(dbo.EDUCATIONALHISTORY.[CONSTITUENTID]) AS RECORDCOUNT
                from dbo.STUDENTPROGRESSION
                inner join dbo.EDUCATIONALHISTORY on dbo.STUDENTPROGRESSION.[ENROLLMENTID] = dbo.EDUCATIONALHISTORY.[ID]
                inner join dbo.SCHOOLGRADELEVEL on dbo.SCHOOLGRADELEVEL.[ID] = dbo.STUDENTPROGRESSION.[SCHOOLGRADELEVELID]
                inner join dbo.GRADELEVEL on dbo.SCHOOLGRADELEVEL.[GRADELEVELID] = dbo.GRADELEVEL.[ID]
                inner join dbo.CONSTITUENT as SCHOOL_CONSTIT on SCHOOL_CONSTIT.[ID] = dbo.SCHOOLGRADELEVEL.[SCHOOLID]
                inner join dbo.ACADEMICYEAR on ACADEMICYEAR.ID = dbo.UFN_ACADEMICYEAR_GET_FORSCHOOL_BYDATE(dbo.SCHOOLGRADELEVEL.[SCHOOLID], dbo.STUDENTPROGRESSION.[STARTDATE], STUDENTPROGRESSION.[ENDDATE])
                inner join dbo.ACADEMICYEARNAMECODE on ACADEMICYEAR.ACADEMICYEARNAMECODEID = ACADEMICYEARNAMECODE.ID
                left outer join dbo.CONSTITUENT as HOMEROOM_CONSTIT on HOMEROOM_CONSTIT.[ID] = dbo.STUDENTPROGRESSION.[HOMEROOMTEACHERID]
                left outer join dbo.STUDENTADVISOR on dbo.STUDENTADVISOR.[ID] = dbo.UFN_STUDENTADVISOR_GETFIRSTID(dbo.STUDENTPROGRESSION.[ID])
                left outer join dbo.CONSTITUENT as ADVISOR_CONSTIT on ADVISOR_CONSTIT.[ID] = dbo.STUDENTADVISOR.[FACULTYID]
                where dbo.STUDENTPROGRESSION.[ENROLLMENTID] = @ENROLLMENTID
                order by dbo.STUDENTPROGRESSION.[STARTDATE] ASC