UFN_APPEALMAILING_PREVIOUSYEARAVERAGES

Returns response rate and average gift amount for the past three years for all appeal mailings of the appeal of the given appeal mailing.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@APPEALMAILINGID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_APPEALMAILING_PREVIOUSYEARAVERAGES
(
    @APPEALMAILINGID uniqueidentifier
)
returns table
as
return 
(
    select
        datepart(year,MKTSEGMENTATION.MAILDATE) YEAR,
        sum(MKTSEGMENTATIONACTIVE.RESPONSERATE) RESPONSERATE,
        SUM(MKTSEGMENTATIONACTIVE.AVERAGEGIFTAMOUNT) AVERAGEGIFTAMOUNT
    from
        dbo.APPEALMAILING
      inner join MKTSEGMENTATION as CURRENTMAILING on CURRENTMAILING.ID = @APPEALMAILINGID
      inner join APPEAL on APPEAL.ID = APPEALMAILING.APPEALID
      inner join APPEALMAILING as AM on AM.APPEALID = APPEAL.ID
      inner join MKTSEGMENTATION on MKTSEGMENTATION.ID = AM.ID
      inner join MKTSEGMENTATIONACTIVE on MKTSEGMENTATIONACTIVE.ID = AM.ID
    where
        APPEALMAILING.ID = @APPEALMAILINGID
        and
        datepart(year,MKTSEGMENTATION.MAILDATE) is not null
        and 
        datepart(year,MKTSEGMENTATION.MAILDATE) < datepart(year, CURRENTMAILING.MAILDATE)
    group by
        datepart(year,MKTSEGMENTATION.MAILDATE)
)