select b.cdr_recordId from tbl_CDR_Distributors b with (nolock) inner join tbl_DST_Distributors db with (nolock) on db.dstId = b.fsl_dstId inner join tbl_CDR_Distributors p with (nolock) on p.fsl_dstId = db.dstParentCo and p.ownerId = b.ownerId and p.fsl_tablecode = b.fsl_tablecode where p.ownerId = and p.fsl_tablecode = and p.cdr_recordId = and b.cdr_dstCompanyType = 'B' with claimSummary as ( select cc.contractNbr, cc.requestId, count(*) claimCount from tbl_TPM_Claims c with (nolock) inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.claimId = c.claimId and cc.ownerId = c.ownerId and cc.ownerType = c.ownerType where c.partnerId in ( ) and c.partnerType = and c.ownerId = and c.ownerType = and exists ( select * from tbl_TPM_Requests r with (nolock) where r.requestId = cc.requestId and r.ownerId = cc.ownerId and r.ownerType = cc.ownerType ) group by cc.contractNbr, cc.requestId ), partners as ( select 'OPR' as partnerType, operatorId as partnerId, ownerId, fsltablecode as ownerType, companyName as partnerName from tbl_OPR_ClientOperators with (nolock) union all select 'CDR' as partnerType, cdr_recordId as partnerId, ownerId, fsl_tablecode as ownerType, cdr_dstName as partnerName from tbl_CDR_Distributors with (nolock) ), contractMapping as ( select r.assignedContractNbr, cr.confirmDate, cr.confirmUserId, cr.confirmed, cr.contractRef, r.description, r.endDate, mr.requestId as mappedRequestId, p.partnerName, r.promoNbr, r.requestId, r.startDate, case when mr.requestId = r.requestId then 'E' else 'I' end as type from tbl_TPM_ContractReferences cr with (nolock) inner join tbl_TPM_Requests mr with (nolock) on mr.requestId = cr.requestId and mr.ownerId = cr.ownerId and mr.ownerType = cr.ownerType and nullif( mr.assignedContractNbr, '' ) is not null inner join tbl_TPM_Requests r with (nolock) on r.assignedContractNbr = mr.assignedContractNbr and r.ownerId = mr.ownerId and r.ownerType = mr.ownerType inner join tbl_TPM_Contracts rp with (nolock) on rp.contractId = r.contractId and rp.ownerId = r.ownerId and rp.ownertype = r.ownertype inner join partners p on p.ownerId = rp.ownerId and p.ownerType = rp.ownerType and p.partnerId = rp.partnerId and p.partnerType = rp.partnerType where cr.partnerId = and cr.partnertype = and cr.ownerId = and cr.ownerType = and mr.contractId is not null ), agreementRefs as ( select contractMapping.*, coalesce( claimSummary.claimCount, 0 ) claimCount from contractMapping left outer join claimSummary on claimSummary.requestId = contractMapping.requestId and claimSummary.contractNbr = contractMapping.contractRef ) #forJSON( 'agreementRefs', attributes.datastore, 'assignedContractNbr, startDate desc' )#