declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @campaignId int = ; with clickCounts as ( select h.hyperlink, count(*) as clickCount from tbl_EML_CampaignRecipients r with (nolock) inner join tbl_EML_CampaignHyperlinks h with (nolock) on h.campaignId = r.campaignId inner join tbl_EML_RecipientClicks c with (nolock) on c.recipientId = r.recipientId and c.clickDestination = h.hyperlink where r.campaignId = @campaignId and c.ignoreClick = 'N' group by h.hyperlink ), responseCounts as ( select l.hyperlink, count(*) responseCount from tbl_EML_Campaign c with (nolock) inner join tbl_EML_CampaignHyperlinks l with (nolock) on l.campaignId = c.campaignId inner join tbl_EML_CampaignRecipients r with (nolock) on r.campaignId = l.campaignId inner join tbl_WRK_FormSubmissions s with (nolock) on s.ownerId = c.ownerId and s.ownerType = c.fsl_tablecode and s.formId = l.formId and s.linkType ='EMR' and s.linkId = r.recipientId where c.campaignId = @campaignId and c.ownerId = @ownerId and c.fsl_tablecode = @ownerType group by l.hyperlink ), formCounts as ( select l.hyperlink, count(l.formId) formCount from tbl_EML_Campaign c with (nolock) inner join tbl_EML_CampaignHyperlinks l with (nolock) on l.campaignId = c.campaignId inner join tbl_EML_CampaignRecipients r with (nolock) on r.campaignId = l.campaignId inner join tbl_WRK_FormSubmissions s with (nolock) on s.ownerId = c.ownerId and s.ownerType = c.fsl_tablecode and s.formId = l.formId and s.linkType ='EMR' and s.linkId = r.recipientId where c.campaignId = @campaignId and c.ownerId = @ownerId and c.fsl_tablecode = @ownerType group by l.hyperlink ), data as ( select e.hyperlink, e.hyperlinkSubjectType, e.hyperlinkSubjectId, e.insertDate, e.hyperlinkId, e.missingLink, c.clickCount, case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id, e.formId, case when e.formId > 0 then 'Y' else 'N' end as formLink, r.responseCount, f.formCount from tbl_EML_CampaignHyperlinks e with (nolock) left outer join clickCounts c on c.hyperlink = e.hyperlink left outer join responseCounts r on r.hyperlink = e.hyperlink left outer join formCounts f on f.hyperlink = e.hyperlink where e.campaignId = @campaignId ) #forJSON( 'data', attributes.datastore, 'hyperlink' )#