USP_DATALIST_MEMBERSHIPAPPEAL

                CREATE procedure dbo.USP_DATALIST_MEMBERSHIPAPPEAL (@ID uniqueidentifier)
                as
                set nocount on;

        declare @MEMBERSHIPPROGRAMID uniqueidentifier;
        declare @CONSTITUENTID uniqueidentifier;

        select 
          @MEMBERSHIPPROGRAMID=MEMBERSHIP.MEMBERSHIPPROGRAMID,
          @CONSTITUENTID=MEMBER.CONSTITUENTID
        from 
          dbo.MEMBERSHIP
       left join dbo.MEMBER on 
          MEMBER.MEMBERSHIPID=MEMBERSHIP.ID
        where 
          MEMBER.ID=@ID;


                select
                    CONSTITUENTAPPEAL.ID,
                    CONSTITUENTAPPEAL.APPEALID,
                    APPEAL.NAME APPEALNAME,
                    APPEAL.DESCRIPTION,
                    CONSTITUENTAPPEAL.DATESENT,
                    CONSTITUENTAPPEAL.SOURCECODE,
                    case when CONSTITUENTAPPEAL.FINDERNUMBER = 0 then null else CONSTITUENTAPPEAL.FINDERNUMBER end as FINDERNUMBER,
                    CONSTITUENTAPPEAL.COMMENTS,
                    CONSTITUENTAPPEAL.MKTSEGMENTATIONID,
                    CONSTITUENTAPPEAL.MKTSEGMENTATIONSEGMENTID,
                    MKTPACKAGE.NAME as PACKAGE,
                    MKTSEGMENTATION.NAME as MAILING,
                    dbo.UFN_MKTSEGMENTATIONSEGMENT_GETNAME(CONSTITUENTAPPEAL.MKTSEGMENTATIONSEGMENTID) as SEGMENT,
                    dbo.UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME(CONSTITUENTAPPEAL.MKTSEGMENTATIONTESTSEGMENTID) as TESTSEGMENT,
                    (case when exists(select 1 from dbo.CONSTITUENTAPPEALRESPONSE where CONSTITUENTAPPEALRESPONSE.CONSTITUENTAPPEALID=CONSTITUENTAPPEAL.ID)
                        then convert(bit,1) else convert(bit,0) end) as HASRESPONSES,
                    cast((case when APPEALMAILING.ID is not null then 1 else 0 end) as bit) as ISAPPEALMAILING,
                    case
                        when CONSTITUENTAPPEAL.DATESENT >= dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0) then 'This month'
                        when CONSTITUENTAPPEAL.DATESENT >= dateadd(month, -1, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then 'Last month'
                        when CONSTITUENTAPPEAL.DATESENT >= dateadd(month, -2, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then 'Two months ago'
                        else 'Older'
                    end TIMEFRAMETEXT,
                    case
                        when CONSTITUENTAPPEAL.DATESENT >= dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0) then '0'
                        when CONSTITUENTAPPEAL.DATESENT >= dateadd(month, -1, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then '2'
                        when CONSTITUENTAPPEAL.DATESENT >= dateadd(month, -2, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then '4'
                        else '6'
                    end TIMEFRAMEGROUPSORT,
                    case
                        when MAILLETTER.ID is not null then MAILLETTER.NAME + ' (Mail)'
                        when EMAILLETTER.ID is not null then EMAILLETTER.NAME + ' (Email)'
                        else ''
                    end    LETTER
                from 
                    dbo.CONSTITUENTAPPEAL
                inner join 
                    dbo.APPEAL on APPEAL.ID = CONSTITUENTAPPEAL.APPEALID
                left join dbo.MKTPACKAGE on MKTPACKAGE.ID = CONSTITUENTAPPEAL.MKTPACKAGEID
                left join dbo.MKTSEGMENTATION on MKTSEGMENTATION.ID = CONSTITUENTAPPEAL.MKTSEGMENTATIONID 
                left join dbo.APPEALMAILING on APPEALMAILING.ID = CONSTITUENTAPPEAL.MKTSEGMENTATIONID
                left join dbo.APPEALMAILINGSETUPLETTER MAILLETTER
                    on MKTPACKAGE.ID = MAILLETTER.MAILPACKAGEID
                left join dbo.APPEALMAILINGSETUPLETTER EMAILLETTER
                    on MKTPACKAGE.ID = EMAILLETTER.EMAILPACKAGEID
                where 
          APPEAL.MEMBERSHIPPROGRAMID=@MEMBERSHIPPROGRAMID and 
                    CONSTITUENTAPPEAL.CONSTITUENTID = @CONSTITUENTID
                order by
                    TIMEFRAMEGROUPSORT asc, APPEAL.NAME;