declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @partnerId int = ; declare @orgType varchar(4) = ; declare @partnerType varchar(3) = ; with jobFunctions as ( select ojf.ownerId, ojf.ownerType, ojf.jobFunctionId, coalesce (nullif( ojf.jobFunctionName, '' ), 'Unknown') as jobFunctionName from tbl_OPR_JobFunctions ojf with (nolock) where ojf.ownerId = @ownerId and ojf.ownerType = @ownerType select jf.ownerId, jf.ownerType, cast( jf.selectionValue as int ) jobFunctionId, coalesce (nullif( jf.selectionLabel, '' ), 'Unknown') as jobFunctionName from tbl_FSPRO_FieldLookups jf with (nolock) where jf.ownerId = @ownerId and jf.ownerType = @ownerType and jf.fieldName = ), partnerContacts as ( select '#_partnerType#' as partnerType, m.orgId as partnerId, m.fspro_userId, m.firstName, m.lastName, m.title, m.email, m.phone, m.salutation, m.firstName + ' ' + m.lastName as fullName, case when m.preferredCommunication = 'P' then 'Phone' when m.preferredCommunication = 'T' then 'Text' when m.preferredCommunication = 'E' then 'Email' when m.preferredCommunication = 'C' then 'Cell' else coalesce (nullif( m.preferredCommunication, '' ), 'No Preference') end as preferredCommunication, m.preferredCommunication as prefCommCode, cast( case when opr.primaryContactId = m.fspro_userId then 'Yes' else 'No' end as varchar) primaryContactId, opr.primaryContactId as currPrimaryContactId, opr.companyName as partnerName, cast( case when cdr.primaryContactId = m.fspro_userId then 'Yes' else 'No' end as varchar) primaryContactId, cdr.primaryContactId as currPrimaryContactId, cdr.cdr_dstName as partnerName, nullif( m.caRelationshipRank, 0 ) caRelationshipRank, coalesce (nullif( rr.selectionLabel, '' ), 'Unknown') as relationshipRank, m.phoneExt, m.fax, m.cellPhone as cell, m.address1, m.address2, m.city, m.state, m.zipcode, m.countryId,m.lastUpdated,m.comments, cast( case when m.contactDecisionMaker = 'Y' then 'Yes' else 'No' end as varchar ) contactDecisionMaker , linkedin, cast( case when m.emailYesNo = 1 then 'Yes' else 'No' end as varchar ) emailYesNo, m.officeAddrSame, m.preferredLanguage, coalesce (nullif( lang.languageName, '' ), 'English') as preferredLanguageName , max( CASE WHEN crmp.urlType='facebook' THEN crmp.urlValue END) AS facebook, max( CASE WHEN crmp.urlType='instagram' THEN crmp.urlValue END) AS instagram, max( CASE WHEN crmp.urlType='youtube' THEN crmp.urlValue END) AS youtube, max( CASE WHEN crmp.urlType='tiktok' THEN crmp.urlValue END) AS tiktok, max( CASE WHEN crmp.urlType='twitter' THEN crmp.urlValue END) AS twitter, jf.jobFunctionId, jf.jobFunctionName, m.failedEmailDate, case when m.failedEmailDate is not null then 'Y' else 'N' end knownBadEmail from tbl_FSPro_members m with (nolock) left outer join tbl_FSPRO_FieldLookups rr with (nolock) on rr.ownerId = m.ownerId and rr.ownerType = m.fsl_tablecode and rr.fieldName = 'RelationshipRank' and cast( rr.selectionValue as int ) = nullif( m.caRelationshipRank, 0 ) left outer join jobFunctions jf with (nolock) on jf.ownerId = m.ownerId and jf.ownerType = m.fsl_tablecode and jf.jobFunctionId = m.caJobFunction left outer join tbl_CRM_PartnerURLs crmp with (nolock) on crmp.ownerId = m.ownerId and crmp.ownerType = m.fsl_tablecode and crmp.partnerId = m.fspro_userId and crmp.partnerType = 'USR' left outer join tbl_CRM_Languages lang with (nolock) on lang.languageCode = m.preferredLanguage left outer join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = m.orgId and opr.ownerId = m.ownerId and opr.FSLTableCode = m.fsl_tableCode left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = m.orgId and cdr.ownerId = m.ownerId and cdr.FSL_TableCode = m.fsl_tableCode where m.ownerId = @ownerId and m.fsl_tablecode = @ownerType and m.orgType = @orgType and case when @partnerId = -1 then 1 when m.orgId = @partnerId then 1 else 0 end = 1 Group by m.orgId, m.ownerId, m.fsl_tableCode, m.fspro_userId, m.firstName, m.lastName, m.title, m.email, m.phone, m.salutation, preferredCommunication, m.preferredCommunication, primaryContactId, opr.primaryContactId, opr.companyName, primaryContactId, cdr.primaryContactId, cdr.cdr_dstName, m.caRelationshipRank, rr.selectionLabel, m.phoneExt, m.fax, m.cellPhone, m.address1, m.address2, m.city, m.state, m.zipcode, m.countryId, m.lastUpdated,m.comments,contactDecisionMaker,linkedin,emailYesNo,m.officeAddrSame, m.preferredLanguage, lang.languageName, jf.jobFunctionId, jf.jobFunctionName, m.failedEmailDate ) #forJSON( 'partnerContacts', attributes.datastore, 'firstName' )#