with rootRequests as ( select r.ownerId, r.ownerType, r.requestId, r.sourceRequestId, r.assignedContractNbr, c.assignedContractNbr as masterAssignedContractNbr, c.contractName, c.contractId, r.chainSequence, r.promoNbr, c.externalAgreement, r.totalEstimatedTrade, r.status, r.renewal, r.termStart, r.termEnd, mfr.mfr_name as mfrName from tbl_TPM_Requests r with (nolock) inner 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 = left outer join tbl_BRO_Principals ppl with (nolock) on ppl.ownerId = r.ownerId and ppl.ownerType = r.ownerType and ppl.principalId = r.principalId left outer join tblManufacturers mfr with (nolock) on mfr.mfr_id = ppl.mfrId where r.ownerId = and r.ownerType = ), claimSummary as ( select requestId, count( distinct claimId ) as claimCount from tbl_TPM_ClaimContracts with (nolock) where ownerId = and ownerType = and requestId in ( select requestId from rootRequests ) group by requestId ), rawTradeAgreements as ( select rr.contractId, rr.masterAssignedContractNbr + ' - ' + rr.contractName as description, sum( rr.totalEstimatedTrade ) totalEstimatedTrade, min( rr.termStart ) startDate, max( rr.termEnd ) endDate, sum( cs.claimCount ) claimCount, sum( case when renewal = 'Y' then 1 else 0 end ) as renewals, case when max( rr.termEnd ) >= GetDate() then 'Active' else 'Expired' end as status, rr.mfrName from rootRequests rr left outer join claimSummary cs on rr.requestId = cs.requestId group by rr.contractId, rr.masterAssignedContractNbr, rr.contractName, rr.mfrName ), tradeAgreements as ( select * from rawTradeAgreements where status = ) #forJSON( 'tradeAgreements', attributes.datastore, 'description' )#