spGetEmailProjectLinks

CREATE    PROCEDURE [dbo].[spGetEmailProjectLinks]
(
@EmailProjectID int,
@ProjectAppealID int,
@AppealListID int
) AS
BEGIN
SELECT p.ID as PID, p.PageName,
        el.[URL], count(s.statsid) hits, count(distinct s.emailjobrecipientid) visitors,
        el.[DocumentID]
FROM [EmailLink] el
LEFT OUTER JOIN SitePages p on p.id = el.pid
LEFT OUTER JOIN Stats s on s.pageid = el.pid and s.sourceid = el.emailid and s.sourcetypeid = 1
INNER JOIN Email e on el.EmailID = e.[ID]
INNER JOIN emailprojectappeal_ListEmail epale on e.ProjectAppealListEmailID = epale.[ID]
INNER JOIN EmailProjectAppeal_EmailList epael on epale.[EmailProjectAppealListID] = epael.[ID] 
INNER JOIN EmailProjectAppeal epa on epael.EmailProjectAppealID = epa.[ID] 
INNER JOIN EmailProject ep on epa.[EmailProjectID] = ep.[ID]
WHERE (ep.[ID] = @EmailProjectID OR @EmailProjectID = 0) AND (epa.[ID] = @ProjectAppealID OR @ProjectAppealID = 0) 
   AND (epael.[ID] = @AppealListID OR @AppealListID = 0) AND epael.AppealListType = 1 
and el.[PID] is not null
GROUP BY p.ID, p.PageName, el.[URL], el.[DocumentID]

UNION

SELECT    p.ID, p.PageName,
        el.[URL], count(s.statsid) hits, count(distinct s.emailjobrecipientid) visitors,
        el.[DocumentID]
FROM [EmailLink] el
LEFT OUTER JOIN SitePages p on p.id = el.pid
LEFT OUTER JOIN Stats s on s.URL = el.URL and s.sourceid = el.emailid  and s.sourcetypeid = 1
INNER JOIN Email e on el.EmailID = e.[ID]
INNER JOIN emailprojectappeal_ListEmail epale on e.ProjectAppealListEmailID = epale.[ID]
INNER JOIN EmailProjectAppeal_EmailList epael on epale.[EmailProjectAppealListID] = epael.[ID] 
INNER JOIN EmailProjectAppeal epa on epael.EmailProjectAppealID = epa.[ID] 
INNER JOIN EmailProject ep on epa.[EmailProjectID] = ep.[ID]
WHERE (ep.[ID] = @EmailProjectID OR @EmailProjectID = 0) AND (epa.[ID] = @ProjectAppealID OR @ProjectAppealID = 0) 
   AND (epael.[ID] = @AppealListID OR @AppealListID = 0) AND epael.AppealListType = 1 
and el.[URL] is not null
GROUP BY p.ID, PageName, el.[URL], el.[DocumentID]

UNION

SELECT    p.ID, p.PageName,
        el.[URL], count(s.statsid) hits, count(distinct s.emailjobrecipientid) visitors,
        el.[DocumentID]
FROM [EmailLink] el
LEFT OUTER JOIN SitePages p on p.id = el.pid
LEFT OUTER JOIN Stats s on s.DocumentId = el.DocumentID and s.sourceid = el.emailid  and s.sourcetypeid = 0
INNER JOIN Email e on el.EmailID = e.[ID]
INNER JOIN emailprojectappeal_ListEmail epale on e.ProjectAppealListEmailID = epale.[ID]
INNER JOIN EmailProjectAppeal_EmailList epael on epale.[EmailProjectAppealListID] = epael.[ID] 
INNER JOIN EmailProjectAppeal epa on epael.EmailProjectAppealID = epa.[ID] 
INNER JOIN EmailProject ep on epa.[EmailProjectID] = ep.[ID]
WHERE (ep.[ID] = @EmailProjectID OR @EmailProjectID = 0) AND (epa.[ID] = @ProjectAppealID OR @ProjectAppealID = 0) 
   AND (epael.[ID] = @AppealListID OR @AppealListID = 0) AND epael.AppealListType = 1 
and el.[DocumentID] is not null
GROUP BY p.ID, PageName, el.[URL], el.[DocumentID]
END