spGetRawEmailStats

CREATE procedure [dbo].[spGetRawEmailStats](@ClientSitesID int, @FromDate datetime)
as
BEGIN
set nocount on

select  e.[ID] EmailID, 
    e.[Name] EmailName, 
    t.[Name] TemplateName, 
    e.Subject, 
    r.MessageDate,  
    e.[Type],
    isnull([dbo].[fnGetRecordIDFromPeopleID](r.BackOfficeSystemPeopleID),
    dbo.fnGetRaisersEdgeRecordIDFromLinkedUserID(r.UserID)) RaisersEdgeID, 
    r.Opened, 
    ls.PageID
    from dbo.EmailJob_Recipient r 
    full join dbo.email e on (r.EmailID = e.[ID])
    left join dbo.EmailTemplate t on ((e.EmailTemplateID = t.[ID]) and (t.ClientsID = @ClientSitesID))
    left join dbo.clientusers u on dbo.fnGetRaisersEdgeRecordIDFromLinkedUserID(r.UserID) = isnull([dbo].[fnGetRecordIDFromPeopleID](r.BackOfficeSystemPeopleID),
    dbo.fnGetRaisersEdgeRecordIDFromLinkedUserID(r.UserID))
    left join dbo.[stats] ls on (e.ID = ls.SourceID and ls.SourceTypeid = 1 and ls.userid = u.[ID])
    where r.MessageDate> = isnull(@FromDate,r.MessageDate)
END