I am a bit new at sql server and am trying to resolve a coding issue that I have come across.

I hope there is someone out there that might be able to assist me.

I am working with zip code +4 data and am trying to range the data to reduce the size of the database from the current ~65 million lines of data.  The trick is that I need to range the data so that a single range created will include the same county and city.  The problem as illustrated below is that the data jumps back and forth with city names and therefore a simple min/max script does not work.

I believe that a looped stored procedure script needs to be used to identify each instance of a city below so that for example 90001 0001-0004 would be identified as 1, 0005-0008 would be indentified as 2, 0009-0012 would be 3, and 0013-0015 would be 4 - then a min max script would be able to capture this.  Any assistance would be terrific!

The data is currently arranged as such (example):

ZIP_CODE  PLUS_FOUR   COUNTY_NAME   CITY_NAME

90001	0001	LOS ANGELES	LOS ANGELES
90001	0002	LOS ANGELES	LOS ANGELES
90001	0003	LOS ANGELES	LOS ANGELES
90001	0004	LOS ANGELES	LOS ANGELES
90001	0005	LOS ANGELES	FONTAINE
90001	0006	LOS ANGELES	FONTAINE
90001	0007	LOS ANGELES	FONTAINE
90001	0008	LOS ANGELES	FONTAINE
90001	0009	LOS ANGELES	LOS ANGELES
90001	0010	LOS ANGELES	LOS ANGELES
90001	0011	LOS ANGELES	LOS ANGELES
90001	0012	LOS ANGELES	LOS ANGELES
90001	0013	LOS ANGELES	FONTAINE
90001	0014	LOS ANGELES	FONTAINE
90001	0015	LOS ANGELES	FONTAINE

Here is one way to do it.  I didn't spend a whole lot of time on this and I'm sure it can be optimized--it accesses the base table 4 times--but if it's just a one-time thing it shouldn't be too bad.

 

declare @z table
(
    zip5 char(5),
    zip4 char(4),
    county varchar(50),
    city varchar(50)
)

insert @z
select '90001', '0001', 'LOS ANGELES', 'LOS ANGELES'
union all select '90001', '0002', 'LOS ANGELES', 'LOS ANGELES'
union all select '90001', '0003', 'LOS ANGELES', 'LOS ANGELES'
union all select '90001', '0004', 'LOS ANGELES', 'LOS ANGELES'
union all select '90001', '0005', 'LOS ANGELES', 'FONTAINE'
union all select '90001', '0006', 'LOS ANGELES', 'FONTAINE'
union all select '90001', '0007', 'LOS ANGELES', 'FONTAINE'
union all select '90001', '0008', 'LOS ANGELES', 'FONTAINE'
union all select '90001', '0009', 'LOS ANGELES', 'LOS ANGELES'
union all select '90001', '0010', 'LOS ANGELES', 'LOS ANGELES'
union all select '90001', '0011', 'LOS ANGELES', 'LOS ANGELES'
union all select '90001', '0012', 'LOS ANGELES', 'LOS ANGELES'
union all select '90001', '0013', 'LOS ANGELES', 'FONTAINE'
union all select '90001', '0014', 'LOS ANGELES', 'FONTAINE'
union all select '90001', '0015', 'LOS ANGELES', 'FONTAINE'

select
    z_min.zip5,
    z_min.zip4 as min_zip4,
    (
        select top(1)
            z3.zip4
        from @z z3
        where
            z3.zip5 = z_min.zip5
            and z3.zip4 >= z_min.zip4
            and z3.county = z_min.county
            and z3.city = z_min.city
            and not exists
            (
                select *
                from
                (
                    select top(1)
                        z4.county,
                        z4.city
                    from @z z4
                    where
                        z4.zip5 = z3.zip5
                        and z4.zip4 > z3.zip4
                    order by z4.zip4 asc
                ) y
                where
                    y.city = z3.city
                    and y.county = z3.county
            )
        order by z3.zip4 asc
    ) as max_zip4,
    z_min.county,
    z_min.city
from
(
    select
        z1.zip5,
        z1.zip4,
        z1.county,
        z1.city
    from @z z1
    where
        not exists
        (
            select *
            from
            (
                select top(1)
                    county,
                    city
                from @z z2
                where
                    z2.zip5 = z1.zip5
                    and z2.zip4 < z1.zip4
                order by zip4 desc
            ) x
            where
                x.county = z1.county
                and x.city = z1.city
        )
) z_min

 

"I believe that a looped stored procedure script needs to be used to identify each instance of a city"
A simple loop or cursor is the most intuitive idea to solve this. Unfortunately there is no other construct sql server offers that solves this problem in a simple fashion. If you see this a grouping problem where you have to great virtual groups over cities that can repeat sql does offer a simple and elegant solution. Unfortunately sql server has not implemented the full sql window (OVER) that would easily solve this. For more background on the nature of this type of problem and an easy sql server solution that mimics the sql window see my reply in the thread:

microsoft.public.sqlserver.programming
Jul 23, 2008
'Roll Up IP-Location Database'
http://tinyurl.com/6dhfkv

You can get into it further with:

http://beyondsql.blogspot.com/2007/06/sql-using-dense-rank-for-identifying.html
http://beyondsql.blogspot.com/2008/04/sql-ranking-overture.html
http://beyondsql.blogspot.com/2007/08/dataphor-creating-super-function.html