1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for software engineers working on automated testing, continuous delivery, service integration and monitoring, and building SDLC infrastructure

-- Which Ontario locations are the users from?

WITH UsersOntarioCity AS (
SELECT 
          CASE                
                --Acton
                WHEN UPPER(Location) LIKE '%ACTON, ON%' THEN 'Acton'
                WHEN UPPER(Location) LIKE '%ACTON,ON%' THEN 'Acton'
                WHEN UPPER(Location) LIKE '%ACTON ON%' THEN 'Acton'
                
                WHEN UPPER(Location) LIKE '%ACTON, CAN%' THEN 'Acton' --Not California
                WHEN UPPER(Location) LIKE '%ACTON,CAN%' THEN 'Acton' --Not California
                WHEN UPPER(Location) LIKE '%ACTON CAN%' THEN 'Acton' --Not California
                
                -- Not Acton, CA nor Acton, MA nor Acton Vale, QC
                
                WHEN lower(Location) LIKE '%arnprior%' THEN 'Arnprior'
                WHEN UPPER(Location) LIKE '%AJAX%' THEN 'Ajax'
                WHEN lower(Location) LIKE '%attawapiskat%' THEN 'Attawapiskat'
                
                --Aurora
                WHEN UPPER(Location) LIKE '%AURORA, ON%' THEN 'Aurora'
                WHEN UPPER(Location) LIKE '%AURORA,ON%' THEN 'Aurora'
                WHEN UPPER(Location) LIKE '%AURORA ON%' THEN 'Aurora'
                
                WHEN UPPER(Location) LIKE '%AURORA, CA%' THEN 'Aurora'
                WHEN UPPER(Location) LIKE '%AURORA,CA%' THEN 'Aurora'
                WHEN UPPER(Location) LIKE '%AURORA CA%' THEN 'Aurora'
                
                WHEN lower(Location) LIKE '%aylmer' THEN 'Aylmer'
                
                --Ayr
                WHEN lower(Location) LIKE '%ayr, on%' THEN 'Ayr'
                WHEN lower(Location) LIKE '%ayr,on%' THEN 'Ayr'
                WHEN lower(Location) LIKE '%ayr on%' THEN 'Ayr'
                
                WHEN lower(Location) LIKE '%ayr, ca%' THEN 'Ayr'
                WHEN lower(Location) LIKE '%ayr,ca%' THEN 'Ayr'
                WHEN lower(Location) LIKE '%ayr ca%' THEN 'Ayr'
                
                --Baltimore
                WHEN lower(Location) LIKE '%baltimore, on%' THEN 'Baltimore'
                WHEN lower(Location) LIKE '%baltimore,on%' THEN 'Baltimore'
                WHEN lower(Location) LIKE '%baltimore on%' THEN 'Baltimore'
                
                -- not Baltimore, CA as there is Baltimore, NB
                
                
                WHEN UPPER(Location) LIKE '%BARRIE%' THEN 'Barrie'
                                
                
                --Belleville
                WHEN UPPER(Location) LIKE '%BELLEVILLE, ON%' THEN 'Belleville'
                WHEN UPPER(Location) LIKE '%BELLEVILLE,ON%' THEN 'Belleville'
                WHEN UPPER(Location) LIKE '%BELLEVILLE ON%' THEN 'Belleville'
                
                WHEN UPPER(Location) LIKE '%BELLEVILLE, CA%' THEN 'Belleville'
                WHEN UPPER(Location) LIKE '%BELLEVILLE,CA%' THEN 'Belleville'
                WHEN UPPER(Location) LIKE '%BELLEVILLE CA%' THEN 'Belleville'
                
                --Not Belleville, IL / MI / NJ
                    
                WHEN UPPER(Location) LIKE '%BOWMANVILLE%' THEN 'Bowmanville' 
                
                --Bradford
                WHEN lower(Location) LIKE '%bradford, on%' THEN 'Bradford'
                WHEN lower(Location) LIKE '%bradford,on%' THEN 'Bradford'
                WHEN lower(Location) LIKE '%bradford on%' THEN 'Bradford'
                
                WHEN lower(Location) LIKE '%bradford, ca%' THEN 'Bradford'
                WHEN lower(Location) LIKE '%bradford,ca%' THEN 'Bradford'
                WHEN lower(Location) LIKE '%bradford ca%' THEN 'Bradford'
                
                
                WHEN UPPER(Location) LIKE '%BRAMPTON%' THEN 'Brampton'
                WHEN UPPER(Location) LIKE '%BRANTFORD%' THEN 'Brantford'
                WHEN UPPER(Location) LIKE '%BROCKVILLE%' THEN 'Brockville'
                
                --Burlington
                WHEN UPPER(Location) LIKE '%BURLINGTON, ON%' THEN 'Burlington'
                WHEN UPPER(Location) LIKE '%BURLINGTON,ON%' THEN 'Burlington'
                WHEN UPPER(Location) LIKE '%BURLINGTON ON%' THEN 'Burlington'
                
                WHEN UPPER(Location) LIKE '%BURLINGTON, CA%' THEN 'Burlington'
                WHEN UPPER(Location) LIKE '%BURLINGTON,CA%' THEN 'Burlington'
                WHEN UPPER(Location) LIKE '%BURLINGTON CA%' THEN 'Burlington'
                
                --Caledon
                WHEN lower(Location) LIKE '%caledonia%' THEN 'Z - Not Canada'
                WHEN lower(Location) LIKE '%caledon%' THEN 'Caledon'
                
                
                --Cambridge
                WHEN UPPER(Location) LIKE '%CAMBRIDGE, ON%' THEN 'Cambridge'
                WHEN UPPER(Location) LIKE '%CAMBRIDGE,ON%' THEN 'Cambridge'
                WHEN UPPER(Location) LIKE '%CAMBRIDGE ON%' THEN 'Cambridge'
                
                WHEN UPPER(Location) LIKE '%CAMBRIDGE, CA%' THEN 'Cambridge'
                WHEN UPPER(Location) LIKE '%CAMBRIDGE,CA%' THEN 'Cambridge'
                WHEN UPPER(Location) LIKE '%CAMBRIDGE CA%' THEN 'Cambridge'
                
                
                WHEN lower(Location) LIKE '%campbellville%' THEN 'Campbellville'
                
                --Chatham, Ontario
                WHEN UPPER(Location) LIKE '%CHATHAM, ON%' THEN 'Chatham'
                WHEN UPPER(Location) LIKE '%CHATHAM,ON%' THEN 'Chatham'
                WHEN UPPER(Location) LIKE '%CHATHAM ON%' THEN 'Chatham'
                
                WHEN UPPER(Location) LIKE '%CHATHAM, CA%' THEN 'Chatham'
                WHEN UPPER(Location) LIKE '%CHATHAM,CA%' THEN 'Chatham'
                WHEN UPPER(Location) LIKE '%CHATHAM CA%' THEN 'Chatham'
                
                -- Not Chatham, NJ / NY
             
                WHEN lower(Location) LIKE '%chatham-kent%' THEN 'Chatham-Kent'
                WHEN lower(Location) LIKE '%cobourg%' THEN 'Cobourg'
                
                --Cochrane
                WHEN lower(Location) LIKE '%cochrane, on%' THEN 'Cochrane'
                WHEN lower(Location) LIKE '%cochrane,on%' THEN 'Cochrane'
                WHEN lower(Location) LIKE '%cochrane on%' THEN 'Cochrane'
                
                -- not Cochrane, CA as there is Cochrane, Alberta
                
                WHEN UPPER(Location) LIKE '%COE HILL%' THEN 'Coe Hill'
                
                --Colchester
                WHEN lower(Location) LIKE '%colchester, on%' THEN 'Colchester'
                WHEN lower(Location) LIKE '%colchester,on%' THEN 'Colchester'
                WHEN lower(Location) LIKE '%colchester on%' THEN 'Colchester'
                
                WHEN lower(Location) LIKE '%colchester, ca%' THEN 'Colchester'
                WHEN lower(Location) LIKE '%colchester,ca%' THEN 'Colchester'
                WHEN lower(Location) LIKE '%colchester ca%' THEN 'Colchester'
                
                --Not UK / CT
                
                WHEN UPPER(Location) LIKE '%COLLINGWOOD%' THEN 'Collingwood'
                
                --Cornwall
                WHEN lower(Location) LIKE '%cornwall, on%' THEN 'Cornwall'
                WHEN lower(Location) LIKE '%cornwall,on%' THEN 'Cornwall'
                WHEN lower(Location) LIKE '%cornwall on%' THEN 'Cornwall'
                
                -- not Cornwall, CA as there is Cornwall, PEI
                
                --Dundas
                WHEN lower(Location) LIKE '%dundas, on%' THEN 'Dundas'
                WHEN lower(Location) LIKE '%dundas,on%' THEN 'Dundas'
                WHEN lower(Location) LIKE '%dundas on%' THEN 'Dundas'
                
                WHEN lower(Location) LIKE '%dundas, ca%' THEN 'Dundas'
                WHEN lower(Location) LIKE '%dundas,ca%' THEN 'Dundas'
                WHEN lower(Location) LIKE '%dundas ca%' THEN 'Dundas'
                
                -- not Dundas, MN
                
                --Elgin
                WHEN lower(Location) LIKE '%elgin, on%' THEN 'Elgin'
                WHEN lower(Location) LIKE '%elgin,on%' THEN 'Elgin'
                WHEN lower(Location) LIKE '%elgin on%' THEN 'Elgin'
                
                WHEN lower(Location) LIKE '%elgin, ca%' THEN 'Elgin'
                WHEN lower(Location) LIKE '%elgin,ca%' THEN 'Elgin'
                WHEN lower(Location) LIKE '%elgin ca%' THEN 'Elgin'
                
                --Elmira
                WHEN UPPER(Location) LIKE '%ELMIRA, ON%' THEN 'Elmira'
                WHEN UPPER(Location) LIKE '%ELMIRA,ON%' THEN 'Elmira'
                WHEN UPPER(Location) LIKE '%ELMIRA ON%' THEN 'Elmira'
                
                WHEN UPPER(Location) LIKE '%ELMIRA, CA%' THEN 'Elmira'
                WHEN UPPER(Location) LIKE '%ELMIRA,CA%' THEN 'Elmira'
                WHEN UPPER(Location) LIKE '%ELMIRA CA%' THEN 'Elmira'
                -- Not Elmira, NY
                
                WHEN lower(Location) LIKE '%elora%' THEN 'Elora'
                
                --Essex
                WHEN lower(Location) LIKE '%essex, on%' THEN 'Essex'
                WHEN lower(Location) LIKE '%essex,on%' THEN 'Essex'
                WHEN lower(Location) LIKE '%essex on%' THEN 'Essex'
                
                -- not Essex, CA as there is one in California
                WHEN lower(Location) LIKE '%essex, can%' THEN 'Essex'
                WHEN lower(Location) LIKE '%essex,can%' THEN 'Essex'
                WHEN lower(Location) LIKE '%essex can%' THEN 'Essex'
                
                --Etobicoke was amalgamated into Toronto
                WHEN lower(Location) LIKE '%etobicoke%' THEN 'Toronto'
                
                --Exeter
                WHEN lower(Location) LIKE '%exeter, on%' THEN 'Exeter'
                WHEN lower(Location) LIKE '%exeter,on%' THEN 'Exeter'
                WHEN lower(Location) LIKE '%exeter on%' THEN 'Exeter'
                
                WHEN lower(Location) LIKE '%exeter, ca%' THEN 'Exeter'
                WHEN lower(Location) LIKE '%exeter,ca%' THEN 'Exeter'
                WHEN lower(Location) LIKE '%exeter ca%' THEN 'Exeter'
                 
                --Fergus
                WHEN lower(Location) LIKE '%fergus, on%' THEN 'Fergus'
                WHEN lower(Location) LIKE '%fergus,on%' THEN 'Fergus'
                WHEN lower(Location) LIKE '%fergus on%' THEN 'Fergus'
                
                WHEN lower(Location) LIKE '%fergus, ca%' THEN 'Fergus'
                WHEN lower(Location) LIKE '%fergus,ca%' THEN 'Fergus'
                WHEN lower(Location) LIKE '%fergus ca%' THEN 'Fergus'
                -- not Fergus Falls nor Ferguson
                 
                --Fonthill
                WHEN lower(Location) LIKE '%fonthill, on%' THEN 'Fonthill'
                WHEN lower(Location) LIKE '%fonthill,on%' THEN 'Fonthill'
                WHEN lower(Location) LIKE '%fonthill on%' THEN 'Fonthill'
                
                WHEN lower(Location) LIKE '%fonthill, ca%' THEN 'Fonthill'
                WHEN lower(Location) LIKE '%fonthill,ca%' THEN 'Fonthill'
                WHEN lower(Location) LIKE '%fonthill ca%' THEN 'Fonthill'
                
                WHEN UPPER(Location) LIKE '%FORT ERIE%' THEN 'Fort Erie'
                
                --Georgetown
                WHEN lower(Location) LIKE '%georgetown, on%' THEN 'Georgetown'
                WHEN lower(Location) LIKE '%georgetown,on%' THEN 'Georgetown'
                WHEN lower(Location) LIKE '%georgetown on%' THEN 'Georgetown'
                
                WHEN lower(Location) LIKE '%georgetown, ca%' THEN 'Georgetown'
                WHEN lower(Location) LIKE '%georgetown,ca%' THEN 'Georgetown'
                WHEN lower(Location) LIKE '%georgetown ca%' THEN 'Georgetown'
                
                --Granton
                WHEN lower(Location) LIKE '%granton, on%' THEN 'Granton'
                WHEN lower(Location) LIKE '%granton,on%' THEN 'Granton'
                WHEN lower(Location) LIKE '%granton on%' THEN 'Granton'
                
                -- not Granton, CA as there is Granton, NS
                
                WHEN lower(Location) LIKE '%greater toronto area%' THEN 'Greater Toronto Area - GTA'
                WHEN Location LIKE '%GTA%' THEN 'Greater Toronto Area - GTA'
                
                WHEN UPPER(Location) LIKE '%GUELPH%' THEN 'Guelph'
                
                --Hamilton, ON
                WHEN UPPER(Location) LIKE '%HAMILTON, ON%' THEN 'Hamilton'
                WHEN UPPER(Location) LIKE '%HAMILTON,ON%' THEN 'Hamilton'
                WHEN UPPER(Location) LIKE '%HAMILTON ON%' THEN 'Hamilton'
                
                WHEN UPPER(Location) LIKE '%HAMILTON, CAN%' THEN 'Hamilton'
                WHEN UPPER(Location) LIKE '%HAMILTON,CAN%' THEN 'Hamilton'
                WHEN UPPER(Location) LIKE '%HAMILTON CAN%' THEN 'Hamilton'
                --Not Hamilton California
                
                --Holland Landing
                WHEN lower(Location) LIKE '%holland landing, on%' THEN 'Holland Landing'
                WHEN lower(Location) LIKE '%holland landing,on%' THEN 'Holland Landing'
                WHEN lower(Location) LIKE '%holland landing on%' THEN 'Holland Landing'
                
                WHEN lower(Location) LIKE '%holland landing, ca%' THEN 'Holland Landing'
                WHEN lower(Location) LIKE '%holland landing,ca%' THEN 'Holland Landing'
                WHEN lower(Location) LIKE '%holland landing ca%' THEN 'Holland Landing'
                
                --Huntsville
                WHEN lower(Location) LIKE '%huntsville, on%' THEN 'Huntsville'
                WHEN lower(Location) LIKE '%huntsville,on%' THEN 'Huntsville'
                WHEN lower(Location) LIKE '%huntsville on%' THEN 'Huntsville'
                
                WHEN lower(Location) LIKE '%huntsville, ca%' THEN 'Huntsville'
                WHEN lower(Location) LIKE '%huntsville,ca%' THEN 'Huntsville'
                WHEN lower(Location) LIKE '%huntsville ca%' THEN 'Huntsville'
                
                --Kemptville
                WHEN lower(Location) LIKE '%kemptville, on%' THEN 'Kemptville'
                WHEN lower(Location) LIKE '%kemptville,on%' THEN 'Kemptville'
                WHEN lower(Location) LIKE '%kemptville on%' THEN 'Kemptville'
                
                WHEN lower(Location) LIKE '%kemptville, ca%' THEN 'Kemptville'
                WHEN lower(Location) LIKE '%kemptville,ca%' THEN 'Kemptville'
                WHEN lower(Location) LIKE '%kemptville ca%' THEN 'Kemptville'
                
                --Kenora
                WHEN lower(Location) LIKE '%kenora, on%' THEN 'Kenora'
                WHEN lower(Location) LIKE '%kenora,on%' THEN 'Kenora'
                WHEN lower(Location) LIKE '%kenora on%' THEN 'Kenora'
                
                WHEN lower(Location) LIKE '%kenora, ca%' THEN 'Kenora'
                WHEN lower(Location) LIKE '%kenora,ca%' THEN 'Kenora'
                WHEN lower(Location) LIKE '%kenora ca%' THEN 'Kenora'
           
                WHEN lower(Location) LIKE '%keswick, on%' THEN 'Keswick'
                
                --Kingston
                WHEN UPPER(Location) LIKE '%KINGSTON, ON%' THEN 'Kingston'
                WHEN UPPER(Location) LIKE '%KINGSTON,ON%' THEN 'Kingston'
                WHEN UPPER(Location) LIKE '%KINGSTON ON%' THEN 'Kingston'
                
                -- Ignoring that Kingston, CA may be Kingston, NS
                WHEN UPPER(Location) LIKE '%KINGSTON, CA%' THEN 'Kingston'
                WHEN UPPER(Location) LIKE '%KINGSTON,CA%' THEN 'Kingston'
                WHEN UPPER(Location) LIKE '%KINGSTON CA%' THEN 'Kingston'
                
                --Kitchener
                WHEN UPPER(Location) LIKE '%KITCHENER%' THEN 'Kitchener'
                WHEN UPPER(Location) LIKE '%KW%' THEN 'Kitchener' 
                
                --Lanark
                WHEN lower(Location) LIKE '%lanark, on%' THEN 'Lanark'
                WHEN lower(Location) LIKE '%lanark,on%' THEN 'Lanark'
                WHEN lower(Location) LIKE '%lanark on%' THEN 'Lanark'
                
                WHEN lower(Location) LIKE '%lanark, ca%' THEN 'Lanark'
                WHEN lower(Location) LIKE '%lanark,ca%' THEN 'Lanark'
                WHEN lower(Location) LIKE '%lanark ca%' THEN 'Lanark'
                
                --Leamington
                WHEN UPPER(Location) LIKE '%LEAMINGTON, ON%' THEN 'Leamington'
                WHEN UPPER(Location) LIKE '%LEAMINGTON,ON%' THEN 'Leamington'
                WHEN UPPER(Location) LIKE '%LEAMINGTON ON%' THEN 'Leamington'
                
                WHEN UPPER(Location) LIKE '%LEAMINGTON, CA%' THEN 'Leamington'
                WHEN UPPER(Location) LIKE '%LEAMINGTON,CA%' THEN 'Leamington'
                WHEN UPPER(Location) LIKE '%LEAMINGTON CA%' THEN 'Leamington'
                
                --London
                WHEN UPPER(Location) LIKE '%LONDON, ON%' THEN 'London'
                WHEN UPPER(Location) LIKE '%LONDON,ON%' THEN 'London'
                WHEN UPPER(Location) LIKE '%LONDON ON%' THEN 'London'
                
                WHEN UPPER(Location) LIKE '%LONDON, CA%' THEN 'London'
                WHEN UPPER(Location) LIKE '%LONDON,CA%' THEN 'London'
                WHEN UPPER(Location) LIKE '%LONDON CA%' THEN 'London'
                
                --Maple
                WHEN lower(Location) LIKE '%maple, on%' THEN 'Maple'
                WHEN lower(Location) LIKE '%maple,on%' THEN 'Maple'
                WHEN lower(Location) LIKE '%maple on%' THEN 'Maple'
                
                WHEN lower(Location) LIKE '%maple, ca%' THEN 'Maple'
                WHEN lower(Location) LIKE '%maple,ca%' THEN 'Maple'
                WHEN lower(Location) LIKE '%maple ca%' THEN 'Maple'
                
                --Markham
                WHEN UPPER(Location) LIKE '%MARKHAM, ON%' THEN 'Markham'
                WHEN UPPER(Location) LIKE '%MARKHAM,ON%' THEN 'Markham'
                WHEN UPPER(Location) LIKE '%MARKHAM ON%' THEN 'Markham'
                
                WHEN UPPER(Location) LIKE '%MARKHAM, CA%' THEN 'Markham'
                WHEN UPPER(Location) LIKE '%MARKHAM,CA%' THEN 'Markham'
                WHEN UPPER(Location) LIKE '%MARKHAM CA%' THEN 'Markham'
                --Not Markham, IL
                
                --Milford
                WHEN lower(Location) LIKE '%milford, on%' THEN 'Milford'
                WHEN lower(Location) LIKE '%milford,on%' THEN 'Milford'
                WHEN lower(Location) LIKE '%milford on%' THEN 'Milford'
                
                -- not Milford, CA as there is Milford, NS
                
                
                --Milton
                WHEN UPPER(Location) LIKE '%MILTON, ON%' THEN 'Milton'
                WHEN UPPER(Location) LIKE '%MILTON,ON%' THEN 'Milton'
                WHEN UPPER(Location) LIKE '%MILTON ON%' THEN 'Milton'
                
                WHEN UPPER(Location) LIKE '%MILTON, CA%' THEN 'Milton'
                WHEN UPPER(Location) LIKE '%MILTON,CA%' THEN 'Milton'
                WHEN UPPER(Location) LIKE '%MILTON CA%' THEN 'Milton'
                
                WHEN UPPER(Location) LIKE '%MISSISSAUGA%' THEN 'Mississauga'
                
                --Muskoka
                WHEN lower(Location) LIKE '%muskoka, on%' THEN 'Muskoka'
                WHEN lower(Location) LIKE '%muskoka,on%' THEN 'Muskoka'
                WHEN lower(Location) LIKE '%muskoka on%' THEN 'Muskoka'
                
                WHEN lower(Location) LIKE '%muskoka, ca%' THEN 'Muskoka'
                WHEN lower(Location) LIKE '%muskoka,ca%' THEN 'Muskoka'
                WHEN lower(Location) LIKE '%muskoka ca%' THEN 'Muskoka'
               
                WHEN lower(Location) LIKE '%napanee%' THEN 'Napanee'
                
                --Nepean is Ottawa
                WHEN lower(Location) LIKE '%nepean' THEN 'Ottawa'
                
                --Newmarket
                WHEN UPPER(Location) LIKE '%NEWMARKET, ON%' THEN 'Newmarket'
                WHEN UPPER(Location) LIKE '%NEWMARKET,ON%' THEN 'Newmarket'
                WHEN UPPER(Location) LIKE '%NEWMARKET ON%' THEN 'Newmarket'
                
                WHEN UPPER(Location) LIKE '%NEWMARKET, CA%' THEN 'Newmarket'
                WHEN UPPER(Location) LIKE '%NEWMARKET,CA%' THEN 'Newmarket'
                WHEN UPPER(Location) LIKE '%NEWMARKET CA%' THEN 'Newmarket'
                
                WHEN lower(Location) LIKE '%new liskeard' THEN 'New Liskeard'
                
                --Niagara
                WHEN UPPER(Location) LIKE '%NIAGARA, ON%' THEN 'Niagara'
                WHEN UPPER(Location) LIKE '%NIAGARA,ON%' THEN 'Niagara'
                WHEN UPPER(Location) LIKE '%NIAGARA ON%' THEN 'Niagara'
                
                WHEN UPPER(Location) LIKE '%NIAGARA, CA%' THEN 'Niagara'
                WHEN UPPER(Location) LIKE '%NIAGARA,CA%' THEN 'Niagara'
                WHEN UPPER(Location) LIKE '%NIAGARA CA%' THEN 'Niagara'
                
                
                WHEN UPPER(Location) LIKE '%NIAGARA FALLS, ON%' THEN 'Niagara Falls'
                WHEN UPPER(Location) LIKE '%NIAGARA FALLS,ON%' THEN 'Niagara Falls'
                WHEN UPPER(Location) LIKE '%NIAGARA FALLS ON%' THEN 'Niagara Falls'
                
                WHEN UPPER(Location) LIKE '%NIAGARA FALLS, CA%' THEN 'Niagara Falls'
                WHEN UPPER(Location) LIKE '%NIAGARA FALLS,CA%' THEN 'Niagara Falls'
                WHEN UPPER(Location) LIKE '%NIAGARA FALLS CA%' THEN 'Niagara Falls'
                
                WHEN lower(Location) LIKE '%niagara region, on%' THEN 'Niagara'
                WHEN lower(Location) LIKE '%niagara region,on%' THEN 'Niagara'
                WHEN lower(Location) LIKE '%niagara region on%' THEN 'Niagara'
                
                WHEN lower(Location) LIKE '%niagara region, ca%' THEN 'Niagara'
                WHEN lower(Location) LIKE '%niagara region,ca%' THEN 'Niagara'
                WHEN lower(Location) LIKE '%niagara region ca%' THEN 'Niagara'
                --Not Niagara Falls, NY
                
                --North Bay
                WHEN UPPER(Location) LIKE '%NORTH BAY, ON%' THEN 'North Bay'
                WHEN UPPER(Location) LIKE '%NORTH BAY,ON%' THEN 'North Bay'
                WHEN UPPER(Location) LIKE '%NORTH BAY ON%' THEN 'North Bay'
                
                WHEN UPPER(Location) LIKE '%NORTH BAY, CA%' THEN 'North Bay'
                WHEN UPPER(Location) LIKE '%NORTH BAY,CA%' THEN 'North Bay'
                WHEN UPPER(Location) LIKE '%NORTH BAY CA%' THEN 'North Bay'
                
                --Oakville
                WHEN UPPER(Location) LIKE '%OAKVILLE, ON%' THEN 'Oakville'
                WHEN UPPER(Location) LIKE '%OAKVILLE,ON%' THEN 'Oakville'
                WHEN UPPER(Location) LIKE '%OAKVILLE ON%' THEN 'Oakville'
                
                WHEN UPPER(Location) LIKE '%OAKVILLE, CA%' THEN 'Oakville'
                WHEN UPPER(Location) LIKE '%OAKVILLE,CA%' THEN 'Oakville'
                WHEN UPPER(Location) LIKE '%OAKVILLE CA%' THEN 'Oakville'
                
                WHEN UPPER(Location) LIKE '%ORANGEVILLE%' THEN 'Orangeville' 
                WHEN UPPER(Location) LIKE '%ORILLIA%' THEN 'Orillia'                
                WHEN UPPER(Location) LIKE '%OSHAWA%' THEN 'Oshawa'
                
                --Ottawa
                WHEN UPPER(Location) LIKE '%OTTAWA, ON%' THEN 'Ottawa'
                WHEN UPPER(Location) LIKE '%OTTAWA,ON%' THEN 'Ottawa'
                WHEN UPPER(Location) LIKE '%OTTAWA ON%' THEN 'Ottawa'
                
                WHEN UPPER(Location) LIKE '%OTTAWA, CA%' THEN 'Ottawa'
                WHEN UPPER(Location) LIKE '%OTTAWA,CA%' THEN 'Ottawa'
                WHEN UPPER(Location) LIKE '%OTTAWA CA%' THEN 'Ottawa'
                --Not Kottawa / Ottawa Lake, MI
                  
                --Oxford Mills
                WHEN lower(Location) LIKE '%oxford mills, on%' THEN 'Oxford Mills'
                WHEN lower(Location) LIKE '%oxford mills,on%' THEN 'Oxford Mills'
                WHEN lower(Location) LIKE '%oxford mills on%' THEN 'Oxford Mills'
                
                WHEN lower(Location) LIKE '%oxford mills, ca%' THEN 'Oxford Mills'
                WHEN lower(Location) LIKE '%oxford mills,ca%' THEN 'Oxford Mills'
                WHEN lower(Location) LIKE '%oxford mills ca%' THEN 'Oxford Mills'
                
                --Parry Sound
                WHEN lower(Location) LIKE '%parry sound%' THEN 'Parry Sound'
                
                --Pembroke
                WHEN lower(Location) LIKE '%pembroke, on%' THEN 'Pembroke'
                WHEN lower(Location) LIKE '%pembroke,on%' THEN 'Pembroke'
                WHEN lower(Location) LIKE '%pembroke on%' THEN 'Pembroke'
                
                WHEN lower(Location) LIKE '%pembroke, ca%' THEN 'Pembroke'
                WHEN lower(Location) LIKE '%pembroke,ca%' THEN 'Pembroke'
                WHEN lower(Location) LIKE '%pembroke ca%' THEN 'Pembroke'
                
                --Perth, Ontario
                WHEN UPPER(Location) LIKE '%PERTH, ON%' THEN 'Perth'
                WHEN UPPER(Location) LIKE '%PERTH,ON%' THEN 'Perth'
                WHEN UPPER(Location) LIKE '%PERTH ON%' THEN 'Perth'
                
                WHEN UPPER(Location) LIKE '%PERTH, CA%' THEN 'Perth'
                WHEN UPPER(Location) LIKE '%PERTH,CA%' THEN 'Perth'
                WHEN UPPER(Location) LIKE '%PERTH CA%' THEN 'Perth'
                
                --Peterborough, Ontario
                WHEN UPPER(Location) LIKE '%PETERBOROUGH, ON%' THEN 'Peterborough'
                WHEN UPPER(Location) LIKE '%PETERBOROUGH,ON%' THEN 'Peterborough'
                WHEN UPPER(Location) LIKE '%PETERBOROUGH ON%' THEN 'Peterborough'
                
                WHEN lower(Location) LIKE '%peterborough, cambridgeshire%' THEN 'Z - Uncountable'
      
                WHEN UPPER(Location) LIKE '%PETERBOROUGH, CA%' THEN 'Peterborough'
                WHEN UPPER(Location) LIKE '%PETERBOROUGH,CA%' THEN 'Peterborough'
                WHEN UPPER(Location) LIKE '%PETERBOROUGH CA%' THEN 'Peterborough'
                
                --Pickering
                WHEN UPPER(Location) LIKE '%PICKERING, ON%' THEN 'Pickering'
                WHEN UPPER(Location) LIKE '%PICKERING,ON%' THEN 'Pickering'
                WHEN UPPER(Location) LIKE '%PICKERING ON%' THEN 'Pickering'
                
                WHEN UPPER(Location) LIKE '%PICKERING, CA%' THEN 'Pickering'
                WHEN UPPER(Location) LIKE '%PICKERING,CA%' THEN 'Pickering'
                WHEN UPPER(Location) LIKE '%PICKERING CA%' THEN 'Pickering'
                -- not Pickerington, OH
                
                --Prescott
                WHEN lower(Location) LIKE '%prescott, on%' THEN 'Prescott'
                WHEN lower(Location) LIKE '%prescott,on%' THEN 'Prescott'
                WHEN lower(Location) LIKE '%prescott on%' THEN 'Prescott'
                
                WHEN lower(Location) LIKE '%prescott, ca%' THEN 'Prescott'
                WHEN lower(Location) LIKE '%prescott,ca%' THEN 'Prescott'
                WHEN lower(Location) LIKE '%prescott ca%' THEN 'Prescott'
                
                --RICHMOND HILL
                WHEN UPPER(Location) LIKE '%RICHMOND HILL, ON%' THEN 'Richmond Hill'
                WHEN UPPER(Location) LIKE '%RICHMOND HILL,ON%' THEN 'Richmond Hill'
                WHEN UPPER(Location) LIKE '%RICHMOND HILL ON%' THEN 'Richmond Hill'
                
                WHEN UPPER(Location) LIKE '%RICHMOND HILL, CA%' THEN 'Richmond Hill'
                WHEN UPPER(Location) LIKE '%RICHMOND HILL,CA%' THEN 'Richmond Hill'
                WHEN UPPER(Location) LIKE '%RICHMOND HILL CA%' THEN 'Richmond Hill'
                --Not Richmond Hill, GA                
                
                WHEN UPPER(Location) LIKE '%SARNIA%' THEN 'Sarnia'
                
                --Sault Ste. Marie / Sault Ste Marie 
                WHEN UPPER(Location) LIKE '% MARIE, ON%' THEN 'Sault Ste. Marie'
                WHEN UPPER(Location) LIKE '% MARIE,ON%' THEN 'Sault Ste. Marie'
                WHEN UPPER(Location) LIKE '% MARIE ON%' THEN 'Sault Ste. Marie'
                
                WHEN UPPER(Location) LIKE '% MARIE, CA%' THEN 'Sault Ste. Marie'
                WHEN UPPER(Location) LIKE '% MARIE,CA%' THEN 'Sault Ste. Marie'
                WHEN UPPER(Location) LIKE '% MARIE CA%' THEN 'Sault Ste. Marie'
                --Not Sault Ste. Marie, MI
                
                --Scarborough was amalgamated into Toronto
                WHEN lower(Location) LIKE '%scarborough, on%' THEN 'Toronto'
                WHEN lower(Location) LIKE '%scarborough,on%' THEN 'Toronto'
                WHEN lower(Location) LIKE '%scarborough on%' THEN 'Toronto'
                
                WHEN lower(Location) LIKE '%scarborough, ca%' THEN 'Toronto'
                WHEN lower(Location) LIKE '%scarborough,ca%' THEN 'Toronto'
                WHEN lower(Location) LIKE '%scarborough ca%' THEN 'Toronto'
                
                
                WHEN lower(Location) LIKE '%south river, on%' THEN 'South River'
                
                WHEN lower(Location) LIKE '%st. catharines%' THEN 'St. Catharines'
                WHEN UPPER(Location) LIKE '%st catharines%' THEN 'St. Catharines'
                
                WHEN lower(Location) LIKE '%st. agatha%' THEN 'St. Agatha'
                
                --St. Thomas
                WHEN UPPER(Location) LIKE '%ST. THOMAS%' THEN 'St. Thomas'
                WHEN UPPER(Location) LIKE '%ST THOMAS%' THEN 'St. Thomas'
                                
                WHEN lower(Location) LIKE '%stayner%' THEN 'Stayner'
                
                WHEN lower(Location) LIKE '%stouffville%' THEN 'Stouffville'
                
                --Stratford
                WHEN UPPER(Location) LIKE '%STRATFORD, ON%' THEN 'Stratford'
                WHEN UPPER(Location) LIKE '%STRATFORD,ON%' THEN 'Stratford'
                WHEN UPPER(Location) LIKE '%STRATFORD ON%' THEN 'Stratford'
                
                WHEN UPPER(Location) LIKE '%STRATFORD, CA%' THEN 'Stratford'
                WHEN UPPER(Location) LIKE '%STRATFORD,CA%' THEN 'Stratford'
                WHEN UPPER(Location) LIKE '%STRATFORD CA%' THEN 'Stratford'
                
                --Sudbury
                WHEN UPPER(Location) LIKE '%SUDBURY, ON%' THEN 'Sudbury'
                WHEN UPPER(Location) LIKE '%SUDBURY,ON%' THEN 'Sudbury'
                WHEN UPPER(Location) LIKE '%SUDBURY ON%' THEN 'Sudbury'
                
                WHEN UPPER(Location) LIKE '%SUDBURY, CA%' THEN 'Sudbury'
                WHEN UPPER(Location) LIKE '%SUDBURY,CA%' THEN 'Sudbury'
                WHEN UPPER(Location) LIKE '%SUDBURY CA%' THEN 'Sudbury'
                
                WHEN lower(Location) LIKE '%thornhill%' THEN 'Thornhill'
                WHEN lower(Location) LIKE '%thorold%' THEN 'Thorold'
                
                WHEN UPPER(Location) LIKE '%THUNDER BAY%' THEN 'Thunder Bay'
                
                
                WHEN lower(Location) LIKE '%tichborne, on%' THEN 'Tichborne'
                
                --Timmins
                WHEN UPPER(Location) LIKE '%TIMMINS, ON%' THEN 'Timmins'
                WHEN UPPER(Location) LIKE '%TIMMINS,ON%' THEN 'Timmins'
                WHEN UPPER(Location) LIKE '%TIMMINS ON%' THEN 'Timmins'
                
                WHEN UPPER(Location) LIKE '%TIMMINS, CA%' THEN 'Timmins'
                WHEN UPPER(Location) LIKE '%TIMMINS,CA%' THEN 'Timmins'
                WHEN UPPER(Location) LIKE '%TIMMINS CA%' THEN 'Timmins'
                
                WHEN UPPER(Location) LIKE '%TORONTO%' THEN 'Toronto'
                                
                WHEN UPPER(Location) LIKE '%VAUGHAN%' THEN 'Vaughan'
                
                WHEN lower(Location) LIKE '%warkworth%' THEN 'Warkworth'
                
                WHEN lower(Location) LIKE '%warwick, on%' THEN 'Warwick'
                WHEN lower(Location) LIKE '%warwick,on%' THEN 'Warwick'
                WHEN lower(Location) LIKE '%warwick on%' THEN 'Warwick'
                
                -- not Warwick, CA as there is Warwick, QC
                
                
                WHEN lower(Location) LIKE '%wasaga beach%' THEN 'Wasaga Beach'
                
                WHEN lower(Location) LIKE '%waterdown%' THEN 'Waterdown'
                
                --Waterloo
                WHEN UPPER(Location) LIKE '%WATERLOO, ON%' THEN 'Waterloo Think Tank'
                WHEN UPPER(Location) LIKE '%WATERLOO,ON%' THEN 'Waterloo Think Tank'
                WHEN UPPER(Location) LIKE '%WATERLOO ON%' THEN 'Waterloo Think Tank'
                
                WHEN UPPER(Location) LIKE '%WATERLOO, CA%' THEN 'Waterloo Think Tank'
                WHEN UPPER(Location) LIKE '%WATERLOO,CA%' THEN 'Waterloo Think Tank'
                WHEN UPPER(Location) LIKE '%WATERLOO CA%' THEN 'Waterloo Think Tank'
                
                WHEN UPPER(Location) LIKE '%UNIVERSITY OF WATERLOO%' THEN 'Waterloo Think Tank'
                
                WHEN lower(Location) LIKE '%waterloo region, on%' THEN 'Waterloo Think Tank'
                
                --Typo
                WHEN lower(Location) LIKE '%waterlo on%' THEN 'Waterloo Think Tank'
                                
                
                WHEN UPPER(Location) LIKE '%WELLAND%' THEN 'Welland'                
                WHEN UPPER(Location) LIKE '%WHITBY%' THEN 'Whitby'
                
                --Windsor
                WHEN UPPER(Location) LIKE '%WINDSOR, ON%' THEN 'Windsor'
                WHEN UPPER(Location) LIKE '%WINDSOR,ON%' THEN 'Windsor'
                WHEN UPPER(Location) LIKE '%WINDSOR ON%' THEN 'Windsor'
                
                WHEN UPPER(Location) LIKE '%WINDSOR, CA%' THEN 'Windsor'
                WHEN UPPER(Location) LIKE '%WINDSOR,CA%' THEN 'Windsor'
                WHEN UPPER(Location) LIKE '%WINDSOR CA%' THEN 'Windsor'
                
                --Woodbridge
                WHEN UPPER(Location) LIKE '%WOODBRIDGE, ON%' THEN 'Woodbridge'
                WHEN UPPER(Location) LIKE '%WOODBRIDGE,ON%' THEN 'Woodbridge'
                WHEN UPPER(Location) LIKE '%WOODBRIDGE ON%' THEN 'Woodbridge'
                
                WHEN UPPER(Location) LIKE '%WOODBRIDGE, CA%' THEN 'Woodbridge'
                WHEN UPPER(Location) LIKE '%WOODBRIDGE,CA%' THEN 'Woodbridge'
                WHEN UPPER(Location) LIKE '%WOODBRIDGE CA%' THEN 'Woodbridge'
                
                
                WHEN lower(Location) LIKE '%zurich, on%' THEN 'Zurich'
                WHEN lower(Location) LIKE '%zurich,on%' THEN 'Zurich'
                WHEN lower(Location) LIKE '%zurich on%' THEN 'Zurich'
                
                WHEN lower(Location) LIKE '%zurich, ca%' THEN 'Zurich'
                WHEN lower(Location) LIKE '%zurich,ca%' THEN 'Zurich'
                WHEN lower(Location) LIKE '%zurich ca%' THEN 'Zurich'
                
                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 lower(Location) LIKE '%canada, on%' 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'
                --Many false positives
                --WHEN UPPER(Location) LIKE '% ON%' THEN 'Ontario' -- leading space ON'
                
                WHEN UPPER(Location) = 'ON' THEN 'Ontario' -- exactly ON'

                -- Ontario, California - Unlikely Canadians would use CA
                WHEN UPPER(Location) LIKE '%ONTARIO, CA%' THEN 'Z - Not Canada'
                WHEN UPPER(Location) LIKE '%ONTARIO,CA%' THEN 'Z - Not Canada'
                WHEN UPPER(Location) LIKE '%ONTARIO CA%' THEN 'Z - Not Canada'
                
                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'
                
                --Assumed
                WHEN lower(Location) LIKE '%ottawa%' THEN 'Ottawa'
                WHEN lower(Location) LIKE '%ontario%' THEN 'Ontario'
                
     
             ELSE 'Z - Uncountable'
         END AS OntarioCity
FROM Users
      WHERE LEN(Location) > 1 and Reputation >= ##minimumReputation##
)

SELECT UsersOntarioCity.OntarioCity,
      count(1) AS 'count'
      FROM UsersOntarioCity

GROUP BY UsersOntarioCity.OntarioCity
--ORDER BY count(1) desc, OntarioCity asc

ORDER BY OntarioCity asc

fork query

edited dec 30 13

created dec 7 13 jon.doe12449