with data as ( select pod.claimId, pod.proofOfDeliveryId, pod.status, pod.operatorName, pod.itemCode, pod.itemDescription, pod.itemQty, pod.itemUOM, pod.itemCost, pod.rebateUnitPrice, pod.totalRebate, pod.contractNbr, case when pod.flagged = 'Y' then 1 else 0 end as flagged, pod.flagComment, pod.flagLinkId, pod.flagLinkData, pod.operatorInvoiceNbr, pod.operatorInvoiceDate, pod.cdrRecordId, pod.skuId as podSkuId, case when cdr.cdr_recordId > 0 then cdr.cdr_dstCode else pod.distributorCode end as distributorCode, case when cdr.cdr_recordId > 0 then cdr.cdr_dstName else pod.distributorName end as distributorName, pod.qualifiedCost, pod.qualifiedRate, pod.perContractRate, pod.perContractQualifiedAmount, pod.contractBaseRate, pod.contractBaseRateType, pod.incentivePct, pod.contractPrice, pod.qualifiedCostInvoiceDate, dstP.uniformBranchCodes, cdrB.cdr_recordID as branchID, cdrP.cdr_recordID as parentID, case when dstP.uniformBranchCodes = 'Y' then cdrP.cdr_recordID else cdrB.cdr_recordID end as CustomerSkuDstId, case when dstP.uniformBranchCodes = 'Y' then 'PDST' else 'DST' end as DstType from tbl_TPM_ProofOfDelivery pod with (nolock) left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = pod.ownerId and cdr.fsl_tablecode = pod.ownerType and cdr.cdr_recordId = pod.cdrRecordId left outer join tbl_CDR_Distributors cdrB with (nolock) on cdrB.ownerid = pod.ownerid and cdrB.fsl_tableCode = pod.ownerType and cdrB.cdr_recordid = pod.cdrRecordId left outer join tbl_DST_Distributors dstB with (nolock) on cdrB.fsl_dstid = dstB.dstid left outer join tbl_DST_Distributors dstP with (nolock) on dstB.dstparentCo = dstP.dstid left outer join tbl_CDR_Distributors cdrP with (nolock) on dstP.dstid = cdrP.fsl_dstid and cdrP.ownerid = pod.ownerid and cdrP.fsl_tableCode = pod.ownerType where pod.ownerId = and pod.ownerType = and pod.claimId = and pod.contractNbr= ) select distinct count(*) over() as fseRowCount, data.* from data order by distributorName, operatorName, itemCode, proofOfDeliveryId for json path, include_null_values