October 16, 2009 at 2:09 pm
Hello,
I am having a problem with my query. Specifically, my database has zip codes in zip + 4 format which when queried is returning too many results. For example with a zip + 4 format, I may return 1000 results for zip code 05701. The reason is that the + 4 is making the zip much more precise. In my query I only want to recognize the zip as a 5 number combo. Instead of 05701-0001, I only want to return 05701 and make that number unique. Therefor in my grouping, it will not recognize the + 4. Please see below my statement. Thanks
select key1,zip, count(*)as 'count',
Case key1
when 'Prospect' Then 'Commercial'
Else 'Residential'
End
from contact1 as t1,contact2 as t2
where (t1.accountno = t2.accountno)
and key1 in ('prospect - res','prospect')
group by zip,key1
order by zip, count desc
October 16, 2009 at 2:37 pm
Maybe I'm misunderstanding something, but why would you want to make the Zip into a unique value? Can't you have multiple addresses in the same Zip?
The function you are looking for is Left(). You can use Left(Zip, 5) to just get the first five digits of a Zip code.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 16, 2009 at 3:01 pm
Ok...to clarify,
Our DB has zip codes in a 'zip + 4 ' format. For example, a zip + 4 could return lots of variations for zip code 05732....
could be 05732-1111,05732-2222,05732-3333. I simply want my query to treat zip as a a traditional zip...no +4.
So I want to know how many customers are in zip code 05732. Hope this sheds some light.
October 16, 2009 at 3:04 pm
Right. In that case, you'd use "Where left(Zip, 5) = '05123'", instead of "Where Zip = '05123'". That's why I referenced the Left() function.
If you do that kind of query a lot, you might want to see about using a calculated column using the Left function, and an index on that column (and whatever else would be needed to make a good index).
Take a look at Left() in Books Online and see if that helps.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 16, 2009 at 3:10 pm
Darn GSquared types too fast for me to keep ahead of him. OH well for what it is worth here is my adaptation of GSquared's recommendations.
Without the table definitions here is a quick revision of yours that worked for me
CREATE TABLE #Contact1(Key1 VARCHAR(15),Zip VARCHAR(12))
INSERT INTO #Contact1(Key1,Zip)
SELECT 'prospect','05701-0001' UNION ALL
SELECT 'prospect','05701-0011' UNION ALL
SELECT 'prospect','05701-0111' UNION ALL
SELECT 'prospect','05701-2001' UNION ALL
SELECT 'Residential','05701-0001' UNION ALL
SELECT 'Residential','05702-0001'
select key1,LEFT(zip,5) AS 'Zip', count(*)as 'count',
Case key1
when 'Prospect' Then 'Commercial'
Else 'Residential'
End
from #contact1 --as t1,contact2 as t2
--where (t1.accountno = t2.accountno)
--and key1 in ('prospect - res','prospect')
group by Left(Zip,5),key1
order by LEFT(zip,5), count desc
Result
key1 Zipcount(No column name)
prospect057014Commercial
Residential057011Residential
Residential057021Residential
October 17, 2009 at 7:02 am
Thanks for the effort! Works perfect!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply