from tbl_TPM_Claims c with (nolock) inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.claimId = c.claimId and cc.ownerId = c.ownerId and cc.ownerType = c.ownerType inner join tbl_TPM_Requests r with (nolock) on r.requestId = cc.requestId and r.ownerId = cc.ownerId and r.ownerType = cc.ownerType inner join tbl_TPM_ProofofDelivery pod with (nolock) on c.claimid = cc.claimid and c.ownerid = cc.ownerid and c.ownerType = cc.ownerType and pod.contractNbr = cc.contractNbr inner join tbl_OPR_ClientOperators opr with (nolock) on pod.operatorid = opr.operatorId and opr.ownerid = pod.ownerid and opr.fsltableCode = pod.ownerType left outer join tbl_TER_Territories ter on ter.territoryid = opr.territoryid and opr.ownerid = ter.ownerid and opr.fsltableCode = ter.fsl_tableCode left outer join tbl_CRM_Affiliations af with (nolock) on af.ownerId = c.ownerId and af.ownerType = c.ownerType and af.orgId = and af.orgType = 'OPR' and af.partnerId = opr.operatorId and af.partnerType = 'OPR' left outer join tbl_TPM_Requests da with (nolock) on da.ownerId = pod.ownerId and da.ownerType = pod.ownerType and da.requestId = pod.flagLinkId and 'TPM' = pod.flagLinkType where c.ownerId = and c.ownerType = and pod.flagged = 'Y' and left(pod.flagComment,17) = 'Excluded Operator' and c.partnerId = and c.partnerType = 'OPR' and cc.reconciled = 'Y' and c.claimDate between and group by c.claimInvoice, c.claimDate, pod.operatorAffiliateNbr, da.assignedContractNbr, ter.name, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipcode, r.description, c.claimID, c.claimPeriodStart, c.claimId, cc.contractNBR, cc.approvedAmount, c.claimPeriodEnd, ter.name, af.partnerAffiliateNbr, c.claimRef, opr.mfrCustNum, pod.flagComment, da.description order by ter.name, opr.companyName, c.claimDate asc ---> select * from ( select distinct ter.name as territoryName, c.claimRef, c.claimInvoice, c.claimDate, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipcode, da.assignedContractNbr , da.description, pod.operatorAffiliateNbr, r.description as agreementDescription, pod.flagComment as comments, opr.mfrCustNum, c.claimPeriodStart, c.claimPeriodEnd, sum( pod.itemQty ) itemQty, sum( coalesce(pod.totalRebate,0) ) amountPaid, sum( coalesce(pod.qualifiedAmount,0) ) - sum( coalesce(pod.totalRebate,0) ) variance, row_number() over (order by ter.name, opr.companyname, c.claimDate) as row_num from tbl_TPM_Claims c with (nolock) inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.claimId = c.claimId and cc.ownerId = c.ownerId and cc.ownerType = c.ownerType inner join tbl_TPM_Requests r with (nolock) on r.requestId = cc.requestId and r.ownerId = cc.ownerId and r.ownerType = cc.ownerType inner join tbl_TPM_ProofofDelivery pod with (nolock) on c.claimid = cc.claimid and c.ownerid = cc.ownerid and c.ownerType = cc.ownerType and pod.contractNbr = cc.contractNbr inner join tbl_OPR_ClientOperators opr with (nolock) on pod.operatorid = opr.operatorId and opr.ownerid = pod.ownerid and opr.fsltableCode = pod.ownerType left outer join tbl_TER_Territories ter on ter.territoryid = opr.territoryid and opr.ownerid = ter.ownerid and opr.fsltableCode = ter.fsl_tableCode left outer join tbl_CRM_Affiliations af with (nolock) on af.ownerId = c.ownerId and af.ownerType = c.ownerType and af.orgId = 1007318 and af.orgType = 'OPR' and af.partnerId = opr.operatorId and af.partnerType = 'OPR' left outer join tbl_TPM_Requests da with (nolock) on da.ownerId = pod.ownerId and da.ownerType = pod.ownerType and da.requestId = pod.flagLinkId and 'TPM' = pod.flagLinkType where c.ownerId = 1 and c.ownerType = 'MFR' and pod.flagged = 'Y' and left(pod.flagComment,17) = 'Excluded Operator' and c.partnerId = 1007318 and c.partnerType = 'OPR' and cc.reconciled = 'Y' and c.claimDate between '01/01/2019' and '12/31/2019' group by c.claimInvoice, c.claimDate, pod.operatorAffiliateNbr, da.assignedContractNbr, ter.name, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipcode, r.description, c.claimID, c.claimPeriodStart, c.claimId, cc.contractNBR, cc.approvedAmount, c.claimPeriodEnd, ter.name, af.partnerAffiliateNbr, c.claimRef, opr.mfrCustNum, pod.flagComment, da.description ) as x -- where row_num between 24300 and 24325 order by row_num--, ter.name, opr.companyName, c.claimDate asc select distinct companyName, address, address2, city, state, zipcode, territoryName, mfrCustNum, operatorAffiliateNbr, comments from qry_reportData order by territoryName, companyName