UFN_GROUPMEMBERROLE_ISUNIQUE2

Determines whether a group member role overlaps with another one for the same group member

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@GROUPMEMBERID	uniqueidentifier	IN
@GROUPMEMBERROLECODEID	uniqueidentifier	IN
@STARTDATE	date	IN
@ENDDATE	date	IN

Definition

Copy



CREATE function [dbo].[UFN_GROUPMEMBERROLE_ISUNIQUE2]
(
    @ID uniqueidentifier,
    @GROUPMEMBERID uniqueidentifier,
    @GROUPMEMBERROLECODEID uniqueidentifier,
    @STARTDATE date,
    @ENDDATE date
)
returns bit
with execute as caller
as 
begin
    declare @DUPLICATECOUNT int;

    select
        @DUPLICATECOUNT = count(ID)
    from
        dbo.GROUPMEMBERROLE
    where
        ID <> @ID and
        GROUPMEMBERID = @GROUPMEMBERID and
        GROUPMEMBERROLECODEID = @GROUPMEMBERROLECODEID 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