declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @campaignId int = ; declare @runDate datetime = ( select runDate from tbl_EML_Campaign with (nolock) where campaignId = @campaignId and ownerId = @ownerId and fsl_tablecode = @ownerType ); with detailClicksByDay as ( select 1 clicks, case when DATEDIFF( "d", r.timeSubmitted, c.DateTimeClicked ) < 7 then DATEDIFF( "d", r.timeSubmitted, c.DateTimeClicked ) else 7 end as dayNum 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 inner join tbl_EML_Campaign cpm with (nolock) on cpm.CampaignID = r.CampaignID and cpm.OwnerID = @ownerId and cpm.FSL_TableCode = @ownerType where r.campaignId = @campaignId and c.ignoreClick = 'N' union all select 0 clicks, 0 dayNum union all select 0 clicks, 1 dayNum union all select 0 clicks, 2 dayNum union all select 0 clicks, 3 dayNum union all select 0 clicks, 4 dayNum union all select 0 clicks, 5 dayNum union all select 0 clicks, 6 dayNum union all select 0 clicks, 7 dayNum ), clicksByDay as ( select dayNum, sum( clicks ) clicks from detailClicksByDay group by dayNum ), clicksByDayFormatted as ( select *, case when @runDate is not null then format( dateadd( day, daynum, @runDate ), 'MM/dd' ) else cast( dayNum as varchar ) end + case when dayNum = 7 then '+' else '' end as [day] from clicksByDay ), data as ( select * from clicksByDayFormatted ) #forJSON( 'data', attributes.datastore, 'daynum' )#