CODEWITHSUNDEEP
sql-server
Karvy1
Karvy1

Reputation: 1055

Calculating distance between 2 zips by using a function

I have a table of which first 3 rows look like: 

ship_to_zip Knoxville_Zip   Phoenix_Zip
52773       37909           85009
46341       37909           85009
83114       37909           85009

I have a function that calculates distance in miles between 2 zips: dbo.ufnzipcodedist_2012(zip1,zip2)

Now I want to add 2 more columns to my table: Miles_from_Knoxville and Miles_from_Phoenix, each which calculates miles between ship_to_id and Knoxville_Zip/Phoenix_Zip respectively.

I tried below:

select IDENTITY(Int,1,1) ID,*,CAST(0 as float) dist1,CAST(0 as FLOAT) DIST2
INTO #TEMP
from #zip

declare @COUNT INT
DECLARE @DIST1 FLOAT
DECLARE @DIST2 FLOAT

set @COUNT=1

while (@COUNT<=2)
begin
    SELECT @DIST1=dbo.ufnzipcodedist_2012(SHIP_TO_ZIP,KNOXVILLE_ZIP)
            ,@DIST2=dbo.ufnzipcodedist_2012(SHIP_TO_ZIP,PHOENIX_ZIP)
    FROM #TEMP

    UPDATE t SET T.DIST1=@DIST1,t.DIST2=@DIST2
    FROM #TEMP t
    WHERE ID=@COUNT

set @COUNT=@COUNT+1
end

It is going into a infinite loop and columns DIST1, DIST2 are populated with 0s. Where did I go wrong?

Upvotes: 0

Views: 72

Answers (1)

ProblemSolver
ProblemSolver

Reputation: 644

Please Modify your query as below:

        SELECT IDENTITY(INT, 1, 1) ID
            ,*
            ,CAST(0 AS FLOAT) dist1
            ,CAST(0 AS FLOAT) DIST2
        INTO #TEMP
        FROM #zip

        DECLARE @COUNT INT
        ,@DIST1 FLOAT
        ,@DIST2 FLOAT
        ,@MAXID INT

        SET @COUNT = 1

        SELECT @MAXID = MAX(ID)
        FROM #TEMP

        WHILE (@COUNT <= @MAXID)
        BEGIN
            SELECT @DIST1 = dbo.ufnzipcodedist_2012(z1, z2)
                ,@DIST2 = dbo.ufnzipcodedist_2012(z2, z3)
            FROM #TEMP
            WHERE ID = @COUNT

            UPDATE t
            SET T.DIST1 = @DIST1
                ,t.DIST2 = @DIST2
            FROM #TEMP t
            WHERE ID = @COUNT

            SET @COUNT = @COUNT + 1
        END

        SELECT *
        FROM #TEMP

Here is a link to SQL fiddle:http://sqlfiddle.com/#!6/b5699/3

Upvotes: 1

Related Questions