with chainRequests as ( select floor( 10191817 * sqrt( rp.partnerId * log10( rp.partnerId ) ) ) as partnerTk, rp.partnerType, r.requestId, floor( 10191817 * sqrt( r.requestId * log10( r.requestId ) ) ) as requestTk, r.sourceRequestId, r.chainSequence, r.description, r.termStart, r.termEnd, r.endDate, r.totalEstimatedTrade, ( select count( distinct claimId ) from tbl_TPM_ClaimContracts with (nolock) where ownerId = r.ownerId and ownerType = r.ownerType and requestId = r.requestId ) as claimCount, case when renewal = 'Y' then 'Renewal' when sourceRequestId > 0 then 'Revision' else 'Initial' end source, r.status, c.assignedContractNbr, r.paymentMethod, r.payableTo, rp.partnerId primaryPartnerId, rp.partnerType primaryPartnerType, c.contractId, c.externalAgreement, case when r.chainSequence >= coalesce( c.lastApprovedChainSequence, 0 ) then 'true' else 'false' end lastInTheChain, r.promoNbr from tbl_TPM_Requests r with (nolock) left outer join tbl_TPM_Contracts c with (nolock) on c.contractId = r.contractId and c.ownerId = r.ownerId and c.ownerType = c.ownerType inner join tbl_TPM_RequestPartners rp with (nolock) on rp.ownerId = r.ownerId and rp.ownertype = r.ownerType and rp.requestId = r.requestId and rp.primaryPartner = 'Y' and rp.partnerId = and rp.partnerType = where r.ownerId = and r.ownerType = and c.contractId = ) #forJSON( 'chainRequests', attributes.datastore, 'chainSequence desc' )#