In my previous post I stated that you would find more data anomalies - Wonder how I knew that.
This is not something that can be fixed with a single query. You are setting yourself up for a spectacular failure.
In order for this to work, you need to do something different.
- Get consistent source data
- Create a staging environment where you clean the data up for your own use
There may be other options, but these are the 2 that I would be looking at.