Merging and validating data

[troygeri]

Hi Guys

I am trying to do something reasonably complex and need a bit of help. I have a table (tbl_DW_Twitter_Location_Parse) with these columns

ID, items, twitter_screen_name, location_orig, time_zone_orig, location_result, Parsed_Date

The twitter_screen_name is foreign key to a table called tbl_Twitter_Profile, so in tbl_DW_Twitter_Location_Parse it can appear multiple times. The latter table basically stores the location results of a location parsing function for each twitter_screen_name and could have up to around seven rows per screen name that can be either (field = location result) a country, region or city. You can get more than one country, city or region per screen name..

Here is an example below
ID Location twitter_screen_name Location_Result
1 Atlanta catbugle City
3 GA catbugle Region
5 US catbugle Country
6 Georgetown catbugle City

What I need to do is

1. Validate each entry against the other entries for the same twitter_profile name by comparing combinatins against a master reference table that contains combo of country,region and city in a single row.. for example taking the City Atlanta here, and seeing whether it is assocaited with the region and the country for the twitter_screen_name in question (i.e. Atlanta and Region GA and Country = US - does this combo exist on the master ref table, if not does just Atlanta and Region or Atlanta and Country exist?)

2. I then need to merge the multiple rows into a single row that would contain columns with the validated locations

twitter_screen_name, city, region, country

3. If we have a city and region that is valid but not for the country, then we need to pull back alternative country from the master ref table that contains that combination of city / region and drop the invalid country...

Thanks