UFN_STEWARDSHIPPLANMANAGERHISTORY_NOOVERLAP

Validates that a historical manager date range do not overlap the date range of another historical manager for a stewardship plan.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@STEWARDSHIPPLANMANAGERHISTORYID	uniqueidentifier	IN
@STEWARDSHIPPLANID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN

Definition

Copy



            create function dbo.UFN_STEWARDSHIPPLANMANAGERHISTORY_NOOVERLAP
            (
                @STEWARDSHIPPLANMANAGERHISTORYID uniqueidentifier,
                @STEWARDSHIPPLANID uniqueidentifier,
                @STARTDATE datetime,
                @ENDDATE datetime
            )
            returns bit
            with execute as caller
            as begin

                if exists (
                    select 1
                    from
                        dbo.STEWARDSHIPPLANMANAGERHISTORY
                    where
                        ID <> @STEWARDSHIPPLANMANAGERHISTORYID and
                        STEWARDSHIPPLANID = @STEWARDSHIPPLANID and
                        (
                            (@STARTDATE < STARTDATE and @ENDDATE > STARTDATE) or
                            (@STARTDATE = STARTDATE and @ENDDATE > @STARTDATE and ENDDATE > STARTDATE) or
                            (@STARTDATE > STARTDATE and @STARTDATE < ENDDATE) or
                            (STARTDATE is null and (@STARTDATE is null or @STARTDATE < ENDDATE)) or
                            (@STARTDATE is null and (STARTDATE is null or STARTDATE < @ENDDATE))
                        )
                    )
                    return 0;

                return 1;

            end