declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @cdr_recordId int = ; drop table if exists #_mfrCodeTable#; drop table if exists #_mfrStockingTable#; with mfrAliasesRaw as ( SELECT ala.objectId as mfr_id, ala.alias mfrCode, row_number() over ( partition by objectId order by alias ) as aliasRank FROM tbl_IMPORT_Aliases ala with (nolock) WHERE ala.ownerid = @ownerId AND ala.ownerType = @ownerType and ala.objectType = 'MFRID' ) select mfr_id, mfrCode into #_mfrCodeTable# from mfrAliasesRaw where aliasRank = 1 alter table #_mfrCodeTable# add constraint pk_#_mfrCodeTable# primary key ( mfr_id ); SELECT s.mfrId as mfr_id, SUM(CASE WHEN O.StockingStatus = 'Y' THEN 1 ELSE 0 END) as openCnt, SUM(CASE WHEN O.StockingStatus = 'N' THEN 1 ELSE 0 END) as closedCnt, SUM(CASE WHEN O.StockingStatus = 'S' THEN 1 ELSE 0 END) as specialCnt into #_mfrStockingTable# FROM tbl_CDR_Stocking O with (nolock) INNER JOIN dbo.tbl_PRD_SKUs S with (nolock) ON S.Skuid = O.SkuId and s.ownerId = o.ownerId and s.fsl_tablecode = o.ownerType and s.mfrId > 0 and s.crmActive = 'Y' WHERE O.OwnerId = @ownerId AND O.OwnerType = @ownerType AND O.StockingStatus IN ('Y','N','S') AND O.cdr_recordId = @cdr_recordId group by s.mfrId; alter table #_mfrStockingTable# alter column mfr_id int not null; alter table #_mfrStockingTable# add constraint pk_#_mfrStockingTable# primary key ( mfr_id ); with data as ( select mcdr.focusAccount, mfr.mfr_name, mfr.mfr_id, mfrCodes.mfrCode, pplt.principalTerritoryId, ppl.principalId, case when ppl.priority = '*' then 'A+' else ppl.priority end as priority, case when ppl.priority = '*' then 1 when ppl.priority = 'A' then 2 when ppl.priority = 'B' then 3 when ppl.priority = 'C' then 4 when ppl.priority = 'D' then 5 else 6 end priorityRank, ppl.sortRank, mcdr.mfrCustNbr -- ,cdr.cdr_dstName, cdr.cdr_recordId, cdr.cdr_dstAddress1, cdr.cdr_dstAddress2, cdr.cdr_dstcity, cdr.cdr_dststate, cdr.cdr_dstzip, , mcdr.cdrContactId, pplt.hireStatus, coalesce( ss.openCnt, 0 ) openCnt, coalesce( ss.closedCnt, 0 ) closedCnt, coalesce( ss.specialCnt, 0 ) specialCnt from tbl_BRO_principals ppl with (nolock) inner join tblManufacturers mfr with (nolock) on mfr.mfr_id = ppl.mfrId inner join tbl_BRO_PrincipalTerritories pplt with (nolock) on pplt.ownerId = ppl.ownerId and pplt.ownerType = ppl.ownerType and pplt.principalId = ppl.principalId inner join tbl_BRO_PrincipalDistributors mcdr with (nolock) on mcdr.principalTerritoryId = pplt.principalTerritoryId and mcdr.ownerId = pplt.ownerId and mcdr.ownerType = pplt.ownerType inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = mcdr.cdrRecordId and cdr.ownerId = mcdr.ownerId and cdr.fsl_tablecode = mcdr.ownerType left outer join #_mfrCodeTable# mfrCodes on mfrCodes.mfr_id = mfr.mfr_id left outer join #_mfrStockingTable# ss on ss.mfr_id = mfr.mfr_id where ppl.ownerId = @ownerId and ppl.ownerType = @ownerType and cdr.cdr_recordId = @cdr_recordId and pplt.hireStatus in ( 'A' ) ) #forJSON( 'data', attributes.datastore, 'priorityRank,sortRank,mfr_name' )# drop table if exists #_mfrCodeTable#; drop table if exists #_mfrStockingTable#;