UFN_STUDENT_GETSTUDENTPROGRESSIONBYDATE

Returns the student progression information by passing in student ID and date

Return

Return Type
uniqueidentifier

Parameters

Parameter	Parameter Type	Mode
@SPECIFIEDDATE	date	IN
@STUDENTID	uniqueidentifier	IN
@SCHOOLID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_STUDENT_GETSTUDENTPROGRESSIONBYDATE
(    
    @SPECIFIEDDATE    date = null,
    @STUDENTID uniqueidentifier = null,
    @SCHOOLID uniqueidentifier = null
)
returns uniqueidentifier
with execute as caller
as 
begin
    declare @RetVal uniqueidentifier

    SELECT TOP 1 @RetVal = dbo.STUDENTPROGRESSION.ID
            FROM dbo.EDUCATIONALHISTORY
            inner join dbo.SCHOOL S1 on dbo.EDUCATIONALHISTORY.[EDUCATIONALINSTITUTIONID] = S1.[ID]
            inner join dbo.STUDENTPROGRESSION on dbo.STUDENTPROGRESSION.[ENROLLMENTID] = dbo.EDUCATIONALHISTORY.[ID]
            inner join dbo.SCHOOLGRADELEVEL on dbo.STUDENTPROGRESSION.[SCHOOLGRADELEVELID] = dbo.SCHOOLGRADELEVEL.[ID]
            inner join dbo.ACADEMICYEAR on (dbo.ACADEMICYEAR.STARTDATE=dbo.STUDENTPROGRESSION.STARTDATE) AND (dbo.ACADEMICYEAR.ENDDATE=dbo.STUDENTPROGRESSION.ENDDATE) AND (dbo.ACADEMICYEAR.SCHOOLID = dbo.SCHOOLGRADELEVEL.SCHOOLID)
            WHERE (dbo.EDUCATIONALHISTORY.[CONSTITUENTID] = @STUDENTID) 
                and (dbo.ACADEMICYEAR.[BILLINGSTARTDATE]<=@SPECIFIEDDATE)
                and (dbo.ACADEMICYEAR.[BILLINGENDDATE]>=@SPECIFIEDDATE)
                and ((dbo.SCHOOLGRADELEVEL.[SCHOOLID] = @SCHOOLID) or (@SCHOOLID IS NULL))
            ORDER BY dbo.STUDENTPROGRESSION.[STARTDATE];
    return @RetVal
end