UFN_VOLUNTEERDATERANGE_ISUNIQUE

Validates whether the volunteer constituency is unique.
Return

Return Type
bit
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@DATEFROM	date	IN
@DATETO	date	IN
Definition

Copy


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

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