State abbrv in sql - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

State abbrv in sql

Author

Topic

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-07-25 : 13:51:34

I have a query that get information from a pivot table. There is a field REGION. The data in the field Region is (Ohio, California, Utah, Montana). Region is a Text Box on the form. Users enter there region like California, Utah, Montana. I want to convert Ohio to OH, California to CA, Utah to UT.
I need help to convert the data in the Region field to the State Abbreviation.

Here is the query

SELECT FirstName, LastName, City, region, country,
ModuleTitle,[FileName]
FROM (SELECT MAX(CASE WHEN ppd.propertyName = 'FirstName' THEN up.propertyValue END) AS FirstName,
MAX(CASE WHEN ppd.propertyName = 'LastName' THEN up.propertyValue END) AS LastName,
MAX(CASE WHEN ppd.propertyName = 'City' THEN up.propertyValue END) AS City,
MAX(CASE WHEN ppd.propertyName = 'region' THEN up.propertyValue END) AS region,
MAX(CASE WHEN ppd.propertyName = 'Country' THEN up.propertyValue END) AS country,
up.UserID, l.clickdate, t.UrlType,m.ModuleTitle, f.FileName,u.Email
FROM ProfilePropertyDefinition AS ppd INNER JOIN
UserProfile AS up ON up.PropertyDefinitionID = ppd.PropertyDefinitionID INNER JOIN
UrlLog l ON up.UserID = l.UserID INNER JOIN
Users u ON u.UserID = l.UserID RIGHT OUTER JOIN
UrlTracking t ON l.UrlTrackingID = t .UrlTrackingID LEFT OUTER JOIN
Modules m ON t .ModuleId = m.ModuleID LEFT OUTER JOIN
Files f ON REPLACE(t .Url, 'FileID=', '') = f.FileId
GROUP BY up.UserID, u.Email, l.clickdate, t.UrlType, m.ModuleTitle, f.FileName) d
WHERE (FirstName IS NOT NULL) AND (LastName IS NOT NULL) AND (clickdate BETWEEN '2007 - 07 - 21' AND
'2007 - 07 - 28') AND (UrlType = 'F') and [FileName] like '%.zip%

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 14:06:00

Do you have a table that maps state name to state abbreviation? You'll need one. You don't convert it programmatically.

Once you have the table in place, you just join to that table and reference the state abbreviation column instead of your region column.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-07-25 : 17:24:04

Thanks for your help. I had made the lookup table.
One more question if i want to show only the the abbrev for the states in the Region field if the country are USA, Canada and australia and rest of the data in the region filed remians what ever it is. Right now it is showing null if the data in my region field is not in my lookup table.

Here is the Query

SELECT FirstName, LastName, Email,CompanyName, City, region, country, telephone, companysize,
ModuleTitle,[FileName]
FROM (SELECT MAX(CASE WHEN ppd.propertyName = 'FirstName' THEN up.propertyValue END) AS FirstName,
MAX(CASE WHEN ppd.propertyName = 'LastName' THEN up.propertyValue END) AS LastName,
MAX(CASE WHEN ppd.propertyName = 'Company Name' THEN up.propertyValue END) AS CompanyName,
MAX(CASE WHEN ppd.propertyName = 'City' THEN up.propertyValue END) AS City,
--MAX(CASE WHEN ppd.propertyName = 'region' THEN up.propertyValue END) AS region,
MAX(CASE WHEN ppd.propertyName = 'region' THEN St.StateAbbrev END) AS region,
MAX(CASE WHEN ppd.propertyName = 'Country' THEN up.propertyValue END) AS country,
MAX(CASE WHEN ppd.propertyName = 'telephone' THEN up.propertyValue END) AS telephone,
MAX(CASE WHEN ppd.propertyName = 'Company size' THEN up.propertyValue END) AS companysize, up.UserID, l.clickdate, t.UrlType,m.ModuleTitle, f.FileName,u.Email
FROM ProfilePropertyDefinition AS ppd INNER JOIN
UserProfile AS up ON up.PropertyDefinitionID = ppd.PropertyDefinitionID INNER JOIN
UrlLog l ON up.UserID = l.UserID INNER JOIN
Users u ON u.UserID = l.UserID RIGHT OUTER JOIN
UrlTracking t ON l.UrlTrackingID = t .UrlTrackingID LEFT OUTER JOIN
Modules m ON t .ModuleId = m.ModuleID LEFT OUTER JOIN
Files f ON REPLACE(t .Url, 'FileID=', '') = f.FileId
LEFT OUTER JOIN State_lookup st ON up.propertyValue = St.StateDescription
GROUP BY up.UserID, u.Email, l.clickdate, t.UrlType, m.ModuleTitle, f.FileName) d
WHERE (FirstName IS NOT NULL) AND (LastName IS NOT NULL) AND (CompanyName IS NOT NULL) AND (clickdate BETWEEN '2007 - 05 - 21' AND
'2007 - 07 - 28') AND (UrlType = 'F') and [FileName] like '%.zip%'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 17:40:34

You are probably using an OUTER JOIN (LEFT or RIGHT) then. Use an INNER JOIN instad.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources