UFN_GETSTUDENTCOURSEREQUESTS

CREATE function dbo.UFN_GETSTUDENTCOURSEREQUESTS
(
    @CURRENTAPPUSERID as uniqueidentifier,
    @STUDENTID as uniqueidentifier
)
returns @RESULTS table
(
    ID uniqueidentifier,
    COURSEID uniqueidentifier,
    STARTTERMID uniqueidentifier,
    FULFILLED bit
)
as
begin
    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date

    select
        @SESSION_STARTDATE = min(STARTDATE),
        @SESSION_ENDDATE = max(ENDDATE)
    from dbo.TERM
        inner join dbo.APPUSERSESSION on TERM.SESSIONID = APPUSERSESSION.SESSIONID
    where APPUSERSESSION.ID = @CURRENTAPPUSERID
    group by APPUSERSESSION.ID, APPUSERSESSION.SESSIONID

    insert into @RESULTS
    (
        ID,
        COURSEID,
        STARTTERMID,
        FULFILLED
    )
    select
        STUDENTCOURSEREQUEST.ID,
        STUDENTCOURSE.COURSEID,
        TERM.ID as STARTTERMID,
        case when count(STUDENTCLASSMEETINGGROUP.ID) > 0 then 1 else 0 end as FULFILLED
    from dbo.STUDENTCOURSEREQUEST
        inner join dbo.STUDENTCOURSE on STUDENTCOURSEREQUEST.ID = STUDENTCOURSE.ID
        inner join dbo.COURSE on STUDENTCOURSE.COURSEID = COURSE.ID
        inner join dbo.SESSION on dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, STUDENTCOURSEREQUEST.STARTDATE, STUDENTCOURSEREQUEST.ENDDATE) = SESSION.ID
        left join dbo.TERM on STUDENTCOURSEREQUEST.CLASSSTARTDATE = TERM.STARTDATE
            and TERM.SESSIONID = SESSION.ID
        left join dbo.STUDENTCLASSMEETINGGROUP
            on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
    where STUDENTCOURSE.STUDENTID = @STUDENTID
        and STUDENTCOURSEREQUEST.STARTDATE >= @SESSION_STARTDATE
        and STUDENTCOURSEREQUEST.ENDDATE <= @SESSION_ENDDATE
    group by
        STUDENTCOURSEREQUEST.ID,
        STUDENTCOURSE.COURSEID,
        TERM.ID,
        STUDENTCOURSEREQUEST.CLASSSTARTDATE,
        COURSE.COURSEID
    order by
        STUDENTCOURSEREQUEST.CLASSSTARTDATE,
        COURSE.COURSEID

    return
end