select opr.ownerId, opr.fsltablecode, opr.operatorId, opr.fsl_choId, max( coalesce( c.claimPeriodEnd, c.claimDate )) lastClaimDate from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.operatorId = opr.operatorId and pod.ownerId = opr.ownerId and pod.ownerType = opr.fsltablecode inner join tbl_TPM_Claims c with (nolock) on c.ownerId = pod.ownerId and c.ownerType = pod.ownerType and c.claimId = pod.claimId where c.ownerType = 'MFR' and nullif( opr.fsl_choId, '' ) is not null and nullif( opr.freshnessDate, '' ) is null group by opr.ownerId, opr.fsltablecode, opr.operatorId, opr.fsl_choId