declare @ownerId int = ; declare @ownerType varchar(3) = ; with contracts as ( select ct.ownerId, ct.ownerType, ct.partnerId, ct.partnerType, ct.contractId, ct.assignedContractNbr, ct.contractName, min( r.startDate ) fromDate, max( r.endDate ) toDate from tbl_TPM_Contracts ct with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = ct.ownerId and r.ownerType = ct.ownerType and r.contractId = ct.contractId and r.status = 'APPROVED' where ct.ownerId = @ownerId and ct.ownerType = @ownerType group by ct.ownerId, ct.ownerType, ct.partnerId, ct.partnerType, ct.contractId, ct.assignedContractNbr, ct.contractName ), contractsWithPartner as ( select ct.*, opr.companyName as partnerName from contracts ct inner join tbl_OPR_CLientOperators opr with (nolock) on opr.operatorId = ct.partnerId and opr.ownerId = ct.ownerId and opr.fsltablecode = ct.ownerType where ct.partnerType = 'OPR' union all select ct.*, cdr.cdr_dstName as partnerName from contracts ct inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = ct.partnerId and cdr.ownerId = ct.ownerId and cdr.fsl_tablecode = ct.ownerType where ct.partnerType = 'CDR' ), formattedAgreements as ( select *, partnerName + ': ' + assignedContractNbr + '-' + contractName + ' (' + format( fromDate, 'MM/dd/yyyy' ) + '-' + format( toDate, 'MM/dd/yyyy' ) + ')' as agreementDisplay from contractsWithPartner ) #forJSON( 'formattedAgreements', attributes.datastore, 'agreementDisplay' )#