Sql Server update based on an aggregate from two tables with two variables each

Question

this is my first time posting a question and I hope not to waste any of your time. Thanks in advance for any help.

I am attempting to calculate the distance between every zip-code in the United States and a list of 495 buildings (384 unique). I already have the latitude/longitude and an approximation for the distance. My problem comes in the form of not being sure how to update the rows in the table individually. For instance, the set function I use will change all the rows in the table to the most recently calculated value. I am using Sql Server 2008 R2, and have the following tables with the following columns: tbl_Zip_Code_Coordinates w/ Zip, Lat, Long, State, Closest_Building, Zip_ID; Tbl_Building_Coordinates w/ Zip, Lat, Long, Store_ID

Here is the code I am currently attempting

Declare @ZipID int = 1
DECLARE @ZipLat Decimal (12,6)
DECLARE @ZipLong Decimal (12,6)

while (@ZipID < 43194) 
Begin
Set @ZipLat = (Select Lat from Zip_Code_Coordinates where Zip_ID = @ZipID)
Set @ZipLong= (Select Long from Zip_Code_Coordinates where Zip_ID = @ZipID)
Update Zip_Code_Coordinates 
set Closest_Building = (select min(Sqrt( SQUARE((@ZipLat - Buildings_Coordinates.lat)*68.96799738887665) 
                                +SQUARE((@ZipLong - Buildings_Coordinates.Long)*54.69366983621222))) 
                  from Buildings_Coordinates, Zip_Code_Coordinates
                  where  Zip_ID = @ZipID
                  )
set @ZipID = @ZipID + 1

end

Answer 1

I suppose you also need the ID for the closest building. If so, you could use the following code as is or with some modifications:

;WITH mycte 
     AS (SELECT Z.zipid, 
                B.bldgid, 
                (( Sqrt(Square((Z.lat - B.lat)*68.96799738887665) 
                        + Square((Z.long - B.long)*54.69366983621222)) )) Dist, 
                Row_number() 
                  OVER ( 
                    partition BY zipid 
                    ORDER BY ((Sqrt( Square((Z.lat - B.lat)*68.96799738887665) + 
                  Square(( 
                  Z.long 
                  - B.long)*54.69366983621222))))) 
                DistOrder 
         FROM   Buildings_Coordinates B 
                CROSS JOIN Zip_Code_Coordinates Z) 
UPDATE z 
SET    closest_buildingid = bldgid, 
       closest_bldgdistance = dist 
FROM   Zip_Code_Coordinates Z 
       INNER JOIN mycte C 
               ON Z.zipid = C.zipid 
WHERE  distorder = 1

Answer 2

I eventually found a code that worked:

Select  Sub2.Zip_ID, Sub2.Distance into Temp_Table from
(
select
Sub1.Zip_ID as Zip_ID,
MIN(Sub1.Distance) as Distance from
    (
    SELECT 
    Z.Zip_ID, 
       ((Sqrt(Square((Z.lat  - S.lat) *68.96799738887665) 
         + Square((Z.long - S.long)*54.69366983621222)))) Distance
    FROM   
    Tbl_Stores_Coordinates S
    CROSS JOIN Zip_Code_Coordinates Z
    )Sub1
group by Sub1.Zip_ID) Sub2

Update Zip_Code_Coordinates 
Set Closest_Store = temp_Table.Distance
from temp_Table
where Zip_Code_Coordinates.Zip_ID = Temp_Table.Zip_ID;

drop table temp_Table;
select * from Zip_Code_Coordinates
select * from Temp_Table

The only problem I had with this was that I couldn't pass the which Building has the shortest distance to each Zip Code through the Subquery. What I ended up doing was creating a temporary table with another query and performed a Join where the Distances and Zip_ID's were the same.