Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
boehnc
Starting Member
15 Posts |
 Posted - 2012-07-26 : 13:54:33
|
| I have a patient table with each patient having one zip code. I'd like to case zipcodes into a specific area which will go into another column for each patient. For example: patients with zip codes of 12345, 67891, and 56789 into area "SW", Zip codes 54321, 98765, 76543 into area "NW", ect. Any help is appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-26 : 14:17:01
|
| [code]SELECT ..,CASE WHEN [zip code] IN (12345,67891,56789) THEN 'SW' WHEN [sip code] IN (54321,98765,76543) THEN 'NW' ...END AS [Area]FROM table..[/code]if you want more scalable and maintainable solution to cater for frequent changes/additions i would suggest creating area-code mapping table AreaCode (Area,Code,MappingID) and use it in join in above sql like[code]SELECT ...,ac.AreaFROM Yourtable tJOIN AreaCodeMapping acON ac.Code = t.[zip code]...[/code]this way you can reorganise mappings as you want by just inserting/updating records in mapping table rather than changing your backend sql code------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
Easy case statement