with claimSummary as ( select c.partnerId, c.partnerType, year( c.claimPeriodEnd ) as claimYear, r.assignedContractNbr, r.volumeGenerator, r.description, c.claimId, c.claimAmount, c.claimInvoice, c.claimRef, c.claimDate, c.claimPeriodStart, c.claimPeriodEnd, sum( #_itemQtyCol# ) rawQty, sum( case when pod.status != 'DENIED' then pod.#_itemQtyCol# else 0 end ) approvedQty, sum( case when pod.status = 'DENIED' then pod.#_itemQtyCol# else 0 end ) deniedQty, sum( case when pod.status != 'DENIED' and cho.cho_type in ( 'U', 'I' ) and r.volumeGenerator = 'Y' then pod.#_itemQtyCol# else 0 end ) as opaQty from tbl_TPM_ProofOfDelivery pod with (nolock) inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.claimId = pod.claimId and cc.ownerId = pod.ownerId and cc.ownerType = pod.ownerType and cc.contractNbr = pod.contractNbr inner join tbl_TPM_Claims c with (nolock) on c.claimId = cc.claimId and c.ownerId = cc.ownerId and c.ownerType = cc.ownerType and c.reconciledDate is not null inner join tbl_TPM_Requests r with (nolock) on r.ownerId = cc.ownerId and r.ownertype = cc.ownerType and r.requestId = cc.requestId inner join tbl_TPM_Contracts ct with (nolock) on ct.contractId = r.contractId and ct.ownerId = r.ownerId and ct.ownerType = r.ownerType inner join tbL_OPR_ClientOperators opr with (nolock) on opr.operatorId = pod.operatorId and opr.fsltablecode = pod.ownerType and opr.ownerId = pod.ownerId left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where ct.partnerId in ( select opr.operatorId from tbl_OPR_ClientOperators opr with (nolock) where opr.ownerId = c.ownerId and opr.fsltablecode = c.ownerType and opr.fsl_choId = ) and ct.partnerType = 'OPR' and ct.ownerId = and ct.ownerType= group by c.partnerId, c.partnerType, r.assignedContractNbr, r.description, r.volumeGenerator, c.claimId, c.claimDate, c.claimPeriodStart, c.claimPeriodEnd, c.claimAmount, c.claimInvoice, c.claimRef ) select case when c.partnerType = 'OPR' then copr.companyName else ccdr.cdr_dstName end as claimant, c.* from claimSummary c left outer join tbl_OPR_ClientOperators copr with (nolock) on copr.operatorId = c.partnerId and 'OPR' = c.partnerType and copr.ownerId = and copr.fsltablecode = left outer join tbl_CDR_Distributors ccdr with (nolock) on ccdr.cdr_recordId = c.partnerId and 'CDR' = c.partnerType and ccdr.ownerId = and ccdr.fsl_tablecode = order by claimYear, claimPeriodEnd, claimPeriodStart, claimDate for JSON PATH, INCLUDE_NULL_VALUES