USP_DATALIST_COMMUNITYMEMBERNEWSLETTERSUBSCRIPTIONS_2

This datalist returns a list of all newsletter 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.
Definition

Copy


            CREATE procedure dbo.USP_DATALIST_COMMUNITYMEMBERNEWSLETTERSUBSCRIPTIONS_2
            (
                @CONSTITUENTID uniqueidentifier = null
            )
            as
                set nocount on;

                select
                    [ORDEREDNEWSLETTEREMAIL].[ID],
                    [ORDEREDNEWSLETTEREMAIL].[EMAILLISTNAME],
                    [ORDEREDNEWSLETTEREMAIL].[SUBSCRIBED],
                    [ORDEREDNEWSLETTEREMAIL].[SENTDATE],
                    [ORDEREDNEWSLETTEREMAIL].[OPENED],
                    [ORDEREDNEWSLETTEREMAIL].[CLICKEDTHROUGH],
                    [ORDEREDNEWSLETTEREMAIL].[URL] [EMAILURL]
                from
                (
                    select
                        row_number() over
                        (
                            partition by
                                [NETCOMMUNITYNEWSLETTERSUBSCRIPTION].[EMAILLISTMAPID]
                            order by
                                [NETCOMMUNITYEMAILJOBRECIPIENT].[SENTDATE] desc
                        ) [ROWNUMBER],
                        [NETCOMMUNITYNEWSLETTERSUBSCRIPTION].[ID],
                        [NETCOMMUNITYNEWSLETTERSUBSCRIPTION].[EMAILLISTNAME],
                        [NETCOMMUNITYNEWSLETTERSUBSCRIPTION].[SUBSCRIBED],
                        [NETCOMMUNITYEMAILJOBRECIPIENT].[SENTDATE],
                        [NETCOMMUNITYEMAILJOBRECIPIENT].[OPENED],
                        [NETCOMMUNITYEMAILJOBRECIPIENT].[CLICKEDTHROUGH],
                        [NETCOMMUNITYEMAILJOBRECIPIENT].[URL]
                    from
                        dbo.NETCOMMUNITYNEWSLETTERSUBSCRIPTION
                        left join dbo.NETCOMMUNITYEMAILJOBRECIPIENT on [NETCOMMUNITYNEWSLETTERSUBSCRIPTION].[LATESTEMAILJOBRECIPIENTID] = [NETCOMMUNITYEMAILJOBRECIPIENT].[ID]
                    where
                        [NETCOMMUNITYNEWSLETTERSUBSCRIPTION].[CONSTITUENTID] = @CONSTITUENTID
                ) ORDEREDNEWSLETTEREMAIL
                where
                    [ORDEREDNEWSLETTEREMAIL].[ROWNUMBER] = 1
                order by
                    [ORDEREDNEWSLETTEREMAIL].[SENTDATE] desc;