We have a list of retail store locations for different retail stores throughout the United States. We also have a list of addresses for all of our customers. Given the zip codes of a list of retail store locations get a list of all customer information for customers that live within X miles of one of the locations.
NOTE: We will supply latitude and longitude for each zip code.
## Deliverables
Given the data in tblStoreLocations, use [login to view URL] to calculate the distance between each zip code in tblAddresses, returning all customer information in tblAddresses for each zip code that is within X miles. We would like this to be written in such a way that it can easily be run repeatedly (most likely a stored procedure) with parameters that allow us to easily change the StoreID and distance. It also needs to run as fast as possible. Ideally we would like it run run in a few minutes, but _we may choose_ to accept solutions that require longer to execute. Regardless of execution time, this needs to run without causing excessive locking, as this would compromise performance of other websites and applications.
What makes it challenging is that we have some stores with a ton of locations (like over 1,500), and we have a fair number of customers (about 25,000 distinct zip codes). That means there are roughly 38 million combinations to compare. The server this will be running on is powerful enough that this *should* run relatively quickly. You will be developing on a slower non-production server though. Here is the result set we want:
[login to view URL]
[login to view URL]
[login to view URL]
[login to view URL]
[login to view URL]
[login to view URL]
[login to view URL]
[login to view URL]
[login to view URL]
[login to view URL]
[login to view URL]
[login to view URL]
[login to view URL]
[login to view URL]
[login to view URL] (rounded to one decimal place)
If a particular address record is found to be within X miles of more than one retailer, the closest retailer should be matched. The extraneous matches can be discarded.
The "product" here can be either a stored procedure or some kind of software. We are running Microsoft SQL Server 2000. If you want to give us an application, all source code will be required. The server is running the Windows platform and has the .Net Framework installed (2.0) as well as the JRE v.6.
## Platform
Operating System: Windows
SQL Server: Microsoft SQL Server 2000