fnGetEmailStatsAll

CREATE function [dbo].[fnGetEmailStatsAll] (@ClientSitesID int)
returns table as
return (
    select top 100 percent
    e.[ID] EmailID, 
    t.[Name], 
    e.Subject, 
    r.MessageDate,  
    bosp.BackOfficeSystemID RaisersEdgeRecordID, 
    r.Opened,
    er.ClickedThrough
    from [dbo].[Email_Recipient] er
    inner join [dbo].[Email] e on e.[id] = er.EmailID and er.backofficesystempeopleid > 0
    inner join [dbo].[EmailTemplate] t on t.[id] = e.EmailTemplateID and t.ClientsID = @ClientSitesID
    inner join [dbo].[EmailJob_Recipient] r on r.EmailRecipientID = er.[id] and r.[current] = 1
    left outer join [dbo].[BackOfficeSystemPeople] bosp on r.BackofficeSystemPeopleID = bosp.id and bosp.BackOfficeSystemID = 0
    order by 1,5
)