declare @cdr_recordId int = ; declare @ownerId int = ; declare @ownertype varchar(3) = ; with cdr as ( select cdr_recordId as distributorId, cdr_recordId, cast( floor( 10191817 * sqrt( cdr.cdr_recordid * log10( cdr.cdr_recordid ))) as bigint ) as cdr_recordIdTk, cast( floor( 10191817 * sqrt( cdr.cdr_recordid * log10( cdr.cdr_recordid ))) as bigint ) as distributorIdTk, cdr_dstName as companyName, cdr_dstAddress1 as address, cdr_dstAddress2 as address2, rtrim(cdr_dstState) as state, cdr_dstCity as city, cdr_dstZip as zipCode, cdr_dstcountryId as countryId, cdr_dstPhone as phone, cdr_dstPhone, cdr_dstcompanytype as dstcompanytype, cdr_dstname, cdr_dstcode, cdr_dstcity, rtrim(cdr_dstState) cdr_dststate, focusAccount, cdr_priority, classificationId, mfr_bsr_id, cdr_dstPath, cdr_dstaddress1, cdr_dstaddress2, cdr_dstzip, fsl_dstId, recordSource, cdr_territoryid, cdr_dstcountryId, gs1_gln, primaryContactId, fmId, fmSyncTime, fmDatabase, comments from tbl_CDR_Distributors cdr with (nolock) where ownerid = @ownerid and fsl_tableCode = @ownerType and cdr_recordId = @cdr_recordId ), cdrWithParent as ( select cdr.*, cdrp.cdr_recordid as parentCdrRecordId, cdrp.cdr_dstname as parentCdrDstName from cdr left outer join tbl_DST_Distributors dst with (nolock) on dst.dstid = cdr.fsl_dstId left outer join tbl_DST_Distributors dstpr with (nolock) on dst.dstparentco = dstpr.dstid left outer join tbl_CDR_Distributors cdrp with (nolock) on cdrp.fsl_dstId = dstpr.dstid and cdrp.fsl_tablecode = @ownerType and cdrp.ownerid = @ownerId -- and cdrp.fsl_mapPriority = 1 ), accountOwner as ( select cdr.*, mem.firstname + ' ' + mem.lastname as accountOwnerFullName from cdrWithParent cdr left outer join tbl_FSPro_Members mem on cdr.mfr_bsr_id = mem.FSPro_UserId and mem.fsl_tableCode in ( 'MFR', 'BRO') and mem.ownerId > 0 ), buyingGroupsRaw as ( select byg_raw.bgId, byg_raw.BgName, byg_raw.cdr_recordId, byg_raw.cdr_dstName from ( select byg.uniqueId as bgId, byg.name as BgName, cdr.cdr_recordId, cdr.cdr_dstName, row_number() over ( partition by cdr_recordId order by cdr.cdr_recordId, afl.affiliationDate desc, byg.name ) as seq from tbl_BYG_BuyingGroups byg with (nolock) inner join tbl_CRM_Affiliations afl with (nolock) on afl.ownerId= 66 and afl.ownerType = 'MSC' and afl.orgType = 'BYG' and afl.partnerType = 'DST' and afl.affiliationStatus = 'A' and afl.orgId = byg.uniqueId inner join tbl_CDR_Distributors cdr with (nolock) on cdr.fsl_dstId = afl.partnerId where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType and cdr.cdr_recordId = @cdr_recordId ) as byg_raw where seq = 1 ), dataRaw as ( select c.cdr_recordId, c.cdr_recordIdTk, c.distributorId, c.companyName, c.address, c.address2, c.state, c.city, c.zipCode, c.countryId, c.phone, c.comments, c.dstcompanytype, c.parentCdrRecordId, c.parentCdrDstName, c.cdr_dstname, c.cdr_dstcode, c.cdr_dstcity, c.cdr_dststate, c.focusAccount, c.cdr_priority, c.classificationId, c.mfr_bsr_id, c.cdr_dstPath, c.cdr_dstaddress1, c.cdr_dstaddress2, c.cdr_dstzip, c.fsl_dstId, c.recordSource, c.cdr_territoryid, c.cdr_dstcountryId, c.gs1_gln, c.primaryContactId, c.fmId, c.fmSyncTime, c.fmDatabase, r.bgId, r.bgName, c.accountOwnerFullName from accountOwner c left outer join buyingGroupsRaw r on c.cdr_recordId = r.cdr_recordId ), data as ( select * from dataRaw ) #forJSON( 'data', attributes.datastore, 'cdr_recordId' )#