UFN_CAMPAIGN_DESIGNATIONDATESOVERLAP

            CREATE function dbo.UFN_CAMPAIGN_DESIGNATIONDATESOVERLAP
            (            
                @DESIGNATIONS xml
            )
            returns bit    
            with execute as caller
            as
            begin
                declare @DESIGNATIONID uniqueidentifier;
                declare @SEQUENCE int;
                declare @DATEFROM datetime;
                declare @DATETO datetime;

                declare DESIGNATION_CURSOR cursor local fast_forward for
                    select distinct
                        tf.DESIGNATIONID
                    from 
                        dbo.UFN_CAMPAIGN_DESIGNATIONS_FROMITEMLISTXML(@DESIGNATIONS) tf;

                open DESIGNATION_CURSOR;
                fetch next from DESIGNATION_CURSOR into @DESIGNATIONID;

                while (@@FETCH_STATUS = 0)
                begin 
                    declare DATE_CURSOR cursor local fast_forward for
                        select SEQUENCE, DATEFROM, DATETO from dbo.UFN_CAMPAIGN_DESIGNATIONS_FROMITEMLISTXML(@DESIGNATIONS) where DESIGNATIONID = @DESIGNATIONID;

                    open DATE_CURSOR 
                    fetch next from DATE_CURSOR into @SEQUENCE, @DATEFROM, @DATETO;

                    while (@@FETCH_STATUS = 0)
                    begin 
                        if exists (
                            select 1 from dbo.UFN_CAMPAIGN_DESIGNATIONS_FROMITEMLISTXML(@DESIGNATIONS) tf
                            where (
                                    ((tf.DATETO between @DATEFROM and @DATETO) or (tf.DATEFROM between @DATEFROM and @DATETO) or (tf.DATEFROM is null and tf.DATETO is null)) --Both not null

                                    or 
                                    (@DATEFROM is null and ((tf.DATEFROM <= @DATETO) or (tf.DATETO <= @DATETO))) --@DATEFROM is null

                                    or
                                    (@DATETO is null and ((tf.DATEFROM >= @DATEFROM) or (tf.DATETO >= @DATEFROM))) --@DATETO is null

                                    or
                                    (@DATEFROM is null and @DATETO is null and tf.SEQUENCE is not null) -- Both are null

                                  )
                            and (tf.SEQUENCE <> @SEQUENCE)
                            and (tf.DESIGNATIONID = @DESIGNATIONID)
                        )
                        begin
                            close DATE_CURSOR;
                            deallocate DATE_CURSOR;

                            close DESIGNATION_CURSOR;
                            deallocate DESIGNATION_CURSOR;

                            return 1;
                        end

                        fetch next from DATE_CURSOR into @SEQUENCE, @DATEFROM, @DATETO;
                    end

                    close DATE_CURSOR;
                    deallocate DATE_CURSOR;



                    fetch next from DESIGNATION_CURSOR into @DESIGNATIONID;
                end

                close DESIGNATION_CURSOR;
                deallocate DESIGNATION_CURSOR;

                return 0;
            end