UFN_STUDENTENROLLMENT_CURRENTINFORMATION

Returns a table of all current student enrollment information for a given student
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode	Description
@StudentID	uniqueidentifier	IN
Definition

Copy


        CREATE function dbo.UFN_STUDENTENROLLMENT_CURRENTINFORMATION(
            @StudentID uniqueidentifier
        )
        returns @STUDENTENROLLMENT_CURRENTINFORMATION TABLE
       (
        ENROLLMENTID    uniqueidentifier,
        SCHOOLID   uniqueidentifier,
        EDUCATIONALREASONLEFTCODEID uniqueidentifier,
        STARTDATE dbo.UDT_FUZZYDATE,
        DATELEFT dbo.UDT_FUZZYDATE,
        DATEGRADUATED dbo.UDT_FUZZYDATE,
        CLASSYEAR dbo.UDT_YEAR,
        EDUCATIONALHISTORYSTATUSID uniqueidentifier,
        STATUSDATE date,
        STATUSREASON nvarchar(100)
       )
        with execute as caller
        as begin
              begin
                INSERT INTO @STUDENTENROLLMENT_CURRENTINFORMATION 
                    (
                    ENROLLMENTID, 
                    SCHOOLID, 
                    EDUCATIONALREASONLEFTCODEID, 
                    STARTDATE, 
                    DATELEFT, 
                    DATEGRADUATED, 
                    CLASSYEAR, 
                    EDUCATIONALHISTORYSTATUSID, 
                    STATUSDATE, 
                    STATUSREASON
                    )
                select 
                    dbo.EDUCATIONALHISTORY.[ID], 
                    dbo.EDUCATIONALHISTORY.[EDUCATIONALINSTITUTIONID], 
                    dbo.EDUCATIONALHISTORY.[EDUCATIONALREASONLEFTCODEID], 
                    dbo.EDUCATIONALHISTORY.[STARTDATE], 
                    dbo.EDUCATIONALHISTORY.[DATELEFT], 
                    dbo.EDUCATIONALHISTORY.[DATEGRADUATED],
                    dbo.EDUCATIONALHISTORY.[PREFERREDCLASSYEAR], 
                    dbo.EDUCATIONALHISTORYSTATUSHISTORY.[EDUCATIONALHISTORYSTATUSID], 
                    dbo.EDUCATIONALHISTORYSTATUSHISTORY.[STATUSDATE], 
                    dbo.EDUCATIONALHISTORYSTATUSHISTORY.[STATUSREASON]
                from 
                    dbo.EDUCATIONALHISTORY
                inner join dbo.SCHOOL on dbo.EDUCATIONALHISTORY.[EDUCATIONALINSTITUTIONID] = dbo.SCHOOL.[ID]
                inner join dbo.EDUCATIONALHISTORYSTATUSHISTORY on dbo.EDUCATIONALHISTORYSTATUSHISTORY.[ID] = dbo.UFN_EDUCATIONALHISTORY_GETCURRENTSTATUSHISTORYID(dbo.EDUCATIONALHISTORY.[ID])
                where 
                    (dbo.EDUCATIONALHISTORY.[CONSTITUENTID] = @StudentID) and (dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[STARTDATE]) <= GETDATE()) and ((GETDATE() <= dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[DATELEFT])) or (dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[DATELEFT]) is null))
                order by dbo.EDUCATIONALHISTORY.[STARTDATE]
              end

            return
        end