Based on their filled in location.
Q&A for system and network administrators
-- Which country are the users from? -- Based on their filled in location WITH UsersCountry AS ( SELECT CASE -- sky - Must precede sk for Saskatchewan WHEN Location LIKE '%Cesky Tesin%' THEN 'Czech Republic' WHEN Location LIKE '%El Mosky%' THEN 'Egypt' WHEN Location LIKE '%Jyväskylä%' THEN 'Finland' WHEN Location LIKE '%Jyvaskyla%' THEN 'Finland ' WHEN Location LIKE '%Sandusky%' THEN 'United States' WHEN Location LIKE '%Khmelnitsky%' THEN 'Ukraine' WHEN Location LIKE '%Zhukovsky%' THEN 'Russia' WHEN UPPER(Location) LIKE '%SKY%' THEN 'Offbeat - Sky' WHEN UPPER(Location) LIKE '%AFGHANISTAN%' THEN 'Afghanistan' WHEN UPPER(Location) LIKE '%KABUL%' THEN 'Afghanistan' WHEN UPPER(Location) LIKE '%, AF%' THEN 'Afghanistan' WHEN UPPER(Location) LIKE '%,AF%' THEN 'Afghanistan' WHEN UPPER(Location) LIKE '%ALBANIA%' THEN 'Albania' WHEN UPPER(Location) LIKE '%TIRANA%' THEN 'Albania' WHEN UPPER(Location) LIKE '%ALGERIA%' THEN 'Algeria' WHEN UPPER(Location) LIKE '%ALGIERS%' THEN 'Algeria' WHEN UPPER(Location) LIKE '%ANDORRA%' THEN 'Andorra' WHEN UPPER(Location) LIKE '%ANTARCTIC%' THEN 'Antarctica' -- Antigua and Barbuda WHEN UPPER(Location) LIKE '%ANTIGUA%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%BARBUDA%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%ARGENTINA%' THEN 'Argentina' --Buenos Aires WHEN UPPER(Location) LIKE '%BUENOS AIRES%' THEN 'Argentina' WHEN UPPER(Location) LIKE '%ARMENIA%' THEN 'Armenia' WHEN UPPER(Location) LIKE '%YEREVAN%' THEN 'Armenia' -- Australia & Austria WHEN UPPER(Location) LIKE '%AUSTRALIA%' THEN 'Australia' WHEN Location LIKE '%AU' THEN 'Australia' WHEN UPPER(Location) LIKE '%ADELAIDE%' THEN 'Australia' WHEN UPPER(Location) LIKE '%FREMANTLE%' THEN 'Australia' WHEN UPPER(Location) LIKE '%GOLD COAST%' THEN 'Australia' WHEN UPPER(Location) LIKE '%SYDNEY%' THEN 'Australia' WHEN UPPER(Location) LIKE '%TASMANIA%' THEN 'Australia' WHEN UPPER(Location) LIKE '%AUSTRIA%' THEN 'Austria' WHEN lower(Location) LIKE '%surat%' THEN 'India' WHEN lower(Location) LIKE '%gujarat' THEN 'India' WHEN Location LIKE '%AT' THEN 'Austria' WHEN UPPER(Location) LIKE '%VIENNA%' THEN 'Austria' WHEN UPPER(Location) LIKE '%IRAN%' THEN 'Iran' -- Azerbaijan, Iran WHEN UPPER(Location) LIKE '%AZERBAIJAN%' THEN 'Azerbaijan' WHEN UPPER(Location) LIKE '%MEMBAKUT%' THEN 'Malaysia' WHEN UPPER(Location) LIKE '%BAKU%' THEN 'Azerbaijan' WHEN UPPER(Location) LIKE '%BAHAMAS%' THEN 'Bahamas' WHEN UPPER(Location) LIKE '%BAHRAIN%' THEN 'Bahrain' WHEN UPPER(Location) LIKE '%BANGLADESH%' THEN 'Bangladesh' WHEN UPPER(Location) LIKE '%BARBADOS%' THEN 'Barbados' WHEN UPPER(Location) LIKE '%BELARUS%' THEN 'Belarus' WHEN UPPER(Location) LIKE '%BELGIUM%' THEN 'Belgium' WHEN UPPER(Location) LIKE '%BRUXELLES%' THEN 'Belgium' WHEN UPPER(Location) LIKE '%BRUSSELS%' THEN 'Belgium' WHEN UPPER(Location) LIKE '%BELIZE%' THEN 'Belize' WHEN UPPER(Location) LIKE '%BENIN%' THEN 'Benin' WHEN UPPER(Location) LIKE '%BERMUDA%' THEN 'Bermuda' WHEN UPPER(Location) LIKE '%BHUTAN%' THEN 'Bhutan' WHEN UPPER(Location) LIKE '%BOLIVIA%' THEN 'Bolivia' WHEN UPPER(Location) LIKE '%BOSNIA%' THEN 'Bosnia and Herzegovina' WHEN UPPER(Location) LIKE '%BOTSWANA%' THEN 'Botswana' WHEN UPPER(Location) LIKE '%BRASIL%' THEN 'Brazil' WHEN UPPER(Location) LIKE '%BRAZIL%' THEN 'Brazil' WHEN lower(Location) LIKE lower('%Americana%') THEN 'Brazil - Americana' WHEN Location LIKE '%BR' THEN 'Brazil' WHEN lower (Location) LIKE '%belo horizonte%' THEN 'Brazil' WHEN lower (Location) LIKE '%rio de janeiro%' THEN 'Brazil' WHEN lower (Location) LIKE '%sao paulo%' THEN 'Brazil' WHEN Location LIKE '%São Paulo%' THEN 'Brazil' WHEN UPPER(Location) LIKE '%BRUNEI%' THEN 'Brunei' WHEN UPPER(Location) LIKE '%BULGARIA%' THEN 'Bulgaria' WHEN UPPER(Location) LIKE '%SOFIA%' THEN 'Bulgaria' WHEN UPPER(Location) LIKE '%CAMBODIA%' THEN 'Cambodia' WHEN UPPER(Location) LIKE '%KAMPOT%' THEN 'Cambodia' WHEN UPPER(Location) LIKE '%CAMEROON%' THEN 'Cameroon' --Yaoundé WHEN UPPER(Location) LIKE '%YAOUND%' THEN 'Cameroon' -- Canada WHEN UPPER(Location) LIKE '%CANADA%' THEN 'Canada' WHEN UPPER(Location) LIKE '%CANADUR%' THEN 'Canada' WHEN UPPER(Location) LIKE '%BRITISH COLUMBIA%' THEN 'Canada' --Mexicali, Baja California, Mexico WHEN UPPER(Location) LIKE '%MEXICALI%' THEN 'Mexico' WHEN UPPER(Location) LIKE '%B.C.%' THEN 'Canada' WHEN UPPER(Location) LIKE '%, BC%' THEN 'Canada' WHEN UPPER(Location) LIKE '%,BC%' THEN 'Canada' WHEN UPPER(Location) LIKE '% BC%' THEN 'Canada' WHEN UPPER(Location) LIKE '%BCN%' THEN 'Spain' WHEN UPPER(Location) LIKE '%BC%' THEN 'Canada' WHEN UPPER(Location) LIKE '%VANCOUVER, CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%VANCOUVER,CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%VANCOUVER CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%VANCOUVER, CA%' THEN 'United States' WHEN UPPER(Location) LIKE '%VANCOUVER,CA%' THEN 'United States' WHEN UPPER(Location) LIKE '%VANCOUVER CA%' THEN 'United States' WHEN UPPER(Location) LIKE '%VANCOUVER IS%' THEN 'Canada' WHEN UPPER(Location) LIKE '%VANCOUVER, WA%' THEN 'United States' WHEN UPPER(Location) LIKE '%VANCOUVER,WA%' THEN 'United States' WHEN UPPER(Location) LIKE '%VANCOUVER WA%' THEN 'United States' WHEN UPPER(Location) LIKE '%ALBERTA%' THEN 'Canada' WHEN UPPER(Location) LIKE '%, AB%' THEN 'Canada' WHEN UPPER(Location) LIKE '%,AB%' THEN 'Canada' WHEN (Location) LIKE '% AB%' THEN 'Canada' WHEN UPPER(Location) LIKE '%CALGARY%' THEN 'Canada' WHEN UPPER(Location) LIKE '%FORT SASKATCHEWAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%SASK' THEN 'Canada' WHEN UPPER(Location) LIKE '%, SK%' THEN 'Canada' WHEN UPPER(Location) LIKE '%,SK%' THEN 'Canada' WHEN Location LIKE '% SK%' THEN 'Canada' WHEN UPPER(Location) LIKE '%MANITOBA%' THEN 'Canada' WHEN UPPER(Location) LIKE '%, MB%' THEN 'Canada' WHEN UPPER(Location) LIKE '%,MB%' THEN 'Canada' WHEN UPPER(Location) LIKE '% MB%' THEN 'Canada' WHEN UPPER(Location) LIKE '%WINNIPEG%' THEN 'Canada' WHEN UPPER(Location) LIKE '%ONTARIO, CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%ONTARIO,CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%ONTARIO CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%BRAMPTON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%GUELPH%' THEN 'Canada' WHEN UPPER(Location) LIKE '%HAMILTON, ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%HAMILTON,ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%HAMILTON ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%HAMILTON, CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%HAMILTON,CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%HAMILTON CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%HAMILTON, NEW Y%' THEN 'United States' WHEN UPPER(Location) LIKE '%HAMILTON,NEW Y%' THEN 'United States' WHEN UPPER(Location) LIKE '%HAMILTON NEW Y%' THEN 'United States' WHEN UPPER(Location) LIKE '%HAMILTON, NY%' THEN 'United States' WHEN UPPER(Location) LIKE '%HAMILTON,NY%' THEN 'United States' WHEN UPPER(Location) LIKE '%HAMILTON NY%' THEN 'United States' WHEN UPPER(Location) LIKE '%HAMILTON, NEW Z%' THEN 'New Zealand' WHEN UPPER(Location) LIKE '%HAMILTON,NEW Z%' THEN 'New Zealand' WHEN UPPER(Location) LIKE '%HAMILTON NEW Z%' THEN 'New Zealand' WHEN UPPER(Location) LIKE '%LONDON, ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%LONDON,ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%LONDON ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%LONDON, CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%LONDON,CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%LONDON CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%MARKHAM, IL%' THEN 'United States' WHEN UPPER(Location) LIKE '%MARKHAM,IL%' THEN 'United States' WHEN UPPER(Location) LIKE '%MARKHAM IL%' THEN 'United States' WHEN UPPER(Location) LIKE '%MARKHAM%' THEN 'Canada' WHEN UPPER(Location) LIKE '%MISSISSAUGA%' THEN 'Canada' WHEN UPPER(Location) LIKE '%OTTAWA, ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%OTTAWA,ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%OTTAWA ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%OTTAWA, CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%OTTAWA,CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%OTTAWA CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%OTTAWA, IL%' THEN 'United States' WHEN UPPER(Location) LIKE '%OTTAWA,IL%' THEN 'United States' WHEN UPPER(Location) LIKE '%OTTAWA IL%' THEN 'United States' WHEN UPPER(Location) LIKE '%OTTAWA%' THEN 'Canada' WHEN UPPER(Location) LIKE '%PETERBOROUGH, ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%PETERBOROUGH,ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%PETERBOROUGH ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%PETERBOROUGH, CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%PETERBOROUGH,CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%PETERBOROUGH CAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%PETERBOROUGH, NH%' THEN 'United States' WHEN UPPER(Location) LIKE '%PETERBOROUGH,NH%' THEN 'United States' WHEN UPPER(Location) LIKE '%PETERBOROUGH NH%' THEN 'United States' WHEN UPPER(Location) LIKE '%PETERBOROUGH, US%' THEN 'United States' WHEN UPPER(Location) LIKE '%PETERBOROUGH,US%' THEN 'United States' WHEN UPPER(Location) LIKE '%PETERBOROUGH US%' THEN 'United States' WHEN UPPER(Location) LIKE '%TORONTO%' THEN 'Canada' WHEN UPPER(Location) LIKE '%VAUGHAN%' THEN 'Canada' WHEN UPPER(Location) LIKE '%WINDSOR, ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%WINDSOR,ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%WINDSOR ON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%WINDSOR, CA%' THEN 'Canada' WHEN UPPER(Location) LIKE '%WINDSOR,CA%' THEN 'Canada' WHEN UPPER(Location) LIKE '%WINDSOR CA%' THEN 'Canada' WHEN UPPER(Location) LIKE '%ONTARIO, CAL%' THEN 'United States' WHEN UPPER(Location) LIKE '%ONTARIO,CAL%' THEN 'United States' WHEN UPPER(Location) LIKE '%ONTARIO CAL%' THEN 'United States' WHEN UPPER(Location) LIKE '%ONTARIO, US%' THEN 'United States' WHEN UPPER(Location) LIKE '%ONTARIO,US%' THEN 'United States' WHEN UPPER(Location) LIKE '%ONTARIO US%' THEN 'United States' WHEN UPPER(Location) LIKE '%ONTARIO, U.S%' THEN 'United States' WHEN UPPER(Location) LIKE '%ONTARIO,U.S%' THEN 'United States' WHEN UPPER(Location) LIKE '%ONTARIO U.S%' THEN 'United States' WHEN UPPER(Location) LIKE '%ONTARIO%' THEN 'Canada' WHEN UPPER(Location) LIKE '%QUEBEC%' THEN 'Canada' WHEN UPPER(Location) LIKE '%QC%' THEN 'Canada' WHEN UPPER(Location) LIKE '%CHICOUTIMI%' THEN 'Canada' WHEN UPPER(Location) LIKE '%MONTREAL%' THEN 'Canada' WHEN Location LIKE '%Montréal%' THEN 'Canada' WHEN Location LIKE '%montréal%' THEN 'Canada' WHEN UPPER(Location) LIKE '%NEW BRUNSWICK%' THEN 'Canada' WHEN UPPER(Location) LIKE '%, NB%' THEN 'Canada' WHEN UPPER(Location) LIKE '%,NB%' THEN 'Canada' WHEN UPPER(Location) LIKE '% NB%' THEN 'Canada' WHEN UPPER(Location) LIKE '%Saint John, N%' THEN 'Canada' WHEN UPPER(Location) LIKE '%NOVA SCOTIA%' THEN 'Canada' WHEN UPPER(Location) LIKE '%, NS%' THEN 'Canada' WHEN UPPER(Location) LIKE '%,NS%' THEN 'Canada' WHEN UPPER(Location) LIKE '% NS%' THEN 'Canada' WHEN UPPER(Location) LIKE '%HALIFAX, N%' THEN 'Canada' WHEN UPPER(Location) LIKE '%PRINCE EDWARD ISLAND%' THEN 'Canada' WHEN UPPER(Location) LIKE '%PEI' THEN 'Canada' WHEN UPPER(Location) LIKE '%, PE%' THEN 'Canada' WHEN UPPER(Location) LIKE '%,PE%' THEN 'Canada' -- WHEN UPPER(Location) LIKE '% PE%' THEN 'Canada' WHEN UPPER(Location) LIKE '%NEWFOUNDLAND%' THEN 'Canada' WHEN UPPER(Location) LIKE '%, NF%' THEN 'Canada' WHEN UPPER(Location) LIKE '%,NF%' THEN 'Canada' WHEN UPPER(Location) LIKE '% NF%' THEN 'Canada' WHEN UPPER(Location) LIKE '%YUKON%' THEN 'Canada' WHEN UPPER(Location) LIKE '%, YK%' THEN 'Canada' WHEN UPPER(Location) LIKE '%,YK%' THEN 'Canada' WHEN UPPER(Location) LIKE '% YK%' THEN 'Canada' WHEN UPPER(Location) LIKE '%NORTHWEST TERRITORIES%' THEN 'Canada' WHEN UPPER(Location) LIKE '%, NT%' THEN 'Canada' WHEN UPPER(Location) LIKE '%,NT%' THEN 'Canada' WHEN UPPER(Location) LIKE '% NT%' THEN 'Canada' WHEN UPPER(Location) LIKE '%NUNAVUT%' THEN 'Canada' WHEN UPPER(Location) LIKE '%, NU%' THEN 'Canada' WHEN UPPER(Location) LIKE '%,NU%' THEN 'Canada' -- WHEN UPPER(Location) LIKE '% NU%' THEN 'Canada' WHEN UPPER(Location) LIKE '%CAYMAN ISLANDS%' THEN 'Cayman Islands' WHEN UPPER(Location) LIKE '%CHILE%' THEN 'Chile' WHEN UPPER(Location) LIKE '%EASTER ISLAND%' THEN 'Chile' -- China WHEN UPPER(Location) LIKE '%CHINA%' THEN 'China' WHEN UPPER(Location) LIKE '%BEIJING%' THEN 'China' WHEN UPPER(Location) LIKE '%SHANDONG%' THEN 'China' WHEN UPPER(Location) LIKE '%SHANGHAI%' THEN 'China' WHEN UPPER(Location) LIKE '%ZHEJIANG%' THEN 'China' WHEN UPPER(Location) LIKE '%COLOMBIA%' THEN 'Colombia' WHEN UPPER(Location) LIKE '%SINCELEJO%' THEN 'Colombia' WHEN UPPER(Location) LIKE '%CONGO%' THEN 'Congo, Republic of the' WHEN UPPER(Location) LIKE '%COSTA RICA%' THEN 'Costa Rica' WHEN UPPER(Location) LIKE '%CROATIA%' THEN 'Croatia' WHEN UPPER(Location) LIKE '%CUBA%' THEN 'Cuba' WHEN UPPER(Location) LIKE '%CYPRUS%' THEN 'Cyprus' WHEN UPPER(Location) LIKE '%CZECH%' THEN 'Czech Republic ' WHEN UPPER(Location) LIKE '%PRAGUE%' THEN 'Czech Republic ' WHEN UPPER(Location) LIKE '%DENMARK%' THEN 'Denmark' WHEN UPPER(Location) LIKE '%GREENLAND%' THEN 'Denmark' WHEN UPPER(Location) LIKE '%DOMINICAN REPUBLIC%' THEN 'Dominican Republic' WHEN UPPER(Location) LIKE '%ECUADOR%' THEN 'Ecuador' WHEN UPPER(Location) LIKE '%EGYPT%' THEN 'Egypt' WHEN UPPER(Location) LIKE '%CAIRO%' THEN 'Egypt' WHEN UPPER(Location) LIKE '%EL SALVADOR%' THEN 'El Salvador' WHEN UPPER(Location) LIKE '%ESTONIA%' THEN 'Estonia' WHEN UPPER(Location) LIKE '%ETHIOPIA%' THEN 'Ethiopia' WHEN UPPER(Location) LIKE '%FAROE ISLANDS%' THEN 'Faroe Islands' WHEN UPPER(Location) LIKE '%FIJI%' THEN 'Fiji' WHEN UPPER(Location) LIKE '%FINLAND%' THEN 'Finland' WHEN UPPER(Location) LIKE '%HELSINKI%' THEN 'Finland' -- France WHEN lower(Location) LIKE lower('%Paris, TN%') THEN 'United States' WHEN UPPER(Location) LIKE '%FRANCE%' THEN 'France' WHEN UPPER(Location) LIKE '%CORSICA%' THEN 'France' WHEN UPPER(Location) LIKE '%FRENCH RIVIERA%' THEN 'France' WHEN UPPER(Location) LIKE '%LYON%' THEN 'France' WHEN UPPER(Location) LIKE '%PARIS%' THEN 'France' WHEN UPPER(Location) LIKE '%MARTINIQUE%' THEN 'France' -- Georgia WHEN UPPER(Location) LIKE '%TBILISI%' THEN 'Georgia' WHEN UPPER(Location) LIKE '%KHELVACHAURI%' THEN 'Georgia' -- Germany WHEN UPPER(Location) LIKE '%GERMANY%' THEN 'Germany' WHEN UPPER(Location) LIKE '%BERLIN%' THEN 'Germany' WHEN UPPER(Location) LIKE '%BREMEN%' THEN 'Germany' WHEN UPPER(Location) LIKE '%HAMBURG%' THEN 'Germany' WHEN UPPER(Location) LIKE '%MUNICH%' THEN 'Germany' WHEN UPPER(Location) LIKE '%NORTH RHINE-WESTPHALIA%' THEN 'Germany' WHEN UPPER(Location) LIKE '%DEUTSCHLAND%' THEN 'Germany ' WHEN UPPER(Location) LIKE '%SAUERLAND%' THEN 'Germany' WHEN lower(Location) LIKE lower('%Lower Saxony%') THEN 'Germany' WHEN UPPER(Location) LIKE '%GHANA%' THEN 'Ghana' WHEN UPPER(Location) LIKE '%ACCRA%' THEN 'Ghana' WHEN UPPER(Location) LIKE '%GREECE%' THEN 'Greece' WHEN UPPER(Location) LIKE '%METAXOURGIO%' THEN 'Greece' WHEN UPPER(Location) LIKE '%THESSALONIKI%' THEN 'Greece' WHEN UPPER(Location) LIKE '%GUATEMALA%' THEN 'Guatemala' WHEN UPPER(Location) LIKE '%GUYANA%' THEN 'Guyana' WHEN UPPER(Location) LIKE '%HAITI%' THEN 'Haiti' WHEN UPPER(Location) LIKE '%HONDURAS%' THEN 'Honduras' WHEN UPPER(Location) LIKE '%HONG KONG%' THEN 'China - Hong Kong' WHEN UPPER(Location) LIKE '%HONGKONG%' THEN 'China - Hong Kong' WHEN UPPER(Location) LIKE '%HUNGARY%' THEN 'Hungary' WHEN UPPER(Location) LIKE '%BUDAPEST%' THEN 'Hungary' WHEN UPPER(Location) LIKE '%ICELAND%' THEN 'Iceland' -- India WHEN UPPER(Location) LIKE '%INDIANA%' THEN 'United States' WHEN UPPER(Location) LIKE '%INDIA%' THEN 'India' WHEN UPPER(Location) LIKE '%AHMEDABAD%' THEN 'India' WHEN UPPER(Location) LIKE '%AJMER%' THEN 'India' WHEN UPPER(Location) LIKE '%BANGALORE%' THEN 'India' WHEN UPPER(Location) LIKE '%BARODA, MI%' THEN 'United States' WHEN UPPER(Location) LIKE '%BARODA%' THEN 'India' WHEN UPPER(Location) LIKE '%BENGALURU%' THEN 'India' WHEN UPPER(Location) LIKE '%BHILWARA%' THEN 'India' WHEN UPPER(Location) LIKE '%BHOPAL%' THEN 'India' WHEN UPPER(Location) LIKE '%BHUBNESHWAR%' THEN 'India' WHEN UPPER(Location) LIKE '%CHANDIGARH%' THEN 'India' WHEN UPPER(Location) LIKE '%CHANDIGHAR%' THEN 'India' WHEN UPPER(Location) LIKE '%CHANDIGHARH%' THEN 'India' WHEN UPPER(Location) LIKE '%CHENNAI%' THEN 'India' WHEN UPPER(Location) LIKE '%DEHRADUN%' THEN 'India' WHEN UPPER(Location) LIKE '%DELHI%' THEN 'India' WHEN UPPER(Location) LIKE '%DOMBIVLI%' THEN 'India' WHEN UPPER(Location) LIKE '%GANDHINAGAR%' THEN 'India' WHEN UPPER(Location) LIKE '%GURGAON%' THEN 'India' WHEN UPPER(Location) LIKE '%HYDERABAD%' THEN 'India' WHEN UPPER(Location) LIKE '%INDORE, WV%' THEN 'United States' WHEN UPPER(Location) LIKE '%INDORE%' THEN 'India' WHEN UPPER(Location) LIKE '%RAJASTHAN%' THEN 'India' WHEN UPPER(Location) LIKE '%JAMKALYANPUR%' THEN 'India' WHEN UPPER(Location) LIKE '%KERALA%' THEN 'India' WHEN UPPER(Location) LIKE '%LUDHIANA%' THEN 'India' WHEN UPPER(Location) LIKE '%MADURAI%' THEN 'India' WHEN UPPER(Location) LIKE '%MAHARASHTRA%' THEN 'India' WHEN UPPER(Location) LIKE '%MULUND%' THEN 'India' WHEN UPPER(Location) LIKE '%MUMBAI%' THEN 'India' WHEN UPPER(Location) LIKE '%NASHIK%' THEN 'India' WHEN UPPER(Location) LIKE '%NOIDA%' THEN 'India' WHEN UPPER(Location) LIKE '%PATNA%' THEN 'India' WHEN UPPER(Location) LIKE '%PONDICHERRY%' THEN 'India' WHEN UPPER(Location) LIKE '%PUNE%' THEN 'India' WHEN UPPER(Location) LIKE '%RAJKOT%' THEN 'India' WHEN UPPER(Location) LIKE '%SURAT%' THEN 'India' WHEN UPPER(Location) LIKE '%TAMIL NADU%' THEN 'India' WHEN UPPER(Location) LIKE '%TRIVANDRUM%' THEN 'India' WHEN UPPER(Location) LIKE '%UDAIPUR%' THEN 'India' WHEN UPPER(Location) LIKE '%VIJAYAWADA%' THEN 'India' WHEN UPPER(Location) LIKE '%VISHAKAPATNAM%' THEN 'India' WHEN UPPER(Location) LIKE '%VIZAG%' THEN 'India' WHEN UPPER(Location) LIKE '%INDONESIA%' THEN 'Indonesia' WHEN lower(Location) LIKE '%ambon%' THEN 'Indonesia' WHEN lower(Location) LIKE '%balilihan%' THEN 'Philippines' WHEN lower(Location) LIKE '%balingen%' THEN 'Germany' WHEN lower(Location) LIKE '%bali%' THEN 'Indonesia' WHEN lower(Location) LIKE '%bandung%' THEN 'Indonesia' WHEN lower(Location) LIKE '%batam%' THEN 'Indonesia' WHEN lower(Location) LIKE '%bekasi%' THEN 'Indonesia' WHEN lower(Location) LIKE '%bogor%' THEN 'Indonesia' WHEN lower(Location) LIKE '%denpasar%' THEN 'Indonesia' WHEN lower(Location) LIKE '%depok%' THEN 'Indonesia' WHEN lower(Location) LIKE '%jakarta%' THEN 'Indonesia' WHEN lower(Location) LIKE '%malang%' THEN 'Indonesia' WHEN lower(Location) LIKE '%surabaya%' THEN 'Indonesia' WHEN lower(Location) LIKE '%yogyakarta%' THEN 'Indonesia' WHEN UPPER(Location) LIKE '%IRAN%' THEN 'Iran' WHEN UPPER(Location) LIKE '%KASHAN%' THEN 'Iran' WHEN UPPER(Location) LIKE '%MASHHAD%' THEN 'Iran' WHEN UPPER(Location) LIKE '%MEHRSHAHR%' THEN 'Iran' WHEN UPPER(Location) LIKE '%TEHRAN%' THEN 'Iran' WHEN UPPER(Location) LIKE '%IRAQ%' THEN 'Iraq' WHEN UPPER(Location) LIKE '%ISRAEL%' THEN 'Israel' -- Italy WHEN UPPER(Location) LIKE '%ITALIA%' THEN 'Italy' WHEN UPPER(Location) LIKE '%ITALY%' THEN 'Italy' WHEN UPPER(Location) LIKE '%BOLOGNA%' THEN 'Italy' WHEN UPPER(Location) LIKE '%MILAN%' THEN 'Italy' WHEN UPPER(Location) LIKE '%NAPOLI%' THEN 'Italy' WHEN UPPER(Location) LIKE '%ROME%' THEN 'Italy' WHEN UPPER(Location) LIKE '%TRENTO%' THEN 'Italy' WHEN UPPER(Location) LIKE '%JAMAICA%' THEN 'Jamaica' WHEN UPPER(Location) LIKE '%JAPAN%' THEN 'Japan' WHEN UPPER(Location) LIKE '%AIZUWAKAMATSU%' THEN 'Japan' WHEN UPPER(Location) LIKE '%JORDAN%' THEN 'Jordan' WHEN UPPER(Location) LIKE '%AMMAN%' THEN 'Jordan' WHEN UPPER(Location) LIKE '%KAZAKHSTAN%' THEN 'Kazakhstan' WHEN UPPER(Location) LIKE '%KENYA%' THEN 'Kenya' --Korea WHEN UPPER(Location) LIKE '%NORTH KOREA%' THEN 'North Korea' WHEN UPPER(Location) LIKE '%Pyongyang%' THEN 'North Korea' WHEN UPPER(Location) LIKE '%SOUTH KOREA%' THEN 'South Korea' WHEN UPPER(Location) LIKE '%SEOUL%' THEN 'South Korea' WHEN UPPER(Location) LIKE '%KOREA%' THEN 'South Korea' WHEN UPPER(Location) LIKE '%KUWAIT%' THEN 'Kuwait' WHEN UPPER(Location) LIKE '%KYRGYZSTAN%' THEN 'Kyrgyzstan' WHEN UPPER(Location) LIKE '%LATVIA%' THEN 'Latvia' WHEN UPPER(Location) LIKE '%LEBANON%' THEN 'Lebanon' WHEN UPPER(Location) LIKE '%LIBYA%' THEN 'Libya' WHEN UPPER(Location) LIKE '%LIECHTENSTEIN%' THEN 'Liechtenstein' WHEN UPPER(Location) LIKE '%LITHUANIA%' THEN 'Lithuania' WHEN UPPER(Location) LIKE '%LUXEMBOURG%' THEN 'Luxembourg' WHEN UPPER(Location) LIKE '%MACEDONIA%' THEN 'Macedonia' WHEN UPPER(Location) LIKE '%MALAYSIA%' THEN 'Malaysia' WHEN UPPER(Location) LIKE '%PENANG%' THEN 'Malaysia' WHEN UPPER(Location) LIKE '%MALDIVES%' THEN 'Maldives' WHEN UPPER(Location) LIKE '%MALTA%' THEN 'Malta' WHEN UPPER(Location) LIKE '%MAURITANIA%' THEN 'Mauritania' WHEN UPPER(Location) LIKE '%MAURITIUS%' THEN 'Mauritius' WHEN UPPER(Location) LIKE '%NEW MEXICO%' THEN 'United States' WHEN UPPER(Location) LIKE '%MEXICO%' THEN 'Mexico' WHEN UPPER(Location) LIKE '%MEXICALI%' THEN 'Mexico' WHEN UPPER(Location) LIKE '%MOLDOVA%' THEN 'Moldova' WHEN UPPER(Location) LIKE '%MONGOLIA%' THEN 'Mongolia' WHEN UPPER(Location) LIKE '%MONTENEGRO%' THEN 'Montenegro' WHEN UPPER(Location) LIKE '%MOROCCO%' THEN 'Morocco' WHEN UPPER(Location) LIKE '%TIZNIT%' THEN 'Morocco' WHEN UPPER(Location) LIKE '%MOZAMBIQUE%' THEN 'Mozambique' WHEN UPPER(Location) LIKE '%MYANMAR%' THEN 'Myanmar' WHEN UPPER(Location) LIKE '%NAMIBIA%' THEN 'Namibia' WHEN UPPER(Location) LIKE '%NEPAL%' THEN 'Nepal' WHEN UPPER(Location) LIKE '%KATHMANDU%' THEN 'Nepal' -- Netherlands WHEN UPPER(Location) LIKE '%NETHERLANDS%' THEN 'Netherlands' WHEN UPPER(Location) LIKE '%AMSTERDAM%' THEN 'Netherlands' WHEN UPPER(Location) LIKE '%ARUBA%' THEN 'Netherlands' WHEN UPPER(Location) LIKE '%NEW CALEDONIA%' THEN 'New Caledonia' -- New Zealand WHEN UPPER(Location) LIKE '%NEW ZEALAND%' THEN 'New Zealand' WHEN UPPER(Location) LIKE '%AUCKLAND%' THEN 'New Zealand' WHEN UPPER(Location) LIKE '%CHRISTCHURCH%' THEN 'New Zealand' WHEN UPPER(Location) LIKE '%NICARAGUA%' THEN 'Nicaragua' -- Nigeria must precede Niger WHEN UPPER(Location) LIKE '%NIGERIA%' THEN 'Nigeria' WHEN UPPER(Location) LIKE '%NIGER%' THEN 'Niger' WHEN UPPER(Location) LIKE '%NORWAY%' THEN 'Norway' WHEN UPPER(Location) LIKE '%SVALBARD%' THEN 'Norway' WHEN UPPER(Location) LIKE '%OMAN%' THEN 'Oman' WHEN UPPER(Location) LIKE '%PAKISTAN%' THEN 'Pakistan' WHEN UPPER(Location) LIKE '%KARACHI%' THEN 'Pakistan' WHEN UPPER(Location) LIKE '%LAHORE%' THEN 'Pakistan' WHEN UPPER(Location) LIKE '%PALESTINE, AR%' THEN 'United States' WHEN UPPER(Location) LIKE '%PALESTINE%' THEN 'Palestine' WHEN UPPER(Location) LIKE '%PALESTIN%' THEN 'Palestine' WHEN UPPER(Location) LIKE '%PANAMA%' THEN 'Panama' WHEN UPPER(Location) LIKE '%PARAGUAY%' THEN 'Paraguay' WHEN UPPER(Location) LIKE '%PERU%' THEN 'Peru' WHEN UPPER(Location) LIKE '%PHILIPPINES%' THEN 'Philippines' WHEN UPPER(Location) LIKE '%MAKATI CITY%' THEN 'Philippines' WHEN UPPER(Location) LIKE '%POLAND%' THEN 'Poland' WHEN UPPER(Location) LIKE '%KRAKOW%' THEN 'Poland' WHEN UPPER(Location) LIKE '%WARSAW%' THEN 'Poland' -- PORTUGAL WHEN UPPER(Location) LIKE '%PORTUGAL%' THEN 'Portugal' WHEN UPPER(Location) LIKE '%LISBON%' THEN 'Portugal' WHEN UPPER(Location) LIKE '%PUERTO RICO%' THEN 'Puerto Rico' WHEN UPPER(Location) LIKE '%QATAR%' THEN 'Qatar' WHEN UPPER(Location) LIKE '%ROMANIA%' THEN 'Romania' WHEN Location LIKE '%România%' THEN 'Romania' WHEN Location LIKE '%românia%' THEN 'Romania' WHEN UPPER(Location) LIKE '%RUSSIA%' THEN 'Russia' WHEN UPPER(Location) LIKE '%KRASNOYARSK%' THEN 'Russia' WHEN UPPER(Location) LIKE '%MOSCOW%' THEN 'Russia' WHEN UPPER(Location) LIKE '%SAINT-PETERSBURG%' THEN 'Russia' WHEN UPPER(Location) LIKE '%SIBERIA%' THEN 'Russia' -- SAMOA WHEN UPPER(Location) LIKE '%AMERICAN SAMOA%' THEN 'United States' WHEN UPPER(Location) LIKE '%SAMOA%' THEN 'Samoa' WHEN UPPER(Location) LIKE '%SAN MARINO%' THEN 'San Marino' WHEN UPPER(Location) LIKE '%SAUDI ARABIA%' THEN 'Saudi Arabia' WHEN UPPER(Location) LIKE '%SENEGAL%' THEN 'Senegal' WHEN UPPER(Location) LIKE '%SERBIA%' THEN 'Serbia' WHEN UPPER(Location) LIKE '%SEYCHELLES%' THEN 'Seychelles' WHEN UPPER(Location) LIKE '%SIERRA LEONE%' THEN 'Sierra Leone' WHEN UPPER(Location) LIKE '%SINGAPORE%' THEN 'Singapore' WHEN UPPER(Location) LIKE '%SLOVAKIA%' THEN 'Slovakia' WHEN UPPER(Location) LIKE '%SLOVENIA%' THEN 'Slovenia' WHEN UPPER(Location) LIKE '%SOUTH AFRICA%' THEN 'South Africa' WHEN UPPER(Location) LIKE '%SANDTON%' THEN 'South Africa' -- Spain WHEN UPPER(Location) LIKE '%SPAIN%' THEN 'Spain' WHEN lower(Location) LIKE lower('%España%') THEN 'Spain' WHEN UPPER(Location) LIKE '%ALICANTE%' THEN 'Spain' WHEN UPPER(Location) LIKE '%BARCELONA%' THEN 'Spain' WHEN UPPER(Location) LIKE '%CANARIA%' THEN 'Spain' WHEN UPPER(Location) LIKE '%CANARY%' THEN 'Spain' WHEN UPPER(Location) LIKE '%CATALONIA%' THEN 'Spain' WHEN UPPER(Location) LIKE '%VALENCIA%' THEN 'Spain' WHEN UPPER(Location) LIKE '%SRI LANKA%' THEN 'Sri Lanka' WHEN UPPER(Location) LIKE '%BATTARAMULLA%' THEN 'Sri Lanka' WHEN UPPER(Location) LIKE '%SUDAN%' THEN 'Sudan' WHEN UPPER(Location) LIKE '%SWAZILAND%' THEN 'Swaziland' WHEN UPPER(Location) LIKE '%SWEDEN%' THEN 'Sweden' WHEN UPPER(Location) LIKE '%SOLNA%' THEN 'Sweden' WHEN UPPER(Location) LIKE '%SWITZERLAND%' THEN 'Switzerland' WHEN UPPER(Location) LIKE '%SWIZERLAND%' THEN 'Switzerland' WHEN UPPER(Location) LIKE '%SYRIA%' THEN 'Syria' WHEN UPPER(Location) LIKE '%TAIWAN%' THEN 'Taiwan' WHEN UPPER(Location) LIKE '%TAJIKISTAN%' THEN 'Tajikistan' WHEN UPPER(Location) LIKE '%TANZANIA%' THEN 'Tanzania' WHEN UPPER(Location) LIKE '%THAILAND%' THEN 'Thailand' WHEN UPPER(Location) LIKE '%TOGO%' THEN 'Togo' WHEN UPPER(Location) LIKE '%TRINIDAD AND%' THEN 'Trinidad and Tobago' WHEN UPPER(Location) LIKE '%TOBAGO%' THEN 'Trinidad and Tobago' WHEN UPPER(Location) LIKE '%TUNISIA%' THEN 'Tunisia' WHEN UPPER(Location) LIKE '%TURKEY%' THEN 'Turkey' WHEN UPPER(Location) LIKE '%TURKIYE%' THEN 'Turkey' WHEN UPPER(Location) LIKE '%ADANA%' THEN 'Turkey' WHEN UPPER(Location) LIKE '%ANKARA%' THEN 'Turkey' WHEN UPPER(Location) LIKE '%BURSA%' THEN 'Turkey' WHEN UPPER(Location) LIKE '%ISTANBUL%' THEN 'Turkey' WHEN UPPER(Location) LIKE '%TURKMENISTAN%' THEN 'Turkmenistan' WHEN UPPER(Location) LIKE '%UGANDA%' THEN 'Uganda' WHEN UPPER(Location) LIKE '%KAMPALA%' THEN 'Uganda' WHEN UPPER(Location) LIKE '%UKRAINE%' THEN 'Ukraine' WHEN UPPER(Location) LIKE '%KHARKOV%' THEN 'Ukraine' WHEN UPPER(Location) LIKE '%KIEV%' THEN 'Ukraine' WHEN UPPER(Location) LIKE '%KYIV%' THEN 'Ukraine' WHEN UPPER(Location) LIKE '%UNITED ARAB EMIRATES%' THEN 'United Arab Emirates' WHEN UPPER(Location) LIKE '%DUBAI%' THEN 'United Arab Emirates' -- United Kingdom & Ireland WHEN UPPER(Location) LIKE '%GIBRALTAR%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%ISLE OF MAN%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%TUVALU%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%NEW ENGLAND%' THEN 'United States' WHEN UPPER(Location) LIKE '%ENGLAND, AR%' THEN 'United States' WHEN UPPER(Location) LIKE '%ENGLAND%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%NORTHERN IRELAND%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%SCOTLAND%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%WALES%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%UNITED KINGDOM%' THEN 'United Kingdom' WHEN Location LIKE '%, UK%' THEN 'United Kingdom' WHEN Location LIKE '%,UK%' THEN 'United Kingdom' WHEN Location LIKE '% UK%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%BRIGHTON, AL%' THEN 'United States' -- Assumed Brighton WHEN UPPER(Location) LIKE '%BRIGHTON%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%EDINBURGH%' THEN 'United Kingdom' -- Must be after Canada, London, Ontario WHEN UPPER(Location) LIKE '%LONDON%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%MANCHESTER%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%NEWCASTLE%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%NOTTINGHAM%' THEN 'United Kingdom' -- Must be after Canada, Peterborough, Ontario WHEN UPPER(Location) LIKE '%PETERBOROUGH%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%SWANSEA%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%WARRINGTON%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%IRELAND%' THEN 'Ireland, Republic of' WHEN UPPER(Location) LIKE '%DUBLIN%' THEN 'Ireland, Republic of' -- United States -- Note the two letter abbreviations have to be near the end of the entire search WHEN UPPER(Location) LIKE '%UNITED STATES%' THEN 'United States' WHEN lower(Location) LIKE lower('%United Sates%') THEN 'United States - Typo United Sates' WHEN UPPER(Location) LIKE '%USA%' THEN 'United States' WHEN UPPER(Location) LIKE '%US OF A%' THEN 'United States' WHEN UPPER(Location) LIKE '%US VIRGIN ISLANDS%' THEN 'United States' WHEN UPPER(Location) LIKE '%VIRGIN ISLANDS%' THEN 'United Kingdom' WHEN UPPER(Location) LIKE '%ALLAHABAD%' THEN 'India' WHEN UPPER(Location) LIKE '%ALABAMA%' THEN 'United States' WHEN Location LIKE '%, AL%' THEN 'United States' WHEN Location LIKE '%,AL%' THEN 'United States' WHEN Location LIKE '% AL%' THEN 'United States' WHEN UPPER(Location) LIKE '%ALASKA%' THEN 'United States' WHEN UPPER(Location) LIKE '%, AK%' THEN 'United States' WHEN UPPER(Location) LIKE '%,AK%' THEN 'United States' WHEN UPPER(Location) LIKE '% AK%' THEN 'United States' WHEN UPPER(Location) LIKE '%ARIZONA%' THEN 'United States' WHEN UPPER(Location) LIKE '%, AZ%' THEN 'United States' WHEN UPPER(Location) LIKE '%,AZ%' THEN 'United States' WHEN UPPER(Location) LIKE '% AZ%' THEN 'United States' WHEN UPPER(Location) LIKE '%ARKANSAS%' THEN 'United States' WHEN UPPER(Location) LIKE '%, AR%' THEN 'United States' WHEN UPPER(Location) LIKE '%,AR%' THEN 'United States' WHEN Location LIKE '% AR' THEN 'United States' WHEN UPPER(Location) LIKE '%CALIFORNIA%' THEN 'United States' WHEN UPPER(Location) LIKE '%VENICE,CA%' THEN 'United States' -- Must follow CANADA WHEN UPPER(Location) LIKE '%, CA' THEN 'United States' WHEN UPPER(Location) LIKE '%,CA' THEN 'United States' WHEN UPPER(Location) LIKE '% CA' THEN 'United States' WHEN UPPER(Location) LIKE '%COLORADO%' THEN 'United States' WHEN UPPER(Location) LIKE '%CONNECTICUT%' THEN 'United States' WHEN UPPER(Location) LIKE '%DELAWARE%' THEN 'United States' WHEN UPPER(Location) LIKE '%DISTRICT OF COLUMBIA%' THEN 'United States' WHEN UPPER(Location) LIKE '%FLORIDA%' THEN 'United States' -- US Georgia WHEN UPPER(Location) LIKE '%TBILISI%' THEN 'Georgia' WHEN UPPER(Location) LIKE '%KHELVACHAURI%' THEN 'Georgia' WHEN UPPER(Location) LIKE '%ATHENS, GEORGIA%' THEN 'United States' WHEN UPPER(Location) LIKE '%ATLANTA, GEORGIA%' THEN 'United States' -- Assume leftover Georgia and GA is United States WHEN UPPER(Location) LIKE '%GEORGIA%' THEN 'United States or Georgia' WHEN UPPER(Location) LIKE '%, GA%' THEN 'United States' WHEN UPPER(Location) LIKE '%,GA%' THEN 'United States' WHEN UPPER(Location) LIKE '% GA%' THEN 'United States' WHEN UPPER(Location) LIKE '%HAWAII%' THEN 'United States' WHEN UPPER(Location) LIKE '%, HI%' THEN 'United States' WHEN UPPER(Location) LIKE '%,HI%' THEN 'United States' WHEN UPPER(Location) LIKE '% HI%' THEN 'United States' WHEN UPPER(Location) LIKE '%IDAHO%' THEN 'United States' WHEN UPPER(Location) LIKE '%ILLINOIS%' THEN 'United States' -- Should not find any. This is the first line for India WHEN UPPER(Location) LIKE '%INDIANA%' THEN 'United States' WHEN UPPER(Location) LIKE '%IOWA%' THEN 'United States' WHEN UPPER(Location) LIKE '%KANSAS%' THEN 'United States' WHEN UPPER(Location) LIKE '%KENTUCKY%' THEN 'United States' WHEN UPPER(Location) LIKE '%LOUISIANA%' THEN 'United States' WHEN UPPER(Location) LIKE '%MAINE%' THEN 'United States' WHEN UPPER(Location) LIKE '%MARYLAND%' THEN 'United States' WHEN UPPER(Location) LIKE '%MASSACHUSETTS%' THEN 'United States' -- Must be after Malaysia, Maine WHEN UPPER(Location) LIKE '%, MA%' THEN 'United States' WHEN UPPER(Location) LIKE '%,MA%' THEN 'United States' WHEN UPPER(Location) LIKE '% MA%' THEN 'United States' WHEN UPPER(Location) LIKE '%MICHIGAN%' THEN 'United States' WHEN UPPER(Location) LIKE '%MINNESOTA%' THEN 'United States' WHEN UPPER(Location) LIKE '%MISSISSIPPI%' THEN 'United States' WHEN UPPER(Location) LIKE '%MISSOURI%' THEN 'United States' WHEN UPPER(Location) LIKE '%MONTANA%' THEN 'United States' WHEN UPPER(Location) LIKE '%NEBRASKA%' THEN 'United States' WHEN UPPER(Location) LIKE '%NEVADA%' THEN 'United States' -- Should not find any. This is the before England WHEN UPPER(Location) LIKE '%NEW ENGLAND%' THEN 'United States' WHEN UPPER(Location) LIKE '%NEW HAMPSHIRE%' THEN 'United States' WHEN UPPER (Location) LIKE '%NEW JERSEY%' THEN 'United States' WHEN Location LIKE '%, NJ%' THEN 'United States' WHEN Location LIKE '% NJ%' THEN 'United States' WHEN Location LIKE '%NJ%' THEN 'United States' -- Should not find any. This is the first line for Mexico WHEN UPPER(Location) LIKE '%NEW MEXICO%' THEN 'United States' -- US New York State WHEN UPPER(Location) LIKE '%NEW YORK%' THEN 'United States' WHEN UPPER(Location) LIKE '%LONG ISLAND%' THEN 'United States' WHEN UPPER(Location) LIKE '%ROCHESTER NY%' THEN 'United States' WHEN UPPER(Location) LIKE '%NYC%' THEN 'United States' WHEN Location LIKE '%, NY%' THEN 'United States' WHEN Location LIKE '%,NY%' THEN 'United States' WHEN Location LIKE '%NY%' THEN 'United States' WHEN UPPER(Location) LIKE '%NORTH CAROLINA%' THEN 'United States' WHEN UPPER(Location) LIKE '%, NC%' THEN 'United States' WHEN UPPER(Location) LIKE '%,NC%' THEN 'United States' WHEN UPPER(Location) LIKE '% NC%' THEN 'United States' WHEN UPPER(Location) LIKE '%NORTH DAKOTA%' THEN 'United States' WHEN UPPER(Location) LIKE '%, ND%' THEN 'United States' WHEN UPPER(Location) LIKE '%,ND%' THEN 'United States' WHEN UPPER(Location) LIKE '% ND' THEN 'United States' WHEN UPPER(Location) LIKE '%OHIO%' THEN 'United States' WHEN lower(Location) LIKE lower('%Okinawa%') THEN 'Japan' WHEN UPPER(Location) LIKE '%OKLAHOMA%' THEN 'United States' WHEN UPPER(Location) LIKE '%, OK%' THEN 'United States' WHEN UPPER(Location) LIKE '%,OK%' THEN 'United States' WHEN UPPER(Location) LIKE '% OK' THEN 'United States' WHEN UPPER(Location) LIKE '%OREGON%' THEN 'United States' WHEN UPPER(Location) LIKE '%SALEM OR%' THEN 'United States' WHEN UPPER(Location) LIKE '%PENNSYLVANIA%' THEN 'United States' WHEN UPPER(Location) LIKE '%RHODE ISLAND%' THEN 'United States' WHEN UPPER(Location) LIKE '%PROVIDENCE RI%' THEN 'United States' WHEN UPPER(Location) LIKE '%SOUTH CAROLINA%' THEN 'United States' WHEN UPPER(Location) LIKE '%SOUTH DAKOTA%' THEN 'United States' WHEN UPPER(Location) LIKE '%TENNESSEE%' THEN 'United States' WHEN UPPER(Location) LIKE '%TEXAS%' THEN 'United States' -- Includes Earth, TX WHEN UPPER(Location) LIKE '%, TX%' THEN 'United States' WHEN UPPER(Location) LIKE '%,TX%' THEN 'United States' WHEN UPPER(Location) LIKE '% TX' THEN 'United States' WHEN UPPER(Location) LIKE '%UTAH%' THEN 'United States - %UTAH%' WHEN UPPER(Location) LIKE '%, UT%' THEN 'United States - %, UT%' WHEN UPPER(Location) LIKE '%,UT%' THEN 'United States - %,UT%' WHEN UPPER(Location) LIKE '% UT' THEN 'United States - % UT' WHEN UPPER(Location) LIKE '%VERMONT%' THEN 'United States' WHEN UPPER(Location) LIKE '%VIRGINIA%' THEN 'United States' WHEN Location LIKE '%, VA' THEN 'United States' WHEN Location LIKE '%,VA' THEN 'United States' WHEN Location LIKE '%VA' THEN 'United States' WHEN UPPER(Location) LIKE '%WASHINGTON%' THEN 'United States' WHEN UPPER(Location) LIKE '%WISCONSIN%' THEN 'United States' WHEN UPPER(Location) LIKE '%WYOMING%' THEN 'United States' -- US cities WHEN UPPER(Location) LIKE '%BOSTON%' THEN 'United States' WHEN UPPER(Location) LIKE '%CHICAGO%' THEN 'United States' WHEN UPPER(Location) LIKE '%HOLLYWOOD%' THEN 'United States' WHEN UPPER(Location) LIKE '%LOS ANGELES%' THEN 'United States' WHEN UPPER(Location) LIKE '%PITTSBURGH%' THEN 'United States' WHEN UPPER(Location) LIKE '%SALT LAKE CITY%' THEN 'United States' WHEN UPPER(Location) LIKE '%SAN FRAN%' THEN 'United States' -- US Misc WHEN UPPER(Location) LIKE '%QUAD CITIES%' THEN 'United States' WHEN UPPER(Location) LIKE '%ROCKY MOUNTAINS%' THEN 'United States' WHEN UPPER(Location) LIKE '%SILICON VALLEY%' THEN 'United States' WHEN UPPER(Location) LIKE '%SOCAL%' THEN 'United States' -- Assumed SUTTON WHEN UPPER(Location) LIKE '%SUTTON%' THEN 'United States' WHEN UPPER(Location) LIKE '%VAN NUYS%' THEN 'United States' WHEN UPPER(Location) LIKE '%URUGUAY%' THEN 'Uruguay' WHEN UPPER(Location) LIKE '%USSR%' THEN 'USSR' WHEN UPPER(Location) LIKE '%UZBEKISTAN%' THEN 'Uzbekistan' WHEN UPPER(Location) LIKE '%VENEZUELA%' THEN 'Venezuela' -- Vietnam WHEN UPPER(Location) LIKE '%VIETNAM%' THEN 'Vietnam' WHEN UPPER(Location) LIKE '%VIET NAM%' THEN 'Vietnam' WHEN UPPER(Location) LIKE '%HO CHI MINH CITY%' THEN 'Vietnam' WHEN UPPER(Location) LIKE '%YEMEN%' THEN 'Yemen' WHEN UPPER(Location) LIKE '%ZAMBIA%' THEN 'Zambia' WHEN UPPER(Location) LIKE '%ZIMBABWE%' THEN 'Zimbabwe' WHEN UPPER(Location) LIKE '%NORTH AMERICA%' THEN 'North America' WHEN UPPER(Location) LIKE '%SOUTH AMERICA%' THEN 'South America' WHEN lower(Location) LIKE lower('%Central America%') THEN 'Central America' WHEN lower(Location) LIKE lower('%Latin America%') THEN 'Latin America' WHEN UPPER(Location) LIKE '%ASIA%' THEN 'Asia' WHEN UPPER(Location) LIKE '%EUROPE%' THEN 'Europe' WHEN UPPER(Location) LIKE '%AFRICA%' THEN 'Africa' WHEN lower(Location) LIKE lower('America') THEN 'United States - equals America' WHEN lower(Location) LIKE lower('%America%') THEN 'United States - fallthrough America' WHEN lower(Location) LIKE lower('%Oceania%') THEN 'Oceania' WHEN lower(Location) LIKE lower('%Atlantic Ocean%') THEN 'Ocean, Atlantic' WHEN lower(Location) LIKE lower('%Somewhere in the Mid-Atlantic%') THEN 'Ocean, Atlantic' WHEN lower(Location) LIKE lower('%Pacific Ocean%') THEN 'Ocean, Pacific' WHEN lower(Location) LIKE lower('%the Pacific%') THEN 'Ocean, Pacific' WHEN lower(Location) LIKE lower('%The Ocean%') THEN 'Ocean' WHEN lower(Location) = lower('Ocean') THEN 'Ocean' -- WHEN UPPER(Location) LIKE '%HAMILTON%' THEN 'Unknown Hamilton' WHEN UPPER(Location) LIKE '%CANABIA%' THEN 'Offbeat - Canabia' WHEN lower(Location) LIKE lower('%Cascadia%') THEN 'Offbeat - Cascadia' WHEN lower(Location) LIKE lower('%Computer%') THEN 'Offbeat - Computer' WHEN UPPER(Location) LIKE '%DESK%' THEN 'Offbeat - Desk' WHEN lower(Location) LIKE lower('%Internet%') THEN 'Offbeat - Internet' WHEN lower(Location) LIKE lower('%Cyberspace%') THEN 'Offbeat - Cyberspace' WHEN lower(Location) LIKE lower('%World%') THEN 'Offbeat - World' -- Must follow Earth, TX WHEN UPPER(Location) LIKE '%EARTH%' THEN 'Offbeat - Earth' WHEN UPPER(Location) LIKE '%PLANET%' THEN 'Offbeat - Planet' WHEN lower(Location) LIKE lower('%Sol%') THEN 'Offbeat - Sol' WHEN lower(Location) LIKE lower('%Heaven%') THEN 'Offbeat - Heaven' WHEN lower(Location) LIKE lower('%Milky Way%') THEN 'Offbeat - Milky Way' WHEN UPPER(Location) LIKE '%UNIVERSE%' THEN 'Offbeat - Universe' WHEN lower(Location) LIKE '%outer space%' THEN 'Offbeat - space' WHEN lower(Location) LIKE '%space%' THEN 'Offbeat - space' WHEN UPPER(Location) LIKE '%WWW%' THEN 'Offbeat - Website' WHEN lower(Location) LIKE '%behind%' THEN 'Offbeat - behind' WHEN lower(Location) LIKE 'hidden' THEN 'Offbeat - hidden' WHEN lower(Location) = 'none' THEN 'Offbeat - none' -- there is a None, Italy WHEN lower(Location) LIKE '%nowhere%' THEN 'Offbeat - nowhere' ---- there is a Nowhere, OK WHEN lower(Location) LIKE '%somewhere%over the rainbow%' THEN 'Offbeat - somewhere over the rainbow' WHEN lower(Location) LIKE lower('somewhere') THEN 'Offbeat - somewhere - equals somewhere' WHEN lower(Location) LIKE lower('(somewhere)') THEN 'Offbeat - somewhere - equals (somewhere)' WHEN lower(Location) LIKE lower('%Somewhere around%') THEN 'Offbeat - Somewhere around' WHEN lower(Location) LIKE lower('%Somewhere between%') THEN 'Offbeat - Somewhere between' WHEN lower(location) LIKE lower('%Somewhere in the US%') THEN 'United States' WHEN lower(location) LIKE lower('%An airport terminal somewhere%') THEN 'Offbeat - Somewhere - An airport terminal somewhere' WHEN lower(location) LIKE lower('%Somewhere East of Where You Are%') THEN 'Offbeat - Somewhere East of Where You Are' WHEN lower(location) LIKE lower('%Somewhere far beyond...%') THEN 'Offbeat - Somewhere far beyond...' WHEN lower(location) LIKE lower('%somewhere good%') THEN 'Offbeat - somewhere good' WHEN lower(location) LIKE lower('%Somewhere I Belong%') THEN 'Offbeat - Somewhere I Belong' WHEN lower(location) LIKE lower('%Somewhere I guess...%') THEN 'Offbeat - Somewhere I guess...' WHEN lower(location) LIKE lower('%Somewhere in 2011%') Then 'Offbeat - Somewhere in 2011' when lower(location) LIKE lower('%Somewhere in Null....%') Then 'Offbeat - Somewhere in Null....' when lower(location) LIKE lower('%somewhere in ny... maybe.%') Then 'Offbeat - somewhere in ny... maybe.' WHEN lower(Location) LIKE '%somewhere%' THEN 'Offbeat - somewhere' ELSE 'Unknown' END AS Country FROM Users WHERE LEN(Location) > 1 and Reputation >= ##minimumReputation## ) SELECT UsersCountry.Country, count(1) AS 'count' FROM UsersCountry GROUP BY UsersCountry.Country --ORDER BY count(1) desc ORDER BY Country asc