UFN_GROUPMEMBERDATERANGE_ISUNIQUE

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

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@GROUPMEMBERID	uniqueidentifier	IN
@DATEFROM	date	IN
@DATETO	date	IN

Definition

Copy



            CREATE function dbo.UFN_GROUPMEMBERDATERANGE_ISUNIQUE
            (
                @GROUPMEMBERID uniqueidentifier,
                @DATEFROM date,
                @DATETO date
            )
            returns bit
            with execute as caller
            as 
            begin
                declare @DUPLICATECOUNT int;

                select
                    @DUPLICATECOUNT = count(ID)
                from
                    dbo.GROUPMEMBERDATERANGE
                where
                    GROUPMEMBERID = @GROUPMEMBERID and
                    (
                        ( DATETO   between @DATEFROM and @DATETO) or
                        (@DATETO   between  DATEFROM and  DATETO) or
                        ( DATEFROM between @DATEFROM and @DATETO) or
                        (@DATEFROM between  DATEFROM and  DATETO) or

                        ( DATEFROM is null and @DATEFROM <=  DATETO)   or
                        (@DATEFROM is null and  DATEFROM <= @DATETO)   or
                        ( DATETO   is null and @DATETO   >=  DATEFROM) or
                        (@DATETO   is null and  DATETO   >= @DATEFROM) or

                        (@DATEFROM is null and  DATEFROM is null) or
                        (@DATETO   is null and  DATETO   is null) or
                        (@DATEFROM is null and @DATETO   is null) or
                        ( DATEFROM is null and  DATETO   is null)
                    )

                if (@DUPLICATECOUNT <= 1)
                    return 1;

                return 0;
            end