declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @partnerId int = ; declare @partnerType varchar(3) = ; with partnerAgreements as ( select ct.ownerId, ct.ownerType, ct.contractId, ct.contractName, ct.assignedContractNbr, ct.partnerId, ct.partnerType, r.payableTo, case when r.volumeGenerator = 'Y' then 1 else 0 end volumeGenerator, count( distinct r.requestId ) revisionCount, min( r.startDate ) startDate, max( r.endDate ) endDate, count( distinct cc.claimId ) claimCount, max( c.claimPeriodEnd ) lastClaimPeriodEnd from tbl_TPM_Contracts ct with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.contractId = ct.contractId and r.ownerId = ct.ownerId and r.ownerType = ct.ownerType and r.ownerId = ct.ownerId and r.ownerType = ct.ownerType left outer join tbl_TPM_ClaimContracts cc with (nolock) on cc.ownerId = r.ownerId and cc.ownerType = r.ownerType and cc.requestId = r.requestId left outer join tbl_TPM_Claims c with (nolock) on c.ownerId = cc.ownerId and c.ownerType = cc.ownerType and c.claimId = cc.claimId where ct.ownerId = @ownerId and ct.ownerType = @ownerType and ct.partnerType = @partnerType and ct.partnerId = @partnerId group by ct.ownerId, ct.ownerType, ct.contractId, ct.assignedContractNbr, ct.contractName, ct.partnerId, ct.partnerType, r.payableTo, r.volumeGenerator ) #forJSON( 'partnerAgreements', attributes.dataStore, 'startDate' )#