November 19, 2004 at 8:47 pm
Hello all,
I've been a lurker here for a while and have appreciated solutions found here. But I've found one that is making me pull my hair out.
Ok ... I have a table which looks like this:
ZIP PLS4LOW PLS4HI CONGRESSIONALDISTRICT
01054 0001 NULL 1st some state
01054 0002 NULL 1st some state
01054 0003 NULL 2nd some state
01054 0004 NULL 1st some state
01054 0005 NULL 3rd some state
The zip + 4 converts to a congressional district. The issue is this -- I'd like to query the database so that instead of being individual records for each zip+4, I want to have each record show a range (pls4low to pls4hi). The above would end up as:
01054 0001 0002 1st some state
01054 0003 0003 2nd some state
01054 0004 0005 1st some state
Luckily, most of the zip codes map directly 1 zip code=1 congressional district, so I only have 1 record for each of those zip codes -- 54,000 records. However, for just shy of 9,000 zip codes, I have from 2,000 to 4,000 records for each zip code, giving me just over 36 million records in the table when the data collection is complete. Way too many.
I tried a variation of select min(plus4low), max(plus4low) to update the records, but in some cases (including the example below) that would ignore the gaps in the middle of the range which have a different CD record.
Any ideas?
November 19, 2004 at 8:48 pm
Oh -- one other tidbit I forgot to mention. The records do have a seperate unique id field as primary key. The zips are not necessarily list in the table sequentially.
November 19, 2004 at 11:26 pm
Read the following article:
http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp
It solves a similar problem and you may find some interesting ideas there.
Razvan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply