UFN_CAMPAIGNSUBPRIORITY_GETQUARTERLYPROGRESS

Returns the quarterly progress for a given campaign subpriority 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_CAMPAIGNSUBPRIORITY_GETQUARTERLYPROGRESS
            (
                @CAMPAIGNID uniqueidentifier, 
                @STARTDATE datetime,
                @ENDDATE datetime
            )
            returns @QUARTERGOALS table
                (
                    CAMAPIGNSUBPRIORITYID 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 
                        CAMPAIGNSUBPRIORITY.ID,
                        datepart(qq, @STARTDATE), 
                        datepart(yyyy, @STARTDATE),
                        @STARTDATE,
                        @NUMPARTS,
                        @CURRENTPART
                    from
                        dbo.CAMPAIGNSUBPRIORITY
                    inner join 
                        dbo.CAMPAIGNPRIORITY on CAMPAIGNPRIORITY.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
                    where
                        CAMPAIGNPRIORITY.CAMPAIGNID = @CAMPAIGNID;


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

                return
            end