USP_EMAILLINKS_GETTEMPLATELINKS

CREATE procedure dbo.USP_EMAILLINKS_GETTEMPLATELINKS
(
    @EMAILTEMPLATEID int 
)
as
begin
    --get page links

    SELECT
        e.[EmailTemplateID], 
        el.[PID], p.PageName,
        el.[URL], 
        count(s.statsid) hits,
        count(distinct s.emailjobrecipientid) visitors,
        el.[DocumentID]
    FROM [EmailLink] el
    INNER JOIN Email e ON e.id = el.EmailID
    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 
    WHERE 
        e.EmailTemplateID = @EMAILTEMPLATEID
        and el.[PID] is not null
    GROUP BY e.[EmailTemplateID], el.[PID], p.PageName, el.[URL], el.[DocumentID]

    UNION

    --get external links

    SELECT    
        e.[EmailTemplateID], 
        el.[PID], 
        p.PageName,
        el.[URL], 
        count(s.statsid) hits, 
        count(distinct s.emailjobrecipientid) visitors,
        el.[DocumentID]
    FROM [EmailLink] el
    INNER JOIN Email e ON e.id = el.EmailID
    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 
    WHERE 
        e.EmailTemplateID = @EMAILTEMPLATEID
        and el.[URL] is not null
    GROUP BY e.[EmailTemplateID], el.[PID], PageName, el.[URL], el.[DocumentID]

    UNION

    --get document links

    SELECT    
        e.[EmailTemplateID], 
        el.[PID], 
        p.PageName,
        el.[URL], 
        count(s.statsid) hits, 
        count(distinct s.emailjobrecipientid) visitors,
        el.[DocumentID]
    FROM [EmailLink] el
    INNER JOIN Email e ON e.id = el.EmailID
    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 
    WHERE 
        e.EmailTemplateID = @EMAILTEMPLATEID
        and el.[DocumentID] is not null
    GROUP BY e.[EmailTemplateID], el.[PID], PageName, el.[URL], el.[DocumentID]

end