UFN_TERM_OVERLAP

Check to see if dates overlap an existing term for a session or dates of other sessions.
Return

Return Type
bit
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@SESSIONID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@WITHINSESSION	bit	IN
Definition

Copy


            CREATE function dbo.UFN_TERM_OVERLAP
            (
              @ID uniqueidentifier,
              @SESSIONID uniqueidentifier,
              @STARTDATE datetime,
              @ENDDATE datetime,
              @WITHINSESSION bit
            )
            returns BIT
            as begin
                declare @RETVAL bit = 0

                -- Make sure the data is OK...    

                if (@STARTDATE > @ENDDATE)
                begin
                    declare @DATESWAP datetime;
                    set @DATESWAP = @ENDDATE;
                    set @ENDDATE = @STARTDATE;
                    set @STARTDATE = @DATESWAP;
                end

                if (@WITHINSESSION = 1) 
                    begin                     
                    if exists(
                        (select 
                            TERM.ID 
                        from 
                            dbo.TERM
                        inner join 
                            dbo.SESSION on TERM.SESSIONID = SESSION.ID
                        where (TERM.ID <> @ID) and 
                              (TERM.SESSIONID = @SESSIONID) and
                              (SESSION.ACADEMICYEARID = (select ACADEMICYEARID from dbo.SESSION where ID = @SESSIONID)) and                            
                              ((TERM.STARTDATE between @STARTDATE and @ENDDATE) or 
                                (TERM.ENDDATE between @STARTDATE and @ENDDATE) or
                                ((TERM.STARTDATE <= @STARTDATE) and (TERM.ENDDATE >= @ENDDATE))
                                )))
                        set @RETVAL = 1;
                    end                    
                else 
                    -- check if the dates overlap other session dates, or will result in a session that overlaps other session dates.

                    begin
                        select @STARTDATE = coalesce(STARTDATE, @STARTDATE) from dbo.UFN_SESSION_GETDATES(@SESSIONID) where STARTDATE < @STARTDATE                 
                        select @ENDDATE = coalesce(ENDDATE, @ENDDATE) from dbo.UFN_SESSION_GETDATES(@SESSIONID) where ENDDATE > @ENDDATE                 
                        if exists(
                            (select 
                                SESSION.ID 
                            from 
                                dbo.TERM
                            inner join 
                                dbo.SESSION on TERM.SESSIONID = SESSION.ID
                            where ((TERM.ID <> @ID) or (@ID is null)) and 
                                  (TERM.SESSIONID <> @SESSIONID) and
                                  (SESSION.ACADEMICYEARID = (select ACADEMICYEARID from dbo.SESSION where ID = @SESSIONID))
                            group by SESSION.ID
                            having                            
                                  ((min(TERM.STARTDATE) between @STARTDATE and @ENDDATE) or 
                                    (max(TERM.ENDDATE) between @STARTDATE and @ENDDATE) or
                                    ((min(TERM.STARTDATE) <= @STARTDATE) and (max(TERM.ENDDATE) >= @ENDDATE)))))
                            set @RETVAL = 1;
                    end

                return @RETVAL;
            end