update b set b.fseCreated = dateadd( second, created, '1970-01-01' ) from tbl_IMPORT_SendGridBounces b with (nolock) where b.ownerId = 78 and b.ownerType = 'MSC' and b.eai_batchId = select min( fseCreated) startDate, max( fseCreated ) endDate from tbl_IMPORT_SendGridBounces b with (nolock) where b.ownerId = 78 and b.ownerType = 'MSC' and b.eai_batchId = and b.created > 0

Bounces From #qmd_summary.startDate# to #qmd_summary.endDate#

drop table if exists [#_emailRecipientsTable#]; create table [#_emailRecipientsTable#] ( ownerType varchar(3) not null, ownerId int not null, email varchar(75) not null, fspro_userId int not null, failedEmail varchar(75), failedEmailDate datetime, failedEmailReason varchar(max)); insert into [#_emailRecipientsTable#] ( ownerType, ownerId, email, fspro_userId ) select fsl_tablecode ownerType, ownerId, email, fspro_userId from tbl_fspro_members where nullif( email, '' ) is not null and ownerId > 0; alter table [#_emailRecipientsTable#] add constraint [pk_#_emailRecipientsTable#] primary key ( ownerType, ownerId, email, fspro_userId ); drop table if exists [#_bouncesTable#]; create table [#_bouncesTable#] ( ownerType varchar(3) not null, ownerId int not null, email varchar(75) not null, emailId int not null, failedEmailDate datetime, failedEmailReason varchar(max) ) alter table [#_bouncesTable#] add constraint [pk_#_bouncesTable#] primary key ( ownertype, ownerId, emailId, email ); drop table if exists [#_emailTable#]; create table [#_emailTable#] ( ownerType varchar(3) not null, ownerId int not null, email varchar(4000) not null, emailId int not null, emailTryDate datetime, failedEmailDate datetime, failedEmailReason varchar(max) ) insert into [#_emailTable#] ( ownerType, ownerId, email, emailId, emailTryDate ) select ownertype, ownerId, emailTo, emailId, emailTryDate from tbl_WRK_Emails with (nolock) where lastSendMethod = 'SendGrid' and emailTryDate < and emailTryDate >= dateadd( day, -15, ); alter table [#_emailTable#] add constraint [pk_#_emailTable#] primary key ( email, emailId ); select eai_lineNumber, eai_importId, status, reason, email, fseCreated from tbl_IMPORT_SendGridBounces with (nolock) where ownerId = 78 and ownerType = 'MSC' and nullif( email, '' ) is not null and eai_batchId = declare @reason varchar(max) = ; declare @bounceDate datetime = ; declare @email varchar(75) = ; insert into [#_bouncesTable#] ( ownerId, ownerType, email, emailId, failedEmailDate, failedEmailReason ) select e.ownerId, e.ownerType, @email as email, e.emailId, @bounceDate, @reason from [#_emailTable#] e with (nolock) where email like '%'+@email+'%' and not exists ( select * from [#_bouncesTable#] b where b.ownerId = e.ownerId and b.ownerType = e.ownerType and b.email = @email and b.emailId = e.emailId ) update r set r.failedEmailDate = b.failedEmailDate, r.failedEmailReason = b.failedEmailReason from [#_bouncesTable#] b inner join [#_emailRecipientsTable#] r on r.ownerId = b.ownerId and r.ownertype = b.ownerType and r.email = b.email; delete from [#_emailRecipientsTable#] where failedEmailDate is null; update m set m.failedEmailDate = r.failedEmailDate, m.failedEmailReason = r.failedEmailReason, m.failedEmail = r.email from tbl_fspro_members m inner join [#_emailRecipientsTable#] r on r.ownerId = m.ownerId and r.ownertype = m.fsl_tablecode and r.fspro_userId = m.fspro_userId; drop table if exists [#_bounceSummaryTable#] select ownerType, ownerId, emailId, string_agg( email, ',' ) failedEmail, max( failedEmailDate ) failedEmailDate into [#_bounceSummaryTable#] from [#_bouncesTable#] group by ownerType, ownerId, emailId; alter table [#_bounceSummaryTable#] add constraint [pk_#_bounceSummaryTable#] primary key (ownerType, ownerId, emailId ); update e set e.failedEmail = bs.failedEmail, e.failedEmailDate = bs.failedEmailDate from tbl_WRK_Emails e inner join [#_bounceSummaryTable#] bs on bs.ownerId = e.ownerId and bs.ownerType = e.ownerType and bs.emailId = e.emailId drop table if exists [#_bouncesTable#]; drop table if exists [#_bounceSummaryTable#]; drop table if exists [#_emailRecipientsTable#]; drop table if exists [#_emailTable#]; select ( select fsl_tablecode, ownerId, fspro_userId, orgId, orgType, email, staffMember, loginEnabled from tbl_fspro_members with (nolock) where email = for json auto ) as json select ( select emailDate, emailFrom, emailSubject, emailId, ownerId, ownerType from tbl_WRK_Emails with (nolock) where emailTo like and lastSendMethod = 'SendGrid' and emailTryDate < order by emailDate for json auto ) as json update tbl_WRK_Emails set failedEmail = coalesce( failedEmail, '' ) + , failedEmailDate = where emailTo = and lastSendMethod = 'SendGrid' and emailTryDate < and emailTryDate >= dateadd( day, -15, ) and coalesce( failedEmail, '' ) not like declare @reason varchar(max) = ; declare @bounceDate datetime = ; declare @email varchar(75) = ; insert into [#_bouncTable#] ( ownerId, ownerType, email, emailId, failedEmailDate, failedEmailReason ) select ownerId, ownerType, emailTo, emailId, @bounceDate, @reason from tbl_WRK_Emails with (nolock) where emailTo = @emailTo and lastSendMethod = 'SendGrid' and emailTryDate < @bounceDate and emailTryDate >= @bounceDate ) and not exists ( select * from [#_bouncesTable#] b where b.ownerId = e.ownerId and b.ownerType = e.ownerType and b.email = e.emailTo and b.emailId = e.emailId ) update tbl_IMPORT_SendGridBounces set fseSendCount = , fseFirstSend = , fseLastSend = , fseFSPro_MemberRecords = , fseEmails = where ownerId = 78 and ownerType = 'MSC' and eai_batchId = and eai_lineNumber = and eai_importId = select status, reason, email, fseCreated, fseSendCount, fseFirstSend, fseLastSend, fseFspro_memberRecords, fseEmails from tbl_IMPORT_SendGridBounces where ownerId = 78 and ownerType = 'MSC' and eai_batchId = -- and fseFspro_memberRecords is not null or and fseSendCount > 0 select status, count(*) instances from qmd_bounces group by status order by instances desc --->