UFN_COURSERESTRICTION_LENGTHINTERMS_VALID

create function dbo.UFN_COURSERESTRICTION_LENGTHINTERMS_VALID(
    @COURSERESTRICTIONSID uniqueidentifier,
    @LENGTHINTERMS int
)
returns bit
with execute as caller
as begin

    declare @retval bit = 0

    declare @MAXSTARTDATE date
    select @MAXSTARTDATE = max(STARTDATE) from dbo.COURSERESTRICTIONSTARTTERM where COURSERESTRICTIONSTARTTERM.COURSERESTRICTIONID = @COURSERESTRICTIONSID

    -- verify lengthinterms does not exceed available terms in the session after the last selected start term.

    if @LENGTHINTERMS <= (select 
                                count(*)
                            from 
                                dbo.COURSERESTRICTION
                                join 
                                    dbo.COURSE on COURSE.ID = COURSERESTRICTION.COURSEID
                                join 
                                    dbo.SESSION on SESSION.ID = dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, COURSERESTRICTION.STARTDATE, COURSERESTRICTION.ENDDATE)
                                join 
                                    dbo.TERM on TERM.SESSIONID = SESSION.ID
                            where
                                COURSERESTRICTION.ID = @COURSERESTRICTIONSID
                                and TERM.STARTDATE >= @MAXSTARTDATE)
        set @retval = 1

    return @retval

end