UFN_STUDENT_CLASS

CREATE function dbo.UFN_STUDENT_CLASS
(
    @CLASSID uniqueidentifier = null
)

returns @RESULTS TABLE
(
    CLASSID uniqueidentifier,
    SESSIONID uniqueidentifier,
    CLASSMEETINGGROUPID uniqueidentifier,
    PATTERNBLOCKID uniqueidentifier,
    STUDENTCOURSEID uniqueidentifier,
    STATUS nvarchar(50),
    STATUSCODE tinyint,
    STATUSDATE date,
    WITHDRAWALDATE date
)
as begin
    insert into @RESULTS
    select
        CLASSID,
        SESSIONID,
        CLASSMEETINGGROUPID,
        PATTERNBLOCKID,
        STUDENTCOURSEID,
        STUDENTCLASS.[STATUS],
        STUDENTCLASS.STATUSCODE,
        STUDENTCLASS.STATUSDATE,
        STUDENTCLASS.WITHDRAWALDATE
    from (select
                STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID,
                t.SESSIONID,
                CLASSMEETINGGROUP.ID as CLASSMEETINGGROUPID,
                CLASSMEETINGGROUP.CLASSID,
                CLASSMEETINGGROUP.PATTERNBLOCKID,
                STUDENTCLASSMEETINGGROUP.STATUS,                                
                STUDENTCLASSMEETINGGROUP.STATUSCODE,
                STUDENTCLASSMEETINGGROUP.STATUSDATE,
                t.WITHDRAWALDATE,
                ROW_NUMBER() over (partition by STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID, CLASSMEETINGGROUP.CLASSID order by CLASSMEETINGGROUP.startdate) ROW
            from dbo.STUDENTCLASSMEETINGGROUP
                inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                inner join
                    (select distinct
                        STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID,
                        CLASSMEETINGGROUP.CLASSID,
                        TERM.SESSIONID,
                        count(case when STUDENTCLASSMEETINGGROUP.STATUSCODE = 1 then 1 else null end) over (partition by STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID, CLASSMEETINGGROUP.CLASSID) as DROPPED,
                        count(case when STUDENTCLASSMEETINGGROUP.STATUSCODE = 2 then 1 else null end) over (partition by STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID, CLASSMEETINGGROUP.CLASSID) as WITHDRAWN,
                        count(case when STUDENTCLASSMEETINGGROUP.STATUSCODE = 3 then 1 else null end) over (partition by STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID, CLASSMEETINGGROUP.CLASSID) as TRANSFERED,
                        min(TERM.WITHDRAWALDATE) over (partition by STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID, CLASSMEETINGGROUP.CLASSID) WITHDRAWALDATE
                    from dbo.STUDENTCLASSMEETINGGROUP
                        inner join dbo.CLASSMEETINGGROUP on dbo.STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = dbo.CLASSMEETINGGROUP.ID
                        inner join dbo.CLASS on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                        inner join dbo.COURSE on CLASS.COURSEID = COURSE.ID
                        inner join dbo.ACADEMICYEAR on COURSE.SCHOOLID = ACADEMICYEAR.SCHOOLID
                        inner join dbo.SESSION on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID
                        inner join dbo.TERM on SESSION.ID = TERM.SESSIONID
                            and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                            and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                    where (@CLASSID is null or CLASS.ID = @CLASSID)
                    ) t
                on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = t.STUDENTCOURSEID and CLASSMEETINGGROUP.CLASSID = t.CLASSID
            where (t.WITHDRAWN > 0 and STUDENTCLASSMEETINGGROUP.STATUSCODE = 2) or
                (t.WITHDRAWN = 0 and t.TRANSFERED > 0 and STUDENTCLASSMEETINGGROUP.STATUSCODE = 3) or
                (t.WITHDRAWN = 0 and t.TRANSFERED = 0 and t.DROPPED > 0 and STUDENTCLASSMEETINGGROUP.STATUSCODE = 3) or
                (t.WITHDRAWN = 0 and t.TRANSFERED = 0 and t.DROPPED = 0)) STUDENTCLASS
    where 
        STUDENTCLASS.ROW = 1

    return
end