fnGetEmailStatsByEmail

CREATE   function [dbo].[fnGetEmailStatsByEmail]
    (
        @ClientSitesID int, 
        @eMailID 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 and e.ID = @eMailID
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
left outer join [dbo].[BackOfficeSystemPeople] bosp on 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 = bosp.BackOfficeRecordID
group by e.[ID], t.[Name], e.Subject, r.MessageDate,  bosp.BackOfficeRecordID, r.Opened
order by 1,5
)