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 contractReferences as ( select r.assignedContractNbr, cr.confirmDate, cr.confirmUserId, cr.confirmed, cr.contractRef, r.description, r.endDate, mr.requestId as mappedRequestId, '#qmd_primaryPartner.partnerName#' as primaryPartnerName, 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 where cr.ownerId = and cr.ownerType = and cr.partnerId in ( ) and cr.partnertype = and mr.contractId is not null ), totalClaimCount as ( select 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.ownerId = and c.ownerType = and c.partnerId in ( ) and c.partnerType = 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 ), agreementRefs as ( select contractReferences.*, coalesce( totalClaimCount.claimCount, 0 ) as claimCount from contractReferences left outer join totalClaimCount on totalClaimCount.requestId = contractReferences.requestId ) #forJSON( 'agreementRefs', attributes.datastore, 'mappedRequestId, endDate desc, assignedContractNbr' )# select cr.contractRef, r.requestId, cr.createUserId, cr.createDate, cr.confirmed, cr.confirmDate, cr.confirmUserId from tbl_TPM_ContractReferences cr with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.requestId = cr.requestId and r.ownerId = cr.ownerId and r.ownerType = cr.ownerType where cr.partnerId = and cr.partnertype = and cr.ownerId = and cr.ownerType = order by contractRef select distinct r.requestId, r.endDate 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 where cr.partnerType = and cr.partnerId = and cr.ownerId = and cr.ownerType = and cr.requestId = and mr.contractId is not null order by r.endDate desc select r.startDate, r.endDate, r.assignedContractNbr, rp.partnerId, rp.partnerType, r.description, r.promoNbr from tbl_TPM_Requests r with (nolock) inner join tbl_TPM_Contracts rp with (nolock) on rp.contractId = r.contractId and rp.ownerId = r.ownerId and rp.ownertype = r.ownertype where r.requestId = and r.ownerId = and r.ownerType = RequestId = #requestId# has an error. select * from qmd_contractreferences order by mappingSeq, endDate desc, assignedContractNbr select assignedContractNbr, count(*) refCount from qmd_contractReferences group by assignedContractNbr having count(*) > 1 select distinct assignedContractNbr from qmd_contractReferences where contractRef =