1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

--- Which provinces are the users from?
-- Based on their filled in location

-- Removing more populous areas may speed up the search

WITH UsersProvince AS (
SELECT 
          CASE
                
                --10062
                WHEN UPPER(Location) LIKE '%AUSTRALIA%' THEN 'Z - Not Canada'
                
                --2012
                WHEN UPPER(Location) LIKE '%AUSTRIA%' THEN 'Z - Not Canada'
                
                 --8304
                WHEN UPPER(Location) LIKE '%BRAZIL%' THEN 'Z - Not Canada'
                
                --446
                --WHEN UPPER(Location) LIKE '%BRASIL%' THEN 'Z - Not Canada'
                
                --11356
                --WHEN UPPER(Location) LIKE '%CHINA%' THEN 'Z - Not Canada'
                
                --8924
                WHEN UPPER(Location) LIKE '%FRANCE%' THEN 'Z - Not Canada'
                
                 --14812
                WHEN UPPER(Location) LIKE '%GERMANY%' THEN 'Z - Not Canada'
                
                --47261 India (Indiana / Indianapolis)
                WHEN UPPER(Location) LIKE '%INDIA%' THEN 'Z - Not Canada'
                
                --2343
                WHEN UPPER(Location) LIKE '%MEXICO%' THEN 'Z - Not Canada'
                
                --8944
                WHEN UPPER(Location) LIKE '%RUSSIA%' THEN 'Z - Not Canada'
                
                --23202
                WHEN UPPER(Location) LIKE '%UNITED KINGDOM%' THEN 'Z - Not Canada'
                
                --3177
                WHEN Location LIKE '%UK%' THEN 'Z - Not Canada'
                
                --11
                WHEN UPPER(Location) LIKE '%U.K%' THEN 'Z - Not Canada'
                               
                --11532
                WHEN UPPER(Location) LIKE '%UNITED STATES%' THEN 'Z - Not Canada'
                          
                --58
                WHEN UPPER(Location) LIKE '%U.S%' THEN 'Z - Not Canada'
                
                --8
                WHEN UPPER(Location) LIKE '%US OF A%' THEN 'Z - Not Canada'
               
                --1346
                WHEN Location LIKE '%, US%' THEN 'Z - Not Canada'
                
                -- Check for Canada province and territory abbreviations
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' AB' THEN 'Alberta'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' BC' THEN 'British Columbia'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' MB' THEN 'Manitoba'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' NB' THEN 'New Brunswick'
                -- NL is The Netherlands
                --WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' NL' THEN 'Newfoundland and Labrador'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' NT' THEN 'Northwest Territories'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' NS' THEN 'Nova Scotia'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' NU' THEN 'Nunavut'
                
                -- See in Ontario section
                --WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' ON' THEN 'Ontario - leading space ON RTRIM'
                
                -- PE is Peru
                --WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' PE' THEN 'Prince Edward Island'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' QC' THEN 'Quebec'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' SK' THEN 'Saskatchewan'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' YT' THEN 'Yukon Territory'
                
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',AB' THEN 'Alberta'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',BC' THEN 'British Columbia'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',MB' THEN 'Manitoba'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',NB' THEN 'New Brunswick'
                -- NL is The Netherlands
                --WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',NL' THEN 'Newfoundland and Labrador'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',NT' THEN 'Northwest Territories'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',NS' THEN 'Nova Scotia'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',NU' THEN 'Nunavut'
                
                -- See in Ontario section
                --WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',ON' THEN 'Ontario - ,ON RTRIM'
                
                -- PE is Peru
                --WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',PE' THEN 'Prince Edward Island'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',QC' THEN 'Quebec'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',SK' THEN 'Saskatchewan'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',YT' THEN 'Yukon Territory'
                
 
                --B.C.
                WHEN UPPER(Location) LIKE '%MEXICALI%' THEN 'Z - Not Canada'
                
                WHEN UPPER(Location) LIKE '%BRITISH COLUMBIA%' THEN 'British Columbia'
                WHEN Location LIKE '%B.C.%' THEN 'British Columbia'
                
                WHEN UPPER(Location) LIKE '%, BC%' THEN 'British Columbia'
                WHEN UPPER(Location) LIKE '%,BC%' THEN 'British Columbia'
                WHEN UPPER(Location) LIKE '% BC%' THEN 'British Columbia'
                
                --WHEN UPPER(Location) LIKE '%VANCOUVER, B%' THEN 'British Columbia'
                --WHEN UPPER(Location) LIKE '%VANCOUVER,B%' THEN 'British Columbia'
                --WHEN UPPER(Location) LIKE '%VANCOUVER B%' THEN 'British Columbia'
                
                WHEN UPPER(Location) LIKE '%VANCOUVER, C%' THEN 'British Columbia'
                WHEN UPPER(Location) LIKE '%VANCOUVER,C%' THEN 'British Columbia'
                WHEN UPPER(Location) LIKE '%VANCOUVER C%' THEN 'British Columbia'
                
                WHEN UPPER(Location) LIKE '%VANCOUVER IS%' THEN 'British Columbia'
                
                WHEN UPPER(Location) LIKE '%VANCOUVER, W%' THEN 'Z - Not Canada'
                WHEN UPPER(Location) LIKE '%VANCOUVER,W%' THEN 'Z - Not Canada'
                WHEN UPPER(Location) LIKE '%VANCOUVER W%' THEN 'Z - Not Canada'

                WHEN UPPER(Location) LIKE '%CALGARY%' THEN 'Alberta'
                WHEN UPPER(Location) LIKE '%ALBERTA%' THEN 'Alberta'
                WHEN Location LIKE '%, AB%' THEN 'Alberta'
                WHEN Location LIKE '%,AB%' THEN 'Alberta'
                WHEN Location LIKE '% AB%' THEN 'Alberta'

                -- sky - Must precede sk for Saskatchewan
                WHEN Location LIKE '%Cesky Tesin%' THEN 'Z - Not Canada'
                WHEN Location LIKE '%El Mosky%' THEN 'Z - Not Canada'
                WHEN Location LIKE '%Jyväskylä%' THEN 'Z - Not Canada'
                WHEN Location LIKE '%Jyvaskyla%' THEN 'Z - Not Canada '
                WHEN Location LIKE '%Sandusky%' THEN 'Z - Not Canada'
                WHEN Location LIKE '%Khmelnitsky%' THEN 'Z - Not Canada'
                WHEN Location LIKE '%Zhukovsky%' THEN 'Z - Not Canada'
                WHEN UPPER(Location) LIKE '%SKY%' THEN 'Z - Unknown'      
       
                
                WHEN UPPER(Location) LIKE '%SASKATCHEWAN%' THEN 'Saskatchewan'
                WHEN UPPER(Location) LIKE '%SASK' THEN 'Saskatchewan'
                WHEN Location LIKE '%, SK%' THEN 'Saskatchewan'
                WHEN Location LIKE '%,SK%' THEN 'Saskatchewan'
                WHEN Location LIKE '% SK%' THEN 'Saskatchewan'
                

                WHEN UPPER(Location) LIKE '%WINNIPEG%' THEN 'Manitoba'
                WHEN UPPER(Location) LIKE '%MANITOBA%' THEN 'Manitoba'
                WHEN UPPER(Location) LIKE '%, MB%' THEN 'Manitoba'
                WHEN UPPER(Location) LIKE '%,MB%' THEN 'Manitoba'
                WHEN UPPER(Location) LIKE '% MB%' THEN 'Manitoba'
                
                ----- ONTARIO ------
                
                --Acton
                WHEN UPPER(Location) LIKE '%ACTON, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%ACTON,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%ACTON ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%ACTON, CAN%' THEN 'Ontario' --Not California
                WHEN UPPER(Location) LIKE '%ACTON,CAN%' THEN 'Ontario' --Not California
                WHEN UPPER(Location) LIKE '%ACTON CAN%' THEN 'Ontario' --Not California
                
                -- Not Acton, CA nor Acton, MA nor Acton Vale, QC
                
                WHEN lower(Location) LIKE '%arnprior%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%AJAX%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%attawapiskat%' THEN 'Ontario'
                
                --Aurora
                WHEN UPPER(Location) LIKE '%AURORA, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%AURORA,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%AURORA ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%AURORA, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%AURORA,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%AURORA CA%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%aylmer' THEN 'Ontario'
                
                --Ayr
                WHEN lower(Location) LIKE '%ayr, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%ayr,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%ayr on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%ayr, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%ayr,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%ayr ca%' THEN 'Ontario'
                
                --Baltimore
                WHEN lower(Location) LIKE '%baltimore, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%baltimore,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%baltimore on%' THEN 'Ontario'
                
                -- not Baltimore, CA as there is Baltimore, NB
                
                WHEN UPPER(Location) LIKE '%BARRIE%' THEN 'Ontario'
                                
                
                --Belleville
                WHEN UPPER(Location) LIKE '%BELLEVILLE, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%BELLEVILLE,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%BELLEVILLE ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%BELLEVILLE, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%BELLEVILLE,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%BELLEVILLE CA%' THEN 'Ontario'
                
                --Not Belleville, IL / MI / NJ
                    
                WHEN UPPER(Location) LIKE '%BOWMANVILLE%' THEN 'Ontario' 
                
                --Bradford
                WHEN lower(Location) LIKE '%bradford, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%bradford,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%bradford on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%bradford, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%bradford,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%bradford ca%' THEN 'Ontario'
                
                
                WHEN UPPER(Location) LIKE '%BRAMPTON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%BRANTFORD%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%BROCKVILLE%' THEN 'Ontario'
                
                --Burlington
                WHEN UPPER(Location) LIKE '%BURLINGTON, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%BURLINGTON,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%BURLINGTON ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%BURLINGTON, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%BURLINGTON,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%BURLINGTON CA%' THEN 'Ontario'
                
                --Caledon
                WHEN lower(Location) LIKE '%caledonia%' THEN 'Z - Unknown'
                WHEN lower(Location) LIKE '%caledon%' THEN 'Ontario'
                
                
                --Cambridge
                WHEN UPPER(Location) LIKE '%CAMBRIDGE, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%CAMBRIDGE,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%CAMBRIDGE ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%CAMBRIDGE, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%CAMBRIDGE,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%CAMBRIDGE CA%' THEN 'Ontario'
                
                
                WHEN lower(Location) LIKE '%campbellville%' THEN 'Ontario'
                
                --Chatham, Ontario
                WHEN UPPER(Location) LIKE '%CHATHAM, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%CHATHAM,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%CHATHAM ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%CHATHAM, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%CHATHAM,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%CHATHAM CA%' THEN 'Ontario'
                
                -- Not Chatham, NJ / NY
             
                WHEN lower(Location) LIKE '%chatham-kent%' THEN 'Ontario'
               
                WHEN lower(Location) LIKE '%cobourg%' THEN 'Ontario'
                
                
                --Cochrane
                WHEN lower(Location) LIKE '%cochrane, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%cochrane,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%cochrane on%' THEN 'Ontario'
                
                -- not Cochrane, CA as there is Cochrane, Alberta
                
                                
                WHEN UPPER(Location) LIKE '%COE HILL%' THEN 'Ontario'
                
                
                --Colchester
                WHEN lower(Location) LIKE '%colchester, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%colchester,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%colchester on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%colchester, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%colchester,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%colchester ca%' THEN 'Ontario'
                
                --Not UK / CT
                
                
                WHEN UPPER(Location) LIKE '%COLLINGWOOD%' THEN 'Ontario'
                
                --Cornwall
                WHEN lower(Location) LIKE '%cornwall, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%cornwall,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%cornwall on%' THEN 'Ontario'
                
                -- not Cornwall, CA as there is Cornwall, PEI
                
                
                --Dundas
                WHEN lower(Location) LIKE '%dundas, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%dundas,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%dundas on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%dundas, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%dundas,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%dundas ca%' THEN 'Ontario'
                
                -- not Dundas, MN
                
                --Elgin
                WHEN lower(Location) LIKE '%elgin, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%elgin,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%elgin on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%elgin, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%elgin,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%elgin ca%' THEN 'Ontario'
                
                
                
                --Elmira
                WHEN UPPER(Location) LIKE '%ELMIRA, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%ELMIRA,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%ELMIRA ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%ELMIRA, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%ELMIRA,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%ELMIRA CA%' THEN 'Ontario'
                
                -- Not Elmira, NY
                
                WHEN lower(Location) LIKE '%elora%' THEN 'Ontario'
                
                --Essex
                WHEN lower(Location) LIKE '%essex, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%essex,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%essex on%' THEN 'Ontario'
                
                -- not Essex, CA as there is one in California
                WHEN lower(Location) LIKE '%essex, can%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%essex,can%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%essex can%' THEN 'Ontario'
                
                --Etobicoke was amalgamated into Toronto
                WHEN lower(Location) LIKE '%etobicoke%' THEN 'Ontario'
                
                --Exeter
                WHEN lower(Location) LIKE '%exeter, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%exeter,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%exeter on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%exeter, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%exeter,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%exeter ca%' THEN 'Ontario'
                
                --Fergus
                WHEN lower(Location) LIKE '%fergus, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%fergus,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%fergus on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%fergus, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%fergus,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%fergus ca%' THEN 'Ontario'
                
                -- not Fergus Falls nor Ferguson
                 
                --Fonthill
                WHEN lower(Location) LIKE '%fonthill, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%fonthill,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%fonthill on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%fonthill, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%fonthill,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%fonthill ca%' THEN 'Ontario'
                
                
                WHEN UPPER(Location) LIKE '%FORT ERIE%' THEN 'Ontario'
                
                
                --Georgetown
                WHEN lower(Location) LIKE '%georgetown, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%georgetown,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%georgetown on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%georgetown, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%georgetown,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%georgetown ca%' THEN 'Ontario'
                
                --Granton
                WHEN lower(Location) LIKE '%granton, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%granton,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%granton on%' THEN 'Ontario'
                
                -- not Granton, CA as there is Granton, NS
                
                
                WHEN lower(Location) LIKE '%greater toronto area%' THEN 'Ontario'
                WHEN Location LIKE '%GTA%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%guelph%' THEN 'Ontario'

                WHEN UPPER(Location) LIKE '%HAMILTON, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%HAMILTON,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%HAMILTON ON%' THEN 'Ontario'
                
                -- Ignoring the possibility of Hamilton California
                WHEN UPPER(Location) LIKE '%HAMILTON, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%HAMILTON,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%HAMILTON CA%' THEN 'Ontario'
                
                --Holland Landing
                WHEN lower(Location) LIKE '%holland landing, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%holland landing,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%holland landing on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%holland landing, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%holland landing,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%holland landing ca%' THEN 'Ontario'
                
                --Huntsville
                WHEN lower(Location) LIKE '%huntsville, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%huntsville,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%huntsville on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%huntsville, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%huntsville,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%huntsville ca%' THEN 'Ontario'
                
                --Kemptville
                WHEN lower(Location) LIKE '%kemptville, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%kemptville,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%kemptville on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%kemptville, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%kemptville,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%kemptville ca%' THEN 'Ontario'
                
                
                --Kenora
                WHEN lower(Location) LIKE '%kenora, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%kenora,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%kenora on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%kenora, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%kenora,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%kenora ca%' THEN 'Ontario'
                
                
                --Kingston
                WHEN UPPER(Location) LIKE '%KINGSTON, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%KINGSTON,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%KINGSTON ON%' THEN 'Ontario'
                
                -- Ignoring Kingston, CA that may be Kingston, NS
                WHEN UPPER(Location) LIKE '%KINGSTON, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%KINGSTON,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%KINGSTON CA%' THEN 'Ontario'
                
                --Kitchener
                WHEN UPPER(Location) LIKE '%KITCHENER%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%KW%' THEN 'Ontario' 
                
                --Lanark
                WHEN lower(Location) LIKE '%lanark, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%lanark,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%lanark on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%lanark, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%lanark,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%lanark ca%' THEN 'Ontario'
                
                
                --Leamington
                WHEN UPPER(Location) LIKE '%LEAMINGTON, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%LEAMINGTON,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%LEAMINGTON ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%LEAMINGTON, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%LEAMINGTON,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%LEAMINGTON CA%' THEN 'Ontario'
                
                
                --London
                WHEN UPPER(Location) LIKE '%LONDON, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%LONDON,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%LONDON ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%LONDON, CAN%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%LONDON,CAN%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%LONDON CAN%' THEN 'Ontario'

                --Maple
                WHEN lower(Location) LIKE '%maple, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%maple,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%maple on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%maple, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%maple,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%maple ca%' THEN 'Ontario'
                
                --Markham
                WHEN UPPER(Location) LIKE '%MARKHAM, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%MARKHAM,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%MARKHAM ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%MARKHAM, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%MARKHAM,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%MARKHAM CA%' THEN 'Ontario'
                --Not Markham, IL
                
                --Milford
                WHEN lower(Location) LIKE '%milford, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%milford,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%milford on%' THEN 'Ontario'
                
                -- not Milford, CA as there is Milford, NS
                
                
                --Milton
                WHEN UPPER(Location) LIKE '%MILTON, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%MILTON,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%MILTON ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%MILTON, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%MILTON,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%MILTON CA%' THEN 'Ontario'

                WHEN UPPER(Location) LIKE '%MISSISSAUGA%' THEN 'Ontario'
                
                --Muskoka
                WHEN lower(Location) LIKE '%muskoka, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%muskoka,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%muskoka on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%muskoka, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%muskoka,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%muskoka ca%' THEN 'Ontario'
               
                WHEN lower(Location) LIKE '%napanee%' THEN 'Ontario'
                
                 --Nepean is Ottawa
                WHEN lower(Location) LIKE '%nepean' THEN 'Ontario'
                
                
                --Newmarket
                WHEN UPPER(Location) LIKE '%NEWMARKET, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NEWMARKET,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NEWMARKET ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%NEWMARKET, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NEWMARKET,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NEWMARKET CA%' THEN 'Ontario'
                
               
                WHEN lower(Location) LIKE '%new liskeard' THEN 'Ontario'
                
                --Niagara
                WHEN UPPER(Location) LIKE '%NIAGARA, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NIAGARA,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NIAGARA ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%NIAGARA, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NIAGARA,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NIAGARA CA%' THEN 'Ontario'
                
                
                WHEN UPPER(Location) LIKE '%NIAGARA FALLS, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NIAGARA FALLS,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NIAGARA FALLS ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%NIAGARA FALLS, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NIAGARA FALLS,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NIAGARA FALLS CA%' THEN 'Ontario'
                --Not Niagara Falls, NY
                
                --North Bay
                WHEN UPPER(Location) LIKE '%NORTH BAY, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NORTH BAY,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NORTH BAY ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%NORTH BAY, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NORTH BAY,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%NORTH BAY CA%' THEN 'Ontario'
                
                --Oakville
                WHEN UPPER(Location) LIKE '%OAKVILLE, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%OAKVILLE,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%OAKVILLE ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%OAKVILLE, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%OAKVILLE,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%OAKVILLE CA%' THEN 'Ontario'
                
                
                WHEN UPPER(Location) LIKE '%ORANGEVILLE%' THEN 'Ontario' 
                WHEN UPPER(Location) LIKE '%ORILLIA%' THEN 'Ontario'                
                WHEN UPPER(Location) LIKE '%OSHAWA%' THEN 'Ontario'
                                
                --Ottawa
                WHEN UPPER(Location) LIKE '%OTTAWA, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%OTTAWA,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%OTTAWA ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%OTTAWA, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%OTTAWA,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%OTTAWA CA%' THEN 'Ontario'
                --Not Kottawa / Ottawa Lake, MI
                
                --Oxford Mills
                WHEN lower(Location) LIKE '%oxford mills, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%oxford mills,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%oxford mills on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%oxford mills, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%oxford mills,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%oxford mills ca%' THEN 'Ontario'
                
                --Parry Sound
                WHEN lower(Location) LIKE '%parry sound%' THEN 'Ontario'
                
                --Pembroke
                WHEN lower(Location) LIKE '%pembroke, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%pembroke,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%pembroke on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%pembroke, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%pembroke,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%pembroke ca%' THEN 'Ontario'
                                
                --Perth, Ontario
                WHEN UPPER(Location) LIKE '%PERTH, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%PERTH,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%PERTH ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%PERTH, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%PERTH,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%PERTH CA%' THEN 'Ontario'
                
                
                --Peterborough, Ontario
                WHEN UPPER(Location) LIKE '%PETERBOROUGH, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%PETERBOROUGH,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%PETERBOROUGH ON%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%peterborough, cambridgeshire%' THEN 'Z - Unknown'
                WHEN UPPER(Location) LIKE '%PETERBOROUGH, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%PETERBOROUGH,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%PETERBOROUGH CA%' THEN 'Ontario'
                
                --Pickering
                WHEN UPPER(Location) LIKE '%PICKERING, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%PICKERING,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%PICKERING ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%PICKERING, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%PICKERING,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%PICKERING CA%' THEN 'Ontario'
                -- not Pickerington, OH
                
                --Prescott
                WHEN lower(Location) LIKE '%prescott, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%prescott,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%prescott on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%prescott, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%prescott,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%prescott ca%' THEN 'Ontario'
                
                
                --RICHMOND HILL
                WHEN UPPER(Location) LIKE '%RICHMOND HILL, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%RICHMOND HILL,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%RICHMOND HILL ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%RICHMOND HILL, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%RICHMOND HILL,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%RICHMOND HILL CA%' THEN 'Ontario'
                --Not Richmond Hill, GA
                
                WHEN UPPER(Location) LIKE '%SARNIA%' THEN 'Ontario'
                
                
                --Sault Ste. Marie / Sault Ste Marie 
                WHEN UPPER(Location) LIKE '% MARIE, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '% MARIE,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '% MARIE ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '% MARIE, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '% MARIE,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '% MARIE CA%' THEN 'Ontario'
                --Not Sault Ste. Marie, MI
                
                --Scarborough was amalgamated into Toronto
                WHEN lower(Location) LIKE '%scarborough, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%scarborough,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%scarborough on%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%scarborough, ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%scarborough,ca%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%scarborough ca%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%st. catharines%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%st catharines%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%st. agatha%' THEN 'Ontario'
                
                
                --St. Thomas
                WHEN UPPER(Location) LIKE '%ST. THOMAS%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%ST THOMAS%' THEN 'Ontario'
                                
                WHEN lower(Location) LIKE '%stayner%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%stouffville%' THEN 'Ontario'
                
                
                --Stratford
                WHEN UPPER(Location) LIKE '%STRATFORD, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%STRATFORD,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%STRATFORD ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%STRATFORD, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%STRATFORD,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%STRATFORD CA%' THEN 'Ontario'
                
                --Sudbury
                WHEN UPPER(Location) LIKE '%SUDBURY, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%SUDBURY,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%SUDBURY ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%SUDBURY, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%SUDBURY,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%SUDBURY CA%' THEN 'Ontario'
                
                
                WHEN lower(Location) LIKE '%thornhill%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%thorold%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%THUNDER BAY%' THEN 'Ontario'
                
                --Timmins
                WHEN UPPER(Location) LIKE '%TIMMINS, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%TIMMINS,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%TIMMINS ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%TIMMINS, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%TIMMINS,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%TIMMINS CA%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%TORONTO%' THEN 'Ontario'
                                
                WHEN UPPER(Location) LIKE '%VAUGHAN%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%warkworth%' THEN 'Ontario'
                
                WHEN lower(Location) LIKE '%warwick, on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%warwick,on%' THEN 'Ontario'
                WHEN lower(Location) LIKE '%warwick on%' THEN 'Ontario'
                -- not Warwick, CA as there is Warwick, QC
                
                
                
                --Waterloo
                WHEN UPPER(Location) LIKE '%WATERLOO, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%WATERLOO,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%WATERLOO ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%WATERLOO, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%WATERLOO,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%WATERLOO CA%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%UNIVERSITY OF WATERLOO%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%WELLAND%' THEN 'Ontario'                
                WHEN UPPER(Location) LIKE '%WHITBY%' THEN 'Ontario'
                
                
                --Windsor
                WHEN UPPER(Location) LIKE '%WINDSOR, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%WINDSOR,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%WINDSOR ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%WINDSOR, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%WINDSOR,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%WINDSOR CA%' THEN 'Ontario'

                --Woodbridge
                WHEN UPPER(Location) LIKE '%WOODBRIDGE, ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%WOODBRIDGE,ON%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%WOODBRIDGE ON%' THEN 'Ontario'
                
                WHEN UPPER(Location) LIKE '%WOODBRIDGE, CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%WOODBRIDGE,CA%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%WOODBRIDGE CA%' THEN 'Ontario'
                
                
                WHEN UPPER(Location) LIKE '%ONTARIO, CAN%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%ONTARIO,CAN%' THEN 'Ontario'
                WHEN UPPER(Location) LIKE '%ONTARIO CAN%' THEN 'Ontario'
                
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',ON' THEN 'Ontario -- ,ON RTRIM'
                WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' ON' THEN 'Ontario -- leading space ON RTRIM'
                
                
                WHEN UPPER(Location) LIKE '%, ON%' THEN 'Ontario -- , ON'
                WHEN UPPER(Location) LIKE '%,ON%' THEN 'Ontario -- ,ON'
                WHEN UPPER(Location) LIKE '% ON%' THEN 'Ontario -- leading space ON'

                WHEN lower(Location) LIKE '%california%' THEN 'Z - Not Canada'
                --Ambiguous Ontario  
                WHEN UPPER(Location) LIKE '%ONTARIO, CA%' THEN 'Ontario, CA - Ambiguous'
                WHEN UPPER(Location) LIKE '%ONTARIO,CA%' THEN 'Ontario,CA - Ambiguous'
                WHEN UPPER(Location) LIKE '%ONTARIO CA%' THEN 'Ontario CA - Ambiguous'
                
                WHEN lower(Location) LIKE '%ohio%' THEN 'Z - Not Canada'
                WHEN lower(Location) LIKE '%ontario, oh%' THEN 'Z - Not Canada'
                
                WHEN UPPER(Location) LIKE '%ONTARIO, U%' THEN 'Z - Not Canada'
                
                
                WHEN UPPER(Location) LIKE '%MONTREAL%' THEN 'Quebec'
                WHEN UPPER(Location) LIKE '%QUEBEC%' THEN 'Quebec'
                
                WHEN lower(Location) LIKE '%acton vale%' THEN 'Quebec'
                
                WHEN lower(Location) LIKE '%alma, q%' THEN 'Quebec'
                WHEN lower(Location) LIKE '%alma,q%' THEN 'Quebec'
                WHEN lower(Location) LIKE '%alma q%' THEN 'Quebec'
                
                WHEN lower(Location) LIKE '%alma, c%' THEN 'Quebec'
                WHEN lower(Location) LIKE '%alma,c%' THEN 'Quebec'
                WHEN lower(Location) LIKE '%alma c%' THEN 'Quebec'
                                
                WHEN lower(Location) LIKE '%philippines%' THEN 'Z - Not Canada' --QC, Philippines
                WHEN UPPER(Location) LIKE '%QC%' THEN 'Quebec'

                WHEN UPPER(Location) LIKE '%NEW BRUNSWICK%' THEN 'New Brunswick'
                WHEN UPPER(Location) LIKE '%, NB%' THEN 'New Brunswick'
                WHEN UPPER(Location) LIKE '%,NB%' THEN 'New Brunswick'
                WHEN UPPER(Location) LIKE '% NB%' THEN 'New Brunswick'

                WHEN UPPER(Location) LIKE '%NOVA SCOTIA%' THEN 'Nova Scotia'
                
                --nsw nsk
                WHEN UPPER(Location) LIKE '%, NSW%' THEN 'Z - Not Canada'
                WHEN UPPER(Location) LIKE '%, NSK%' THEN 'Z - Not Canada'
                
                WHEN UPPER(Location) LIKE '%, NS%' THEN 'Nova Scotia'
                WHEN UPPER(Location) LIKE '%,NS%' THEN 'Nova Scotia'
                WHEN UPPER(Location) LIKE '% NS%' THEN 'Nova Scotia'

                WHEN UPPER(Location) LIKE '%PRINCE EDWARD ISLAND%' THEN 'Prince Edward Island'
                WHEN UPPER(Location) LIKE '%PEI' THEN 'Prince Edward Island'
                
                
                WHEN Location LIKE '%PENN%' THEN 'Z - Not Canada'
                
                -- PEru / PEnnsylvania
                --WHEN UPPER(Location) LIKE '%, PE%' THEN 'Prince Edward Island'
                --WHEN UPPER(Location) LIKE '%,PE%' THEN 'Prince Edward Island'
                --WHEN UPPER(Location) LIKE '% PE%' THEN 'Prince Edward Island'

                --Newfoundland, PA
                WHEN UPPER(Location) LIKE '%NEWFOUNDLAND, PA%' THEN 'Z - Not Canada'
                
                WHEN UPPER(Location) LIKE '%NEWFOUNDLAND%' THEN 'Newfoundland and Labrador'
                WHEN lower(Location) LIKE '%st. john''s%' THEN 'Newfoundland and Labrador' -- St. John''s'
                
                -- NL is Netherlands
                --WHEN UPPER(Location) LIKE '%, NL%' THEN 'Newfoundland and Labrador'
                --WHEN UPPER(Location) LIKE '%,NL%' THEN 'Newfoundland and Labrador'
                --WHEN UPPER(Location) LIKE '% NL%' THEN 'Newfoundland and Labrador'

                WHEN UPPER(Location) LIKE '%YUKON%' THEN 'Yukon Territory'
                WHEN Location LIKE '%YT%' THEN 'Yukon Territory'

                WHEN UPPER(Location) LIKE '%NORTHWEST TERRITORIES%' THEN 'Northwest Territories'
                WHEN Location LIKE '%, NT%' THEN 'Northwest Territories'

                WHEN UPPER(Location) LIKE '%NUNAVUT%' THEN 'Nunavut'
                WHEN Location LIKE '%, NU%' THEN 'Nunavut'

                --Alacant, Spain
                WHEN UPPER(Location) LIKE '%ALACANT%' THEN 'Z - Not Canada'
                
                --Alicante, Spain
                WHEN UPPER(Location) LIKE '%ALICANTE%' THEN 'Z - Not Canada'
                
                --American
                WHEN UPPER(Location) LIKE '%AMERICAN%' THEN 'Z - Not Canada'
                
                -- cantt
                WHEN UPPER(Location) LIKE '%CANTT%' THEN 'Z - Not Canada'
                    
                --Bulacan, Phi
                WHEN UPPER(Location) LIKE '%BULACAN%' THEN 'Z - Not Canada'
                
                --philippines
                WHEN UPPER(Location) LIKE '%PHILIPPINES%' THEN 'Z - Not Canada'
                
                
                WHEN UPPER(Location) LIKE '%DOMINICAN%' THEN 'Z - Not Canada'
                
                --Lucan
                WHEN UPPER(Location) LIKE '%LUCAN%' THEN 'Z - Not Canada'
                
                --Pascani, Romania
                WHEN UPPER(Location) LIKE '%CANI%' THEN 'Z - Not Canada'
                
                --Scandinavia
                WHEN UPPER(Location) LIKE '%SCANDINAVIA%' THEN 'Z - Not Canada'
                
                --Kocani, Macedonia
                WHEN UPPER(Location) LIKE '%KOCANI%' THEN 'Z - Not Canada'
                
                WHEN UPPER(Location) LIKE '%CANADA%' THEN 'Canada'
                WHEN UPPER(Location) LIKE '%, CAN%' THEN 'Canada'
                WHEN UPPER(Location) LIKE '%,CAN%' THEN 'Canada'
                WHEN UPPER(Location) LIKE '% CAN%' THEN 'Canada'
                
                WHEN lower(Location) LIKE '%canadur%' THEN 'Canadur'

             ELSE 'Z - Unknown'
         END AS Province
FROM Users
      WHERE LEN(Location) > 2 and Reputation >= ##minimumReputation##
)

SELECT UsersProvince.Province,
count(1) AS 'count'
      FROM UsersProvince

GROUP BY UsersProvince.Province
--ORDER BY count(1) desc
 ORDER BY Province asc

view current version

edited dec 29 13

created dec 7 13 jon.doe12449