spREINT_GetNewslettersInfo

    CREATE PROCEDURE [dbo].[spREINT_GetNewslettersInfo]
            (
            @MaxRecords int,
            @DownloadStartpoint datetime,
            @LastID as int
            )
            AS
            SET ROWCOUNT @MaxRecords 

            SELECT els.UserID, 
                bosp.BackofficeRecordID,
                els.EmailListID, 
                el2.name AS "name", 
                CASE WHEN els.HasOpted = 1 THEN 1 ELSE 0 END AS "subscribed", 
                e2.id AS "EmailID", 
                e2.emailtemplateID, 
                ejr2.sentdate, 
                ejr2.opened, 
                (SELECT MAX(d.UpdateDate) FROM (SELECT ejr2.updatedate as UpdateDate UNION SELECT els.UpdateDate) d ) as "UpdateDate",
                coalesce(er.ClickedThrough, 0) AS "ClickedThrough", 
                '' AS "URL", 
                ejr2.id AS "EmailJobRecipientID" 
            FROM EmailList_Subscription els 
            inner join BackOfficeSystemPeople bosp on els.BackofficeSystempeopleID=bosp.id and bosp.backofficeSystemid = 0 
            LEFT OUTER JOIN ( 
                SELECT els.id AS "ELSID", MAX(ejr.id) AS "EJRID" 
                    FROM emailjob_recipient ejr  
                        inner JOIN email e ON e.id = ejr.emailid 
                        inner JOIN email_emaillist eel ON eel.emailid = e.id 
                        inner JOIN emaillist el ON el.id = eel.emaillistid 
                        inner JOIN emaillist_subscription els ON els.backofficesystempeopleid = ejr.backofficesystempeopleid AND els.emaillistid = el.id 
                    GROUP BY els.id 
                ) AS subLastIssue ON subLastIssue.elsid = els.id 
            LEFT OUTER JOIN EmailJob_Recipient ejr2 ON ejr2.id = subLastIssue.ejrid 
            LEFT OUTER JOIN email e2 ON e2.id = ejr2.emailid  
            LEFT OUTER join dbo.[Email_Recipient] er on er.ID=ejr2.EmailRecipientID
            inner JOIN emaillist el2 ON el2.id = els.emaillistid 
            WHERE (e2.type IS NULL OR e2.type = 2) AND (ejr2.sent IS NULL OR ejr2.sent = 1) 
            AND (((ejr2.updatedate > @DownloadStartpoint) OR (els.UpdateDate > @DownloadStartPoint))
                OR (ejr2.updatedate IS NULL AND els.UpdateDate = @DownloadStartPoint AND els.EmailListID > @LastID)) 
            ORDER BY updatedate,EmailJobRecipientID ASC
            SET ROWCOUNT 0