USP_ENTERREQUESTSBYSTUDENT_GETCOURSES

CREATE procedure dbo.USP_ENTERREQUESTSBYSTUDENT_GETCOURSES
(
    @SESSIONID uniqueidentifier,
    @SCHOOLID uniqueidentifier,
    @GRADELEVELID uniqueidentifier,
    @DEPARTMENTID uniqueidentifier
)
as
begin
    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date

    select
        @SESSION_STARTDATE = min(STARTDATE),
        @SESSION_ENDDATE = max(ENDDATE)
    from dbo.TERM
    where TERM.SESSIONID = @SESSIONID
    group by TERM.SESSIONID

    select distinct
        COURSE.ID,
        COURSE.COURSEID,
        COURSE.NAME,
        case when count(COURSERESTRICTIONSTARTTERM.ID) over (partition by SCHOOLGRADELEVEL.ID, COURSERESTRICTION.ID) = 1
            then TERM.ID
            else null
        end as STARTTERMID
    from dbo.COURSE
        inner join dbo.COURSEGRADELEVEL on COURSE.ID = COURSEGRADELEVEL.COURSEID
        inner join dbo.SCHOOLGRADELEVEL on COURSEGRADELEVEL.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
        inner join dbo.V_COURSERESTRICTION as COURSERESTRICTION on COURSEGRADELEVEL.COURSEID = COURSERESTRICTION.COURSEID
        inner join dbo.COURSERESTRICTIONSTARTTERM on COURSERESTRICTION.ID = COURSERESTRICTIONSTARTTERM.COURSERESTRICTIONID
        inner join dbo.TERM on
            COURSERESTRICTIONSTARTTERM.STARTDATE = TERM.STARTDATE and
            COURSERESTRICTIONSTARTTERM.ENDDATE = TERM.ENDDATE and
            @SESSIONID = TERM.SESSIONID
    where (@GRADELEVELID is null or @GRADELEVELID = '00000000-0000-0000-0000-000000000000' or SCHOOLGRADELEVEL.GRADELEVELID = @GRADELEVELID) and
        (@SCHOOLID is null or @SCHOOLID = '00000000-0000-0000-0000-000000000000' or SCHOOLGRADELEVEL.SCHOOLID = @SCHOOLID) and
        COURSERESTRICTIONSTARTTERM.STARTDATE >= @SESSION_STARTDATE and
        COURSERESTRICTIONSTARTTERM.ENDDATE <= @SESSION_ENDDATE and
        (
            @DEPARTMENTID is null or
            @DEPARTMENTID = '00000000-0000-0000-0000-000000000000' or
            COURSE.SELECTEDDEPARTMENTSCODE = 0 or
            exists (
                select 1
                from dbo.COURSEACADEMICCATALOGDEPARTMENT
                where COURSEACADEMICCATALOGDEPARTMENT.COURSEID = COURSE.ID and
                    ACADEMICCATALOGDEPARTMENTID = @DEPARTMENTID
            )
        )
    order by COURSE.COURSEID, COURSE.NAME
end