ozzy t
asked on
Query to capture 5 and 9 digit zip code?
i have this query which works well, i need to make sure i am also capturing the 9 digit zip code in my results, how would i go about this?
select distinct pm.vwGenPatInfo.Patient_Number,Patient_Zip_Code --,substring(Patient_Zip_Code,1,5)as patient_zip_code
from pm.vwGenPatInfo
inner join pm.vwGenSvcInfo
on pm.vwGenPatInfo.Patient_Number = pm.vwGenSvcInfo.Patient_Number
where
PM.vwGenSvcInfo.Service_Date_From >= CONVERT(DATE, DATEADD(MONTH, -24, GETDATE()))
and pm.vwGenPatInfo.Patient_Zip_Code in ('28006','28012','28016','28021','28032','28034','28052','28053','28054','28055','28056','28077','28098','28101','28120','28164')
--and Prim_Policy_Carrier_Category_Descr = 'Self-pay Insurances (HNG)'
--and Prim_Policy_Carrier_Category_Descr is null
order by patient_zip_code desc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much!! you rock !
ASKER
thank you !
Open in new window
A better solution would be to design your table so that there is a column for a 5 digit zip code, and another column for the zip extension. Then you could just look for the 5 digit zip.