Programmatically populating a table with detail

[annethorne]

Hi,

We are creating an ASP Database Application that connects to Sql Server 2000.

The application is for associating a zip code with a warehouse. For instance a zip code for the los angeles area would go to the los angeles warehouse and a zip code for the new york area would go to the new

york warehouse. We would like to enter zip code ranges into the following Wzip_Log table. Then we would like to programmatically populate the Wzip_Detail table and the Wzip table.

The Wzip_Log table holds zip code ranges, where as the Wzip_Detail and the Wzip table hold individual zip codes.

For instance, the Wzip_Log table might have the following record (completely fictitious)
Wzip_Log_ID: 1
PostalCode_Start: 11111
PostalCode_End: 11113
CountryCode: USA
WarehouseNum: 1
Operator: jdoe
Time_Stamp: 00:00:00 6/1/2006

Then we would want the Wzip_Detail table to be populated as follows:
Wzip_Detail_ID: 1
Wzip_Log_ID: 1
PostalCode: 11111
CountryCode: USA
WarehouseNum: 1

Wzip_Detail_ID: 2
Wzip_Log_ID: 1
PostalCode: 11112
CountryCode: USA
WarehouseNum: 1

Wzip_Detail_ID: 3
Wzip_Log_ID: 1
PostalCode: 11113
CountryCode: USA
WarehouseNum: 1

The Wzip table would be populated exactly the same as the Wzip_Detail table at this point.

However, say a user later updated the Wzip_Log table as follows:
Wzip_Log_ID: 2
PostalCode_Start: 11111
PostalCode_End: 11113
CountryCode: USA
WarehouseNum: 2
Operator: jdoe
Time_Stamp: 01:10:00 6/4/2006

The Wzip_Detail table would contain an INSERT of this data (giving it a total of 6 records)
whereas the Wzip table would contain an UPDATE of this data (giving it a total of 3 records)


For your reference, here are the table layouts:


Table Name: Wzip_Log

Wzip_Log_ID int 4 (primary key, Identity)
PostalCode_Start varchar 10
PostalCard_End varchar 10
CountryCode varchar 3
WarehouseNum int 4
Operator varchar 50
Time_Stamp datetime 8

Table Name: Wzip_Detail

Wzip_Detail_ID int 4 (primary key, Identity)
Wzip_Log_ID int 4 (foreign key)
PostalCode varchar 10
CountryCode varchar 3
WarehouseNum int 4


Table Name: Wzip

Wzip_ID int 4 (primary key, Identity)
Wzip_Log_ID int 4 (foreign key)
Wzip_Detail_ID int 4 (foreign key)
PostalCode varchar 10
CountryCode varchar 3
WarehouseNum int 4


I think there is probably some way to do this programmatically in Sql Query Analyzer, but are not sure how, so I thought I would ask you here who have helped me tremendously in the past.

I am eager to learn more about writing such functions and am enjoying this project.

Thanks in advance for any help you can give,
Anne

 

[gmmastros]

I'm confused, so please permit me to ask a follow up question:

What's the point? It sounds like you want to find a 'close' warehouse for a given zip code. If that's the case, storing a range of zip codes for a warehouse is not a good idea. The problem is that zipcodes are not linear. You cannot draw a straight line between all the cities in the US. Furthermore, 2 adjacent zipcodes may not have contiguous numbers either.

My suggestion would be to obtain a ZipCode table that also has latitude/longitude values associated with them. Then, make sure you have the zipcode for each warehouse. You'll then be able to perform distance calculations based on latitude/longitude to determine the nearest warehouse.

I once found a database that has zipcode, latitude, * longitude on the internet using a google search. There have also been threads in this forum that will show you how to use SQL Server to perform distance calculations on latitude/longitude to find the closest warehouse.

If you'd like to pursue this, let me know and I will be able to help further.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom