UFN_STUDENTCURRENTACADEMICYEAR_GETNAME

Get the name of a student's current or future academic year
Return

Return Type
nvarchar(100)
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN
Definition

Copy


        CREATE function dbo.UFN_STUDENTCURRENTACADEMICYEAR_GETNAME(@ID uniqueidentifier) returns nvarchar(100) 
        with execute as caller
        as begin
            declare @year nvarchar(100);

            select @year = 
                ACADEMICYEARNAMECODE.DESCRIPTION 
                from dbo.EDUCATIONALHISTORY
                inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
                inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
                inner join dbo.ACADEMICYEAR on SCHOOLGRADELEVEL.SCHOOLID = ACADEMICYEAR.SCHOOLID
                inner join dbo.ACADEMICYEARNAMECODE on ACADEMICYEAR.ACADEMICYEARNAMECODEID = ACADEMICYEARNAMECODE.ID
                and STUDENTPROGRESSION.STARTDATE = ACADEMICYEAR.STARTDATE
                and STUDENTPROGRESSION.ENDDATE = ACADEMICYEAR.ENDDATE
                where GETDATE() between STUDENTPROGRESSION.STARTDATE and STUDENTPROGRESSION.ENDDATE
                and EDUCATIONALHISTORY.CONSTITUENTID = @ID

            If @year is null
            select top 1 @year = 
                ACADEMICYEARNAMECODE.DESCRIPTION
                from dbo.EDUCATIONALHISTORY
                inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
                inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
                inner join dbo.ACADEMICYEAR on SCHOOLGRADELEVEL.SCHOOLID = ACADEMICYEAR.SCHOOLID
                inner join dbo.ACADEMICYEARNAMECODE on ACADEMICYEAR.ACADEMICYEARNAMECODEID = ACADEMICYEARNAMECODE.ID
                and STUDENTPROGRESSION.STARTDATE = ACADEMICYEAR.STARTDATE
                and STUDENTPROGRESSION.ENDDATE = ACADEMICYEAR.ENDDATE
                where GETDATE() <= STUDENTPROGRESSION.STARTDATE
                and EDUCATIONALHISTORY.CONSTITUENTID =@ID
                order by STUDENTPROGRESSION.STARTDATE;

            return @year;
        end