declare @ownerId int = ; declare @ownerType varchar(3) = ; with campaignForms as ( select mstr_attrId as gtmCampaignId, child_attrId as workflowFormId from tbl_ORG_AttributeLinks oal with (nolock) where oal.mstr_AttrTypeId = 98 and oal.child_AttrTypeId = 89 ), leadsRaw as ( select fs.ownerId, fs.ownerType, fs.formId, fs.submissionId, fs.submissionTime, gtm.targetPartnerType, gtm.campaignType, gtm.campaignName, gtm.campaignId as gtmCampaignId, m.email sentToEmail, m.firstName sentToFirstName, m.lastName sentToLastName, m.fspro_userId as sentToContactId, rtrim( m.orgType ) sentToPartnerType, m.orgId sentToPartnerId, fs.companyName, fs.contactFirstName, fs.contactLastName, fs.contactEmail, fs.companyAddress1, fs.companyCity, fs.companyState, fs.companyZipCode, coalesce( fs.leadStatus, '?' ) leadStatus, fs.leadCompanyAction, fs.leadPartnerType, fs.leadPartnerId, fs.leadContactAction, fs.leadContactId, cr.recipientId, cr.campaignId emailCampaignId from tbl_WRK_FormSubmissions fs with (nolock) inner join campaignForms cf on cf.workflowFormId = fs.formId inner join tbl_GTM_Campaigns gtm with (nolock) on gtm.ownerId = fs.ownerId and gtm.ownerType = fs.ownerType and gtm.campaignId = cf.gtmCampaignId inner join tbl_EML_Campaign ec with (nolock) on ec.ownerId = fs.ownerId and ec.fsl_tablecode = fs.ownerType and ec.gtmCampaignId = cf.gtmCampaignId inner join tbl_EML_CampaignRecipients cr with (nolock) on cr.recipientId = fs.linkId and cr.campaignId = ec.campaignId left outer join tbl_FSpro_members m with (nolock) on m.ownerId = gtm.ownerId and m.fsl_tablecode = gtm.ownerType and m.fspro_userId = cr.fspro_userId where fs.linkType = 'EMR' and fs.ownerId = @ownerId and fs.ownerType = @ownerType ), allLeads as ( select l.*, opr.companyName sentToCompanyName, opr.address sentToAddress1, opr.city sentToCity, opr.state sentToState, opr.zipCode sentToZipCode, ter.territoryPath, ter.name as territoryName, ter.territoryId, leadopr.companyName leadCompanyName, leadopr.address leadCompanyAddress1, leadopr.city leadCompanyCity, leadopr.state leadCompanyState, leadopr.zipCode leadCompanyZipCode, leadct.firstName leadFirstName, leadct.lastName as leadLastName, leadct.email as leadEmail from leadsRaw l inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = l.sentToPartnerId inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode = opr.fsltablecode and ter.territoryId = opr.territoryId left outer join tbl_OPR_ClientOperators leadOPR with (nolock) on leadOPR.ownerId = @ownerId and leadOPR.fsltablecode = @ownerType and leadOPR.operatorId = l.leadPartnerId and 'OPR' = l.leadPartnerType left outer join tbl_fspro_members leadct with (nolock) on leadct.ownerId = @ownerId and leadct.fsl_tablecode = @ownerType and leadct.fspro_userId = l.leadContactId where l.sentToPartnerType = 'OPR' ), leads as ( select * from allLeads where 1 = 1 and territoryId in ( ) ) #forJSON( 'leads', attributes.datastore, 'submissionTime' )#