USP_DATALIST_APPEALMAILINGSELECTIONMERGESELECTIONS

Displays the selections created from merged selections and associated with a current appeal mailing.

Parameters

Parameter	Parameter Type	Mode	Description
@RECORDTYPEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_APPEALMAILINGSELECTIONMERGESELECTIONS
(
    @RECORDTYPEID uniqueidentifier
)
as
begin
    set nocount on;

    select distinct
        IDSETREGISTER.ID as ID,
        IDSETREGISTERMERGE.NAME as NAME
    from
        dbo.IDSETREGISTER
        inner join dbo.IDSETREGISTERMERGE on IDSETREGISTER.ID = IDSETREGISTERMERGE.IDSETREGISTERID

        -- Filter by appeal mailing

        inner join 
            dbo.MKTSEGMENTSELECTION on MKTSEGMENTSELECTION.SELECTIONID = IDSETREGISTER.ID
        inner join
            dbo.MKTSEGMENT on MKTSEGMENT.ID = MKTSEGMENTSELECTION.SEGMENTID
        inner join 
            dbo.MKTSEGMENTATIONSEGMENT on MKTSEGMENTATIONSEGMENT.SEGMENTID = MKTSEGMENT.ID
        inner join
            dbo.MKTSEGMENTATION on MKTSEGMENTATION.ID = MKTSEGMENTATIONSEGMENT.SEGMENTATIONID
        inner join
            dbo.APPEALMAILING on APPEALMAILING.ID = MKTSEGMENTATION.ID
    where
        IDSETREGISTER.RECORDTYPEID = @RECORDTYPEID 
        and IDSETREGISTER.ACTIVE = 1
        and IDSETREGISTER.ISSYSTEM = 0

        -- Appeal mailings for this year and next year

        and (MKTSEGMENTATION.MAILDATE is not null)
        and (year(MKTSEGMENTATION.MAILDATE) between year(getdate()) and (year(getdate()) + 1))

    union

    select distinct
        IDSETREGISTER.ID as ID,
        IDSETREGISTERMERGE.NAME as NAME
    from
        dbo.IDSETREGISTER
        inner join dbo.IDSETREGISTERMERGE on IDSETREGISTER.ID = IDSETREGISTERMERGE.IDSETREGISTERID

        -- For appeal mailings not yet started

        inner join 
            APPEALMAILINGSETUPSELECTION on APPEALMAILINGSETUPSELECTION.SELECTIONID = IDSETREGISTER.ID
        inner join
            APPEALMAILINGSETUP on APPEALMAILINGSETUP.ID = APPEALMAILINGSETUPSELECTION.APPEALMAILINGSETUPID
        inner join
            MKTSEGMENTATION on MKTSEGMENTATION.ID = APPEALMAILINGSETUP.ID
    where
        IDSETREGISTER.RECORDTYPEID = @RECORDTYPEID and IDSETREGISTER.ACTIVE = 1

        -- Appeal mailings for this year and next year

        and (MKTSEGMENTATION.MAILDATE is not null)
        and (year(MKTSEGMENTATION.MAILDATE) between year(getdate()) and (year(getdate()) + 1))
end