USP_DATALIST_CONSTITUENTMEMBERSHIPAPPEAL

                    CREATE procedure dbo.USP_DATALIST_CONSTITUENTMEMBERSHIPAPPEAL (@ID uniqueidentifier)
                as
                set nocount on;
                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 IS NOT NULL and 
                    CONSTITUENTAPPEAL.CONSTITUENTID = @ID
                order by
                    TIMEFRAMEGROUPSORT asc, APPEAL.NAME;