USP_DATALIST_COMMUNITYMEMBEREMAILRECEIVED

This datalist returns a list of all emails received by a community member.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@VIEWFILTER	tinyint	IN	View
Definition

Copy


            CREATE procedure dbo.USP_DATALIST_COMMUNITYMEMBEREMAILRECEIVED
            (
                @CONSTITUENTID uniqueidentifier = null,
                @VIEWFILTER tinyint = 2
            )
            as
                set nocount on;

                declare @ASOFDATE datetime;
                set @ASOFDATE = getdate();

                declare @STARTDATE datetime;
                select
                    @STARTDATE =
                        case @VIEWFILTER
                            when 0 then dbo.UFN_DATE_THISWEEK_FIRSTDAY(@ASOFDATE, 0)
                            when 1 then dbo.UFN_DATE_THISMONTH_FIRSTDAY(@ASOFDATE, 0)
                            when 2 then dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@ASOFDATE, 0)
                            when 3 then dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@ASOFDATE, 0)
                            when 4 then null
                        end;

                select
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[ID],
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[EMAILNAME],
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[SENTDATE],
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[BOUNCED],
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[BOUNCETEXT],
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[EMAILADDRESS],
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[OPENED],
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[CLICKEDTHROUGH],
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[DONATED],
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[EMAILSUBJECT],
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[URL]
                from
                    dbo.NETCOMMUNITYEMAILJOBRECIPIENT
                where
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[CONSTITUENTID] = @CONSTITUENTID
                    and [NETCOMMUNITYEMAILJOBRECIPIENT].[UPDATEDATE] is not null --Newsletter subscriptions can generate incomplete email job recipient rows

                    and
                    (
                        @STARTDATE is null
                        or
                        @STARTDATE <= [NETCOMMUNITYEMAILJOBRECIPIENT].[SENTDATE]
                    )
                order by
                    [NETCOMMUNITYEMAILJOBRECIPIENT].[SENTDATE] desc;