SQL statement help for ranging zipcode + 4

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 2005 Forums

Transact-SQL (2005)

SQL statement help for ranging zipcode + 4

Author

Topic

captruth
Starting Member

1 Post

Posted - 2008-08-07 : 14:58:13

Hey all;

I'm having a tough time getting my head around the following issues. I have a table with over 67 millions rows of data based upon Zipcodes + 4; i.e. 5-digits zipcode in one column and 4-digit suffix in another; for the entire us.

I need to range the sucker to reduce file sizes for export files. My problem is ranging the zipcode based upon the 4-digit +4 column.

I am using SQL Server 2005; I have tried the all the sorting and grouping options I know of along with DISTINCT MIN and MAX to no avail.

Everytime the 4-digit +4 changes cities (i.e. CITY_NAME) I need to group that as a specific range; if the same city picks up after that I need that to be a separate range. Here's the input and output;

Input:
Zip Plus4 StateCountyFIPSCode Placecode PlaceName
90001 1125 06037 0624477 FLORENCE-GRAHAM
90001 1126 06037 0624477 FLORENCE-GRAHAM
90001 1127 06037 0644000 LOS ANGELES
90001 1128 06037 0644000 LOS ANGELES
90001 1129 06037 0624477 FLORENCE-GRAHAM
90001 1130 06037 0644000 LOS ANGELES
90001 1131 06037 0624477 FLORENCE-GRAHAM
90001 1132 06037 0644000 LOS ANGELES
90001 1133 06037 0624477 FLORENCE-GRAHAM
90001 1134 06037 0624477 FLORENCE-GRAHAM

Output:
Zip Plus4MIN Plus4Max PlaceName ZipRangeID
90001 0001 1000 FLORENCE-GRAHAM 1
90001 1001 1098 LOS ANGELES 2
90001 1100 1126 FLORENCE-GRAHAM 3
90001 1127 1128 LOS ANGELES 4
90001 1129 1129 FLORENCE-GRAHAM 5
90001 1130 1130 LOS ANGELES 6
90001 1131 1131 FLORENCE-GRAHAM 7
90001 1132 1132 LOS ANGELES 8
90001 1133 1166 FLORENCE-GRAHAM 9
90001 1167 1168 LOS ANGELES 10

This is just a sample dataset I manipulated for 1 zipcoden in MS Excel.

Can anyone help; anyone ever done this before? Any direction would be greatly appreciated.

Mark

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-08 : 05:34:56

One way of breaking it up is to use a cursor & in the loop, check if there is a change in say left(zip + Plus4,7), something like:
FETCH NEXT FROM zip_Cursor;
SELECT @previousKey = left(zip + Plus4,7)

WHILE @@FETCH_STATUS = 0
BEGIN
IF left(zip + Plus4,7) <> @previousKey
BEGIN
-- do something - new batch
END
ELSE
-- save this curent batch
END
SELECT @previousKey = left(zip + Plus4,7)
FETCH NEXT FROM zip_Cursor;
END;
You'll need to look up cursors for all the syntax. Cursors are a little slow, some gurus here may know a neat way of doing this using some other approach like a CTE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources