July 25, 2007 at 12:09 pm
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%
July 25, 2007 at 12:32 pm
Note that I'm assuming you have a table named States which has the abbreviation and the long description of the state:
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 St.StateAbbrev 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
LEFT OUTER JOIN STATES 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 (clickdate BETWEEN '2007 - 07 - 21' AND '2007 - 07 - 28')
AND (UrlType = 'F')
and [FileName] like '%.zip%
Lowell
July 25, 2007 at 3:21 pm
Thanks for your help. I had made the lookup table how to use it was the problem.
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.
July 25, 2007 at 5:54 pm
... THEN ISNULL(St.StateDescription, up.propertyValue) END ...
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply