UFN_COURSERESTRICTION_VALIDDATESBYLENGTHINTERMS

Returns start and end dates of valid terms for a session.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@SESSIONSTARTDATE	date	IN
@SESSIONENDDATE	date	IN
@COURSEID	uniqueidentifier	IN

Definition

Copy



create function dbo.UFN_COURSERESTRICTION_VALIDDATESBYLENGTHINTERMS
(
    @SESSIONSTARTDATE date,
    @SESSIONENDDATE date,
    @COURSEID uniqueidentifier
)
returns table
as return 
(
    select
        @COURSEID 'COURSEID',         
        COURSERESTRICTIONSTARTTERM.STARTDATE, 
        (select max(CRT.ENDDATE) from
                (select top (COURSERESTRICTION.LENGTHINTERMS) ENDDATE 
                from dbo.TERM
                where  
                    (SESSIONID = dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, @SESSIONSTARTDATE, @SESSIONENDDATE)) and 
                    (ENDDATE >= (select ENDDATE from dbo.TERM where ID = dbo.UFN_TERM_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, COURSERESTRICTIONSTARTTERM.STARTDATE, COURSERESTRICTIONSTARTTERM.ENDDATE)))) CRT) 'ENDDATE'
    from 
        dbo.COURSERESTRICTIONSTARTTERM
    join
        dbo.COURSERESTRICTION on COURSERESTRICTIONSTARTTERM.COURSERESTRICTIONID = COURSERESTRICTION.ID
    join 
        dbo.COURSE on COURSERESTRICTION.COURSEID = COURSE.ID
    where COURSE.ID = @COURSEID and
            dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, @SESSIONSTARTDATE, @SESSIONENDDATE) = dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, COURSERESTRICTION.STARTDATE, COURSERESTRICTION.ENDDATE)            


)