Q&A for ebook publishers and readers
-- 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