Find Markers by Geographic Location

SELECT * FROM (SELECT Country, [The-Prefix], MarkerCount, StateCount, convert(date,DATEADD(d,1,Changed)) as GoodUntil, CASE WHEN MarkerCount>1999 THEN RANK() OVER(ORDER BY MarkerCount DESC) ELSE 1000 END as Top10, Parent='', ParentFullName='', RANK() OVER(ORDER BY MarkerCount DESC) as StateRank FROM Countries WHERE MarkerCount>0 GROUP BY Country, [The-Prefix],MarkerCount, StateCount, Changed UNION SELECT State as Country, 0 as [The-Prefix], States.MarkerCount, 2 as StateCount, convert(date,DATEADD(d,1,States.Changed)) as GoodUntil, 1000 AS Top10, Countries.Country AS Parent, Countries.FullName AS ParentFullName, 1000 as StateRank FROM States INNER JOIN Countries ON States.CountryID=Countries.CountryID WHERE StatesAsCountries=1 AND States.MarkerCount>0 GROUP BY Country, Countries.FullName, State, States.MarkerCount, States.Changed) AS B ORDER BY Top10, Replace(Replace(Replace(Replace(Replace(Replace(Country,'The ',''),'Overseas ',''),'Dutch ','Netherlands '),'Bailiwick of ',''),'South Korea','Korea South'),'North Korea','Korea North')

UPDATE Countries SET Countries.MarkerCount=Temp.MarkerCount, Countries.StateCount=Temp.StateCount,Changed=GETDATE() FROM Countries INNER JOIN ( SELECT COUNT(*) as MarkerCount, Country, COUNT(DISTINCT State) as StateCount FROM Markers WHERE MarkerID>10 AND Accepted is not Null AND Rejected is Null AND Withdrawn is Null AND ReplacedbyID is Null GROUP BY Country) as Temp ON Countries.Country=Temp.Country