fnGetEmailStatsByDonor

CREATE   function [dbo].[fnGetEmailStatsByDonor]
    (
        @ClientSitesID int, 
        @RaisersEdgeRecordID int
    )
returns table as
return (

     select top 100 percent
        e.[ID] EmailID, 
        t.[Name], 
        e.Subject, 
        r.MessageDate,  
        bosp.BackOfficeRecordID RaisersEdgeRecordID, 
        r.Opened, 
        convert(bit,case when count(s.PageID) > 0 then 1 else 0 end)  ClickedThrough
    from [dbo].[EmailTemplate] t 
    inner join [dbo].[Email] e on t.[id] = e.EmailTemplateID and t.ClientsID = @ClientSitesID
    left outer join [dbo].[EmailJob] j on j.EmailID = e.[ID] and j.[current] = 1
    left outer join [dbo].[Email_Recipient] er on er.emailid = j.EmailID
    left outer join [dbo].[EmailJob_Recipient] r on r.EmailRecipientID = er.[id] and r.[current] = 1
    inner join [dbo].[BackOfficeSystemPeople] bosp on bosp.BackOfficeRecordID = @RaisersEdgeRecordID and bosp.id = r.BackofficeSystemPeopleID and bosp.BackOfficeSystemid = 0
    left outer join [dbo].[Stats] s on e.[id] = s.SourceID and s.SourceTypeid = 1 and s.sourceREID = dbo.fnGetRecordIDFromPeopleID(r.BackofficeSystemPeopleID)
    group by e.[ID], t.[Name], e.Subject, r.MessageDate,  bosp.BackOfficeRecordID, r.Opened
    order by 1,5
)