UFN_BATCHDESIGNATIONLEVELFINANCIALINFO_VALIDTIMEPERIOD

Determines whether the date range for this financial information has already been used.
Return

Return Type
bit
Parameters

Parameter	Parameter Type	Mode
@BATCHID	uniqueidentifier	IN
@DESIGNATIONLEVELID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
Definition

Copy


            create function dbo.UFN_BATCHDESIGNATIONLEVELFINANCIALINFO_VALIDTIMEPERIOD
            (
                @BATCHID uniqueidentifier,
                @DESIGNATIONLEVELID uniqueidentifier,
                @STARTDATE datetime,
                @ENDDATE datetime                                                
            )
            returns bit
            with execute as caller
            as 
            begin
                declare @DUPLICATECOUNT int;

                --check for existing rows in the current batch that violate the date range

                select
                    @DUPLICATECOUNT = count(ID)
                from
                    dbo.BATCHDESIGNATIONLEVELFINANCIALINFO
                where
                    BATCHID = @BATCHID and
                    DESIGNATIONLEVELID = @DESIGNATIONLEVELID and
                    (
                        ( ENDDATE   between @STARTDATE and @ENDDATE) or
                        (@ENDDATE   between  STARTDATE and  ENDDATE) or
                        ( STARTDATE between @STARTDATE and @ENDDATE) or
                        (@STARTDATE between  STARTDATE and  ENDDATE) 
                    );

                --check for rows already in the database that violate the date range

                if (@DUPLICATECOUNT = 0)
                begin
                    select
                        @DUPLICATECOUNT = count(ID)
                    from
                        dbo.DESIGNATIONLEVELFINANCIALINFO
                    where
                        DESIGNATIONLEVELID = @DESIGNATIONLEVELID and
                        (
                            ( ENDDATE   between @STARTDATE and @ENDDATE) or
                            (@ENDDATE   between  STARTDATE and  ENDDATE) or
                            ( STARTDATE between @STARTDATE and @ENDDATE) or
                            (@STARTDATE between  STARTDATE and  ENDDATE) 
                        );                                                    
                end

                if (@DUPLICATECOUNT <= 1)
                    return 1;

                return 0;

            end