During a meeting a question came up about any possible way to determine which phone numbers in our address table might be cell phones versus numbers that are land-lines. I gave it a little bit of thought and figured one possible way is to compare the area codes of the phone numbers we have for people versus the indigenous area codes of where they currently live. The logic ran that if someone purchased a cell phone in one area code and moved, their area code on their cell phone would not match the area code serviced by the zip code they currently inhabit. These mismatches might be cell phones. I figured this kind of thing might be useful if anyone else ran into this question.
Free online database that correlates zip codes and area codes: https://www.unitedstateszipcodes.org/zip-code-database/
View for Good Valid Preferred addresses that I used:
CREATE VIEW [dbo].[rpt_addressGoodValPref]
Select * from address
where addrlocatr = 'g'
and addrmc1 = 'Y'
and Case month( getdate())
WHEN 1 THEN addrjan
WHEN 2 THEN addrfeb
WHEN 3 THEN addrmar
WHEN 4 THEN addrapr
WHEN 5 THEN addrmay
WHEN 6 THEN addrjun
WHEN 7 THEN addrjul
WHEN 8 THEN addraug
WHEN 9 THEN addrsep
WHEN 10 THEN addroct
WHEN 11 THEN addrnov
WHEN 12 THEN addrdec
ELSE addrdec END = 'Y'
Import the zip_code_database.csv file from the website into a table, I called mine ajm_areacode.
select o.* from (
substring(ltrim(addrzipcod),0,6) as zip_m,
substring(ltrim(addrphone),0,4) as ac_m,
from rpt_addressGoodValPref join ajm_areacode on substring(ltrim(addrzipcod),0,6)=zip
where CHARINDEX(substring(ltrim(addrphone),0,4),area_codes)=’0′) o
where o.ac_m is not null and o.ac_m!=”
It's not pretty, but it works. :)
Western Michigan University
Current Config: Mill 7.9.1, SQL2k8r2, IIS 6.0, Windows 2003 Servers, 344k corebio.