CREATE TABLE #ZIPS (ZIP Varchar(5))
INSERT INTO #ZIPs Values ('09876')
INSERT INTO #ZIPs Values ('34876')
INSERT INTO #ZIPs Values ('09446')
INSERT INTO #ZIPs Values ('02376')
INSERT INTO #ZIPs Values ('49876')
INSERT INTO #ZIPs Values ('29876')
INSERT INTO #ZIPs Values ('69876')
INSERT INTO #ZIPs Values ('79876')
INSERT INTO #ZIPs Values ('99976')
INSERT INTO #ZIPs Values ('00076')
INSERT INTO #ZIPs Values ('59006')
INSERT INTO #ZIPs Values ('70076')
INSERT INTO #ZIPs Values ('89006')
INSERT INTO #ZIPs Values ('10876')
INSERT INTO #ZIPs Values ('10076')
How to make this slabs for example
Anything between 00000 to 10000 THEN 1
Anything between 10000 to 20000 THEN 2
Anything between 20000 to 30000 THEN 3
so on
Can you please help me other than this way below
SELECT CASE WHEN zip< '10000' THEN 1
WHEN ZIp<'20000' THEN 2
...
....
...
WHEN ZIP < '90000' THEN 0
END
FROM #ZIPs
Thanks
ASita
June 15, 2021 at 3:51 pm
Why doesn't the CASE work for you?
June 15, 2021 at 4:04 pm
I would like to see if any other alternatives were there. other than Case.. Please
Thank you
June 15, 2021 at 4:52 pm
I would like to see if any other alternatives were there. other than Case.. Please
Thank you
Heh... more likely, it's probably an interview question. 😉 It's a good one, IMHO, as well.
I think the last value in your case for <90000 is incorrect but I get the gist of what you're trying to do.
Here's an alternative to such things using just basic integer math. EDIT: There's more than one way... There may be additional methods but these are two of the obvious ways.
SELECT *
,Slab1 = (ZIP/10000+1)%10
,Slab2 = (LEFT(ZIP,1)+1)%10
FROM #ZIPS
;
And thank you for posting the readily consumable test data! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2021 at 7:02 pm
I'm unsure what value a leading '9' should yield as the slab#, it's not really clear from your OP.
SELECT
LEFT(ZIP, 1) + CASE WHEN SUBSTRING(ZIP, 2, 4) = '0000' THEN 0 ELSE 1 END AS slab#,
ZIP
FROM #ZIPS
Edit: If leading '9' should yield a '0' result, then:
SELECT
(LEFT(ZIP, 1) + CASE WHEN SUBSTRING(ZIP, 2, 4) = '0000' THEN 0 ELSE 1 END) % 10 AS slab#,
ZIP
FROM #ZIPS
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 15, 2021 at 7:50 pm
Create a table with the zip code ranges - then use an outer/cross apply to lookup the range. Or use a CTE to create a table with the range and a join using BETWEEN to pull the specific row from the range.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Or...
Select *
, ZipRange = (z.ZIP / 10000) + 1
From #ZIPs z;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 15, 2021 at 8:22 pm
I like the cross apply, but not sure it's needed here.
Going back to the original, asita, is the goal to bucket rows for a report of some sort? what's the reasoning behind breaking up zip codes?
In some sense, the LEFT() approach from Scott is one I like a lot, perhaps as a computed column for quick reporting.
June 15, 2021 at 8:30 pm
I like the cross apply, but not sure it's needed here.
Going back to the original, asita, is the goal to bucket rows for a report of some sort? what's the reasoning behind breaking up zip codes?
In some sense, the LEFT() approach from Scott is one I like a lot, perhaps as a computed column for quick reporting.
Just throwing out ideas - which it seemed the OP wanted.
The table lookup would be a better option if there are more columns that you would want available. For example, if you wanted an SlabID, SlabName, SlabMnemonic, SlabDescription - and other information about that slab. For any type of table lookup you can use BETWEEN in the join if you have a start/end range - or apply with top 1 if you only have the start of the range.
If you are only using US zip codes - and the first 5 digits - then you should not have any issues using divide by 10000 (I don't think there are any US zip codes with letters).
FWIW - any time I see someone looking to code categories like this I would recommend building a table to manage the categories. At some point, someone will want to change the categories and then you would need to modify it in multiple areas.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 17, 2021 at 2:43 pm
Thank you for your inputs I apologize for my mistake in my sample the last case statement is 9. not 0.
Thank you for all your assistance and guidance
June 17, 2021 at 2:44 pm
Thank you Jeffery,
Can you please give one sample with couple records. much helpful. with the join and CTE..
Thank you
Asiti
June 17, 2021 at 2:53 pm
According to your sample data and results, the "Answer" you marked is not correct. For example, you stated that '10000' should return a 1 -- "Anything between 00000 to 10000 THEN 1" -- but that code returns a 2.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 17, 2021 at 5:19 pm
According to your sample data and results, the "Answer" you marked is not correct. For example, you stated that '10000' should return a 1 -- "Anything between 00000 to 10000 THEN 1" -- but that code returns a 2.
I assumed that the OP actually wants categories for 00000 thru 09999, 10000 through 19999 - which makes more sense than including 10000 in category 1, 20000 in category 2, etc.
With that said - I still would recommend building a category table instead of calculating the value.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 17, 2021 at 5:26 pm
Thank you Jeffery,
Can you please give one sample with couple records. much helpful. with the join and CTE..
Thank you
Asiti
First - you need a table of categories - with a start/end of range. Then it is simple:
Select *
From Zips z
Inner Join SlabRange sr On z.Zip Between sr.SlabStart And sr.SlabEnd;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 17, 2021 at 5:44 pm
Thank you for your inputs I apologize for my mistake in my sample the last case statement is 9. not 0.
Thank you for all your assistance and guidance
Ok... that does not match what your other case statements imply.
Your other case statements imply the following...
You DO NOT NEED A RANGE TABLE FOR THIS.
[EDIT] Just to clarify that line above, you don't need a table to do this simple calculation. However (as I admit to on the second page of this post), Jeffrey Williams IS CORRECT about needing a table for this. I go into it a bit more in detail later in my apology to him BUT the table IS needed for "Documentation Purposes to Explain and Estoteric Value" and, even if the meaning were totally obvious, the table is needed for purposes of DRI.
What you need is to correctly identify what you want to do with the ranges of zip codes, which are mathematical in nature, and then just assign it with a single mathematical formula (like I did on my original post on this thread), which will ALWAYS be faster than a JOIN for a lookup. (see EDIT above)
We just need to know what the actual "gazintas" are to solve this problem. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply