September 8, 2008 at 9:36 am
I have address data that contains City, ZipCode, and CityType. For any given distinct city and zipcode, there can be up to four different CityTypes:
zipcodecitycitytype
85739TUCSONB
85739TUCSONC
85739TUCSONN
85739TUCSONP
'P' indicates that the zipcode and city are 'Primary'. In some cases, there isn't a primary CityType:
zipcodecity citytype
00934FORT BUCHANANB
00934FORT BUCHANANN
00934FORT BUCHANANU
What I need to do is return all the records where the citytype = 'p', or where there is no 'p' citytype, return just one instance of the record. ZipCodeID and ZipCode have a one to one relationship.
One way I have tried to do this is by assigning a 'score' to CityType. CASE CityType = 'p', THEN CityType = 2, ELSE 1. So far, my efforts have yielded a result such as follows:
City ZipCodeIDZipCodeCityTypeScoreCityID
HOLTSVILLE8586384005011 140079
HOLTSVILLE8586384005012 140078
HOLTSVILLE8586385005441 140079
HOLTSVILLE8586385005442 140078
HOLTSVILLE8590231117422 140078
The result I am looking for would be as follows:
City ZipCodeIDZipCodeCityTypeScoreCityID
HOLTSVILLE8586384005012 140078
HOLTSVILLE8586385005442 140078
HOLTSVILLE8590231117422 140078
In this example each of these ZipCode, City, CityID records has a CityType of 'p'. If one of the city, zipcode, CityType records did not have a 'p' CityType, then that record would show up here with a CityTypeScore of 1.
The following is the query I have attempted:
SELECT
tr.City,
tr.ZipCodeID,
MAX(tr.ZipCode) AS MaxZipCode,
MAX(tr.CityTypeScore) AS MaxCityTypeScore, --MAX(CityTypeScore)
ct.CityID
FROM
(
SELECT DISTINCT
st.city,
st.[State],
zp.ZipCodeID,
st.Zipcode,
CASE
WHEN st.CityType = 'p' THEN 2
ELSE 1
END AS CityTypeScore,
st.CityType
from dbo.USZipCodeDetail_Staging st
inner join
(SELECT ZipCode FROM [dbo].[USZipCodeDetail_Staging] GROUP BY ZipCode HAVING COUNT(DISTINCT [City]) = 1)th
on st.ZipCode = th.ZipCode
inner join dbo.zipcode zp
on st.zipcode = zp.zipcode
WHERE st.city = 'holtsville'
GROUP BY st.City, st.[State], zp.ZipCodeID, st.ZipCode, st.CityType
) as tr
inner join vStateCity ct
on tr.City = ct.CityName
and tr.[state] = ct.TwoLetterCode
and tr.CityType = ct.CityTypeCode
GROUP BY
tr.City,tr.ZipCodeID, ct.CityID
ORDER BY tr.ZipCodeID, tr.City
The following are the results from this query:
City ZipCodeID MaxZipCodeMaxCityTypeScoreCityID
HOLTSVILLE8586384 00501 2 140078
HOLTSVILLE8586384 00501 1 140079
HOLTSVILLE8586385 00544 1 140079
HOLTSVILLE8586385 00544 2 140078
HOLTSVILLE8590231 11742 2 140078
No matter what I do, I can't seem to get only the records with the Max CityTypeScore to come back in the results.
Any suggestions?
Thank you for your help!
CSDunn
September 8, 2008 at 9:58 am
Here is something that works on a simple one table problem:
[font="Courier New"]DECLARE @cities TABLE(zipcode CHAR(5), city VARCHAR(25), TYPE CHAR(1))
INSERT INTO @cities
SELECT
'85739', 'TUCSON', 'C'
UNION ALL
SELECT
'85739', 'TUCSON', 'N'
UNION ALL
SELECT
'85739', 'TUCSON', 'P'
UNION ALL
SELECT
'85739', 'TUCSON', 'B'
UNION ALL
SELECT
'00934', 'FORT BUCHANAN', 'B'
UNION ALL
SELECT
'00934', 'FORT BUCHANAN', 'N'
UNION ALL
SELECT
'00934', 'FORT BUCHANAN', 'U'
SELECT DISTINCT
city,
zipcode
FROM
@cities C
WHERE
TYPE = 'P' OR
NOT EXISTS (SELECT * FROM @cities WHERE TYPE = 'P' AND C.city = city AND C.zipcode = zipcode)[/font]
Please note how I supplied some test data to work with and check out the links in my signature on how to post questions in a way that assists those that want to help help more quickly and easily.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2008 at 11:49 am
Thanks for your help! I am also trying something with the SQL Server 2005 'Ranking' function.
CSDunn
September 8, 2008 at 11:54 am
You can probably use the Rank Function, but because you don't care which of the remaining rows you return ranking is probably unnecessary.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2008 at 8:17 pm
If your CityType field is null where you don't have a primary city you should be able to simply use Coalesce.
SELECT DISTINCT
st.city,
st.[State],
zp.ZipCodeID,
st.Zipcode,
COALESCE(CityType, 'P') CityType,
st.CityType
from dbo.USZipCodeDetail_Staging st
...
If it is an empty string try COALESCE(NULLIF(CityType,''),'P')
Gary Johnson
Sr Database Engineer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply