declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @contractId int = ; with agreementRevisions as ( select r.ownerId, r.ownerType, r.requestId, r.promoNbr, r.chainSequence, r.description, r.status, r.startDate, r.endDate, r.termStart, r.termEnd, 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.contractId = @contractId group by r.ownerId, r.ownerType, r.requestId, r.promoNbr, r.description, r.chainSequence, r.status, r.startDate, r.endDate, r.termStart, r.termEnd ) #forJSON( 'agreementRevisions', attributes.dataStore, 'chainSequence' )#