It seems like you are missing a rule: what do you want when there are entries for pdt_serv_id_num in the RATE_TBL with overlapping dates? For example, for 50242021060 there are entries with effective_date of 20210801 and 20210901, both with end_dt of 29991231. So the entry in CLAIMS_TBL for 50242021060 with PCH_DATE of 20211108 matches both entries in the RATE_TBL. Which should be used? From your WANT table, it appears the rule is to use the record in the RATE_TBL with end_dt of 29991231 that has the maximum effective date that is less than PCH_DATE, so in this case it is the RATE_TBL entry with effective_date of 20210901. Is this correct?
... View more