UFN_RELATIONSHIPJOBINFODATERANGE_ISUNIQUE

Validates whether the job information dates are unique.
Return

Return Type
bit
Parameters

Parameter	Parameter Type	Mode
@RELATIONSHIPSETID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
Definition

Copy


            create function dbo.UFN_RELATIONSHIPJOBINFODATERANGE_ISUNIQUE
            (
                @RELATIONSHIPSETID uniqueidentifier,
                @STARTDATE datetime,
                @ENDDATE datetime
            )
            returns bit
            with execute as caller
            as begin
                declare @DUPLICATECOUNT int;

                select
                    @DUPLICATECOUNT = count(ID)
                from
                    dbo.RELATIONSHIPJOBINFO
                where
                    RELATIONSHIPSETID = @RELATIONSHIPSETID and
                    (
                        ( ENDDATE   between @STARTDATE and @ENDDATE) or
                        (@ENDDATE   between  STARTDATE and  ENDDATE) or
                        ( STARTDATE between @STARTDATE and @ENDDATE) or
                        (@STARTDATE between  STARTDATE and  ENDDATE) or

                        ( STARTDATE is null and @STARTDATE <=  ENDDATE)   or
                        (@STARTDATE is null and  STARTDATE <= @ENDDATE)   or
                        ( ENDDATE   is null and @ENDDATE   >=  STARTDATE) or
                        (@ENDDATE   is null and  ENDDATE   >= @STARTDATE) or

                        (@STARTDATE is null and  STARTDATE is null) or
                        (@ENDDATE   is null and  ENDDATE   is null) or
                        (@STARTDATE is null and @ENDDATE   is null) or
                        ( STARTDATE is null and  ENDDATE   is null)
                    )

                if (@DUPLICATECOUNT <= 1)
                    return 1;

                return 0;
            end