UFN_GROUPMEMBERROLE_ISUNIQUE

Determines whether or not a given date range overlaps with any other date range records for a group member role of a specific type

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@GROUPMEMBERID	uniqueidentifier	IN
@GROUPMEMBERROLECODEID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN

Definition

Copy



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

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