The real issue is that one of your tables isn't normalized.  If you normalize your table, you don't...

You don't want to do it this way.  You want to split the string in b and then join on that split, otherwise you'll get matches like 'York' and 'New...

When I read the suggested solution, I knew there had to be a better approach, because it's scanning each of the tables three times: once for the beginning dates, once...

T-SQL is horrible at string manipulation.  Your best option is to use SQLCLR.

You also need to better define your options, Specifically what do you want to happen when...

Spam reported.