UFN_ACADEMICYEARS_BILLINGDATES_OVERLAP

Make sure that this academic year's billing dates do not overlap with another academic year's billing dates 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_BILLINGDATES_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.BILLINGSTARTDATE between @STARTDATE AND @ENDDATE) or
                                                     (ACADEMICYEAR.BILLINGENDDATE between @STARTDATE AND @ENDDATE) or
                           ((ACADEMICYEAR.BILLINGSTARTDATE <= @STARTDATE) and (ACADEMICYEAR.BILLINGENDDATE >= @ENDDATE)))))
                    set @RETVAL = 1;

                return @RETVAL;
            end