clicksByDay log
{ts '2025-07-01 09:30:23'} SQL
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

)





, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by daynum
		
		
		 offset 0 rows fetch next 20 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 387
-- BRO
-- 23048

{ts '2025-07-01 09:31:02'} SQL
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

)





, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by daynum
		
		
		 offset 0 rows fetch next 20 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 387
-- BRO
-- 23048

{ts '2025-07-01 09:31:39'} SQL
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

)





, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by daynum
		
		
		 offset 0 rows fetch next 20 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 387
-- BRO
-- 23048

{ts '2025-07-01 09:32:18'} SQL
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

)





, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by daynum
		
		
		 offset 0 rows fetch next 20 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 387
-- BRO
-- 23048

{ts '2025-07-01 09:33:05'} SQL
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

)





, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by daynum
		
		
		 offset 0 rows fetch next 20 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 387
-- BRO
-- 23048

{ts '2025-07-01 09:34:24'} SQL
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

)





, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by daynum
		
		
		 offset 0 rows fetch next 20 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 387
-- BRO
-- 23048

{ts '2025-07-01 09:38:46'} SQL
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

)





, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by daynum
		
		
		 offset 0 rows fetch next 20 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 387
-- BRO
-- 23048