UFN_STUDENTCURRENTHOMEROOMTEACHER_GETID

        CREATE function dbo.UFN_STUDENTCURRENTHOMEROOMTEACHER_GETID(@ID uniqueidentifier)
        returns uniqueidentifier
        with execute as caller
        as begin
            declare @HomeroomTeacherID uniqueidentifier;

            select @HomeroomTeacherID = 
                dbo.STUDENTPROGRESSION.[HOMEROOMTEACHERID]
                from dbo.EDUCATIONALHISTORY
                inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
                where GETDATE() between STUDENTPROGRESSION.STARTDATE and STUDENTPROGRESSION.ENDDATE
                and EDUCATIONALHISTORY.CONSTITUENTID = @ID;

            If @HomeroomTeacherID is null
            select top 1 @HomeroomTeacherID = 
                dbo.STUDENTPROGRESSION.[HOMEROOMTEACHERID]
                from dbo.EDUCATIONALHISTORY
                inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
                where GETDATE() <= STUDENTPROGRESSION.STARTDATE
                and EDUCATIONALHISTORY.CONSTITUENTID = @ID
                order by STUDENTPROGRESSION.STARTDATE;

            return @HomeroomTeacherID;
        end