I'm having so much trouble with this and I am unsure why. I need a join to a rate code table, but only grabbing the rate code from this table for each member with the max eff date (there are multiple rate codes in the table for each member but I only want the one with max(effdate)).
select distinct ek.memid, rs.ratecode, rs.description, rs.effdate from enrollkeys ek inner join ratesuffix rs on ek.ratecode = rs.ratecode
How do I add logic to only see the ratecodes listed per member with the max(effdate)? So if a member had 4 ratecodes, I only want to see the ones with the highest effdate.