USP_DATALIST_CONSTITUENTGROUPAPPEAL

                CREATE procedure dbo.USP_DATALIST_CONSTITUENTGROUPAPPEAL 
        (
          @ID uniqueidentifier,
          @CURRENTAPPUSERID uniqueidentifier
        )
                as
                  set nocount on;

          declare @DATALISTID uniqueidentifier;
          set @DATALISTID = 'c15d0dc5-4dc0-45a5-a8e4-dfefceb24337';

          with APPEALS_CTE as
          (
            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,
                CONSTITUENTAPPEAL.CONSTITUENTID as CONSTITUENTID,
                case 
                    when exists(
                            select 1 
                            from dbo.CONSTITUENTAPPEALRESPONSE 
                            where CONSTITUENTAPPEALRESPONSE.CONSTITUENTAPPEALID=CONSTITUENTAPPEAL.ID
                        )
                        then convert(bit,1) 
                    else convert(bit,0) 
                end as HASRESPONSES,
                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 
                inner join dbo.GROUPMEMBER GM on CONSTITUENTAPPEAL.CONSTITUENTID = GM.MEMBERID
                left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                left join dbo.APPEALMAILINGSETUPLETTER MAILLETTER
                    on CONSTITUENTAPPEAL.MKTSEGMENTATIONID = MAILLETTER.APPEALMAILINGSETUPID
                        and CONSTITUENTAPPEAL.MKTPACKAGEID = MAILLETTER.MAILPACKAGEID
                left join dbo.APPEALMAILINGSETUPLETTER EMAILLETTER
                    on CONSTITUENTAPPEAL.MKTSEGMENTATIONID = EMAILLETTER.APPEALMAILINGSETUPID
                        and CONSTITUENTAPPEAL.MKTPACKAGEID = EMAILLETTER.EMAILPACKAGEID
            where GM.GROUPID = @ID
                and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= CONSTITUENTAPPEAL.DATESENT))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= CONSTITUENTAPPEAL.DATESENT)) 
                    or (GMDR.DATEFROM <= CONSTITUENTAPPEAL.DATESENT and GMDR.DATETO >= CONSTITUENTAPPEAL.DATESENT))

            union all

            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,
            CONSTITUENTAPPEAL.CONSTITUENTID as CONSTITUENTID,
            case 
                when exists(
                        select 1 
                        from dbo.CONSTITUENTAPPEALRESPONSE 
                        where CONSTITUENTAPPEALRESPONSE.CONSTITUENTAPPEALID=CONSTITUENTAPPEAL.ID
                    )
                    then convert(bit,1) 
                else convert(bit,0) 
            end as HASRESPONSES,
            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.APPEALMAILINGSETUPLETTER MAILLETTER
                    on CONSTITUENTAPPEAL.MKTSEGMENTATIONID = MAILLETTER.APPEALMAILINGSETUPID
                        and CONSTITUENTAPPEAL.MKTPACKAGEID = MAILLETTER.MAILPACKAGEID
                left join dbo.APPEALMAILINGSETUPLETTER EMAILLETTER
                    on CONSTITUENTAPPEAL.MKTSEGMENTATIONID = EMAILLETTER.APPEALMAILINGSETUPID
                        and CONSTITUENTAPPEAL.MKTPACKAGEID = EMAILLETTER.EMAILPACKAGEID
            where CONSTITUENTAPPEAL.CONSTITUENTID = @ID
          )
          select
            ID,
            APPEALID,
            APPEALNAME,
            NF.NAME CONSTITUENTNAME,
            DESCRIPTION,
            MAILING,
            LETTER,
            DATESENT,
            PACKAGE,
            SOURCECODE,
            SEGMENT,
            TESTSEGMENT,
            FINDERNUMBER,
            COMMENTS,
            MKTSEGMENTATIONID,
            MKTSEGMENTATIONSEGMENTID,
            case
                when C.DATESENT >= dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0) then 'This month'
                when C.DATESENT >= dateadd(month, -1, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then 'Last month'
                when C.DATESENT >= dateadd(month, -2, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then 'Two months ago'
                else 'Older'
            end TIMEFRAMETEXT,
            case
                when C.DATESENT >= dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0) then '0'
                when C.DATESENT >= dateadd(month, -1, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then '2'
                when C.DATESENT >= dateadd(month, -2, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then '4'
                else '6'
            end TIMEFRAMEGROUPSORT,
            case when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1 else 
              dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, C.CONSTITUENTID)
            end as 'HASPERMISSIONS',
            HASRESPONSES
            from APPEALS_CTE C
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.CONSTITUENTID) NF
            order by
                  TIMEFRAMEGROUPSORT asc, APPEALNAME asc;