name changes causing duplicates, sql code fix

[maygross]

I have data that has customer name, customer id, subscriber numbers, and dates and billings for each month. I want to join this data to another table that only has subscriber numbers to get the customer id and name in my final output. 

My issue here is this: sometimes the customer names change, like in April the same customer ID would be called ABC CORP and in May its called ABCD CORP. Meaning that when I join these tables, I will have duplicates because of the different names. Also, some subscribers who have left before the change would retain the old name instead of the new name. I don't know the best way to effectively get the most recent name of the customer id by date and apply that same name to all the subscriber numbers (even if they left before the name change). Also, sometimes there's issues with blank customer names so I would like to exclude those too. 

I don't need the date (fin_cycl_yrmn) to be included in the final output but I just need the most recent customer_name.

I would prefer to do this through a SQL code through a connect in DB tool since my existing workflow is already too long, I don't want to do this through several extra tools. 

My attachment has a visual example of my problem and desired solution. 

[caltang]

You're saying that the customer name changes along with the ID... and you want to group them via SQL code? 

If your company names change and you want to match them, it's going to be hard since you may need to use Fuzzy Match to solve it. Otherwise you'll have to do some data cleansing before you run your process. 

Standardize your data is a better solution... not sure how to even fuzzy match in SQL. I think it'scalled Approximate String Matching....