UFN_CAMPAIGNPRIORITY_GETQUARTERLYPROGRESS

Returns the quarterly progress for a given campaign priority for the campaign priority report.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@CAMPAIGNID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN

Definition

Copy



            CREATE function dbo.UFN_CAMPAIGNPRIORITY_GETQUARTERLYPROGRESS
            (
                @CAMPAIGNID uniqueidentifier,
                @STARTDATE datetime,
                @ENDDATE datetime
            )
            returns @QUARTERGOALS table
                (
                    CAMAPIGNPRIORITYID uniqueidentifier not null,
                    QUARTER tinyint not null,
                    YEARNAME int not null,
                    STARTDATE datetime not null,
                    TOTALQUARTERS int not null,
                    SEQUENCE decimal not null
                )
            as
            begin
                declare @NUMPARTS int;
                declare @CURRENTPART decimal;

                set @NUMPARTS = datediff(qq, @STARTDATE, @ENDDATE) + 1
                set @CURRENTPART = 1

                WHILE datediff(qq, @STARTDATE, @ENDDATE) > -1
                    begin                    
                    INSERT INTO @QUARTERGOALS
                    select 
                        CAMPAIGNPRIORITY.ID,
                        datepart(qq, @STARTDATE), 
                        datepart(yyyy, @STARTDATE),
                        @STARTDATE,
                        @NUMPARTS,
                        @CURRENTPART
                    from
                        dbo.CAMPAIGNPRIORITY
                    where
                        CAMPAIGNID = @CAMPAIGNID;

                    set @STARTDATE = dateadd(qq, 1, @STARTDATE);            
                    set @CURRENTPART = @CURRENTPART + 1;
                    CONTINUE
                    end

                return
            end