Determine if a couple of zip codes are contiguous?

Yes, it is possible to obtain the ranges from the list with a single query. For that you will use a CTE ranking and a bit of grey matter.

Yes, it is possible to obtain the ranges from the list with a single query. For that you will use a CTE, ranking, and a bit of grey matter: WITH ranked AS ( SELECT Zip, AreaCode, ZipGroup = CAST(Zip AS int) - ROW_NUMBER() OVER (PARTITION BY AreaCode ORDER BY Zip) FROM EnteredZip ) SELECT StartZip = MIN(Zip), EndZip = MAX(Zip), AreaCode FROM ranked GROUP BY AreaCode, ZipGroup Output: StartZip EndZip AreaCode -------------------- -------------------- ------------------------- 00544 00544 Area1 00601 00602 Area1 00604 00604 Area2 00606 00606 Area2 00501 00501 Area3 00544 00544 Area3 00601 00604 Area3 00610 00612 Area3 This output doesn't match yours, but it does match the source data. UPDATE If the Zip table is a reference table for determining the contiguity of a list of zip codes, then the above solution should be modified like this: WITH ZipRanked AS ( SELECT Zip, State, ZipRank = ROW_NUMBER() OVER (PARTITION BY State ORDER BY Zip) FROM Zip ), EnteredZipRanked AS ( SELECT e.

Zip, e. AreaCode, ZipGroup = z. ZipRank - ROW_NUMBER() OVER (PARTITION BY e.

AreaCode ORDER BY e. Zip) FROM EnteredZip e INNER JOIN ZipRanked z ON e. Zip = z.

Zip ) SELECT StartZip = MIN(Zip), EndZip = MAX(Zip), AreaCode FROM EnteredZipRanked GROUP BY AreaCode, ZipGroup.

The output in the sample data is correct now. The query you gave cant work because the table Zip is necessary. For instance the Area1 has StartZip 00544 and Endzip 00602, because there exists no other zip code between these two codes then 00601.

Sry for making this harder then necessary by giving the wrong input data. – Malcolm Frexner Jul 24 '11 at 22:08 1 I understand the point, although the Zip table in your sample doesn't contain the 00601 zip code, which is present in the entered zip code list. Is this possible in a real-life situation or did you just omit the code by mistake?

– Andriy M Jul 24 '11 at 22:18 Good point: I corrected the input data. – Malcolm Frexner Jul 24 '11 at 22:30 @Malcolm Frexner: Please check the updated answer. – Andriy M Jul 24 '11 at 22:36 Beautifull!

I just had to remove "e. AreaCode = z. State AND".

The state in the Zip table doesn't have a meaning for my query. – Malcolm Frexner Jul 24 '11 at 22:55.

First, I need to tell you that what I think you're planning to do strikes me as a bad idea. The table EnteredZip is just fine for storing which area a ZIP code belongs to. (As long as you put a PRIMARY KEY constraint on ZIP.) It looks like this is roughly where you're aiming, select areacode, min(zip), max(zip) from enteredzip group by areacode order by areacode but it doesn't match your output.

Frankly, your sample output doesn't make sense to me. There's only one row for Area1, but the ZIP codes aren't contiguous. There are two rows for Area2, but each has a single ZIP code.

There are two rows for Area3, but the ZIPs aren't contiguous. Wait .. . Does contiguous mean you inserted a blank line between the INSERT statements in the sample data?

If that's the case, then you need to store more data. You have to identify which ZIP codes should be considered contiguous, and store those facts in a table.(Also, you left out a blank line in Area2. ).

I edited the sample output to be more easy to read. I see the point you make about not being a good idea. I just want to try if it solves my performance problem.

Maybe I have a chance if the regions are realy big. Indexes are already in place but that wasnt enough. – Malcolm Frexner Jul 23 '11 at 12:05.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.