Workup:
I have a (table) function
getZips(@ZipCode, @RangeToCheckInMiles)
that returns a list of zip codes within x miles of a zip code that you pass to the function.
I have a temp table that has two zip codes in it.
Problem:
I need to update a flag column if Zip1 is close to zip2.
I tried joining on the table function, but won't work, because i need to pass a value from table 1 to the function.
Here is what i have so far:
-Sometimes the answer to your question is the hack that works
I have a (table) function
getZips(@ZipCode, @RangeToCheckInMiles)
that returns a list of zip codes within x miles of a zip code that you pass to the function.
I have a temp table that has two zip codes in it.
Problem:
I need to update a flag column if Zip1 is close to zip2.
I tried joining on the table function, but won't work, because i need to pass a value from table 1 to the function.
Here is what i have so far:
Code:
Select idCol, zip1, zip2, 'N' as CloseToEachOther
Into #tmp
From table1 t1
join table2 t2 on
1=1
DECLARE @idCol int
DECLARE @zip1 varchar(10)
DECLARE @zip2 varchar(10)
DECLARE compareZips CURSOR for
SELECT idCol, zip1, zip2
FROM #tmp tmp
OPEN CompareZips
FETCH NEXT FROM CompareZips
INTO @idCol, @zip1, @zip2
WHILE @@FETCH_STATUS = 0
BEGIN
Update t
set t.CloseToEachOther = 'Y'
FROM #tmp t
Join dbo.GetZips(@zip1, 100) zips on
t.idCol = @idCol
and left(@zip1, 5) = zips.Zip1
and left(@zip2, 5) = zips.Zip2
FETCH NEXT FROM CompareStudentZipToDLeader
INTO @idCol, @zip1, @zip2
END
CLOSE CompareZips
DEALLOCATE CompareZips
select * from #tmp
-Sometimes the answer to your question is the hack that works