UFN_ACADEMICYEARS_OVERLAP

Make sure that this academic year does not overlap with another academic year in the same school.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@ACADEMICYEARID	uniqueidentifier	IN
@STARTDATE	date	IN
@ENDDATE	date	IN
@SCHOOLID	uniqueidentifier	IN

Definition

Copy



            CREATE function dbo.UFN_ACADEMICYEARS_OVERLAP
                (
                    @ACADEMICYEARID uniqueidentifier,
                    @STARTDATE date, 
                    @ENDDATE date,
                    @SCHOOLID uniqueidentifier
                )
            returns bit
            as begin
                -- Assume we will not have an overlap

                declare @RETVAL int = 0;

                -- see if a year exists

                if (exists (select ACADEMICYEAR.ID
                                        from ACADEMICYEAR
                                        where (ACADEMICYEAR.ID <> @ACADEMICYEARID) and
                                                    (ACADEMICYEAR.SCHOOLID = @SCHOOLID) and
                                                    ((ACADEMICYEAR.STARTDATE between @STARTDATE AND @ENDDATE) or
                                                     (ACADEMICYEAR.ENDDATE between @STARTDATE AND @ENDDATE) or
                           ((ACADEMICYEAR.STARTDATE <= @STARTDATE) and (ACADEMICYEAR.ENDDATE >= @ENDDATE)))))
                    set @RETVAL = 1;

                return @RETVAL;
            end