UFN_STUDENTPROGRESSION_CHECKVALIDDATES

Student progression constraint that determines if a student progression conflicts with the date range of other student progressions.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@ENROLLMENTID	uniqueidentifier	IN
@STUDENTPROGRESSIONID	uniqueidentifier	IN
@STARTDATE	date	IN
@ENDDATE	date	IN

Definition

Copy



        create function dbo.UFN_STUDENTPROGRESSION_CHECKVALIDDATES(
            @ENROLLMENTID uniqueidentifier,
            @STUDENTPROGRESSIONID uniqueidentifier,
            @STARTDATE date,
            @ENDDATE date
        )
        returns bit
        with execute as caller
        as begin
            declare @ISVALID bit
            set @ISVALID = 1

            select  @ISVALID = CASE WHEN COUNT(dbo.STUDENTPROGRESSION.[ID]) > 0 THEN 0 ELSE 1 END
            from 
                dbo.STUDENTPROGRESSION
            where 
                (dbo.STUDENTPROGRESSION.[ENROLLMENTID] = @ENROLLMENTID) and
                (dbo.STUDENTPROGRESSION.[ID] <> @STUDENTPROGRESSIONID) and
                (
                    ((@STARTDATE >= dbo.STUDENTPROGRESSION.[STARTDATE]) and (@STARTDATE <= dbo.STUDENTPROGRESSION.[ENDDATE])) 
                    or
                    ((@ENDDATE >= dbo.STUDENTPROGRESSION.[STARTDATE]) and (@ENDDATE <= dbo.STUDENTPROGRESSION.[ENDDATE]))    
                    or
                    (@STARTDATE < dbo.STUDENTPROGRESSION.[STARTDATE] and @ENDDATE > STUDENTPROGRESSION.[ENDDATE])
                )
            return @ISVALID
        end