Loop through a table and assign a value to a global variable - 01-14-2005 , 10:23 AM
I have 2 tables: 1 contains postcodes and map coordinates - tblpostcodes.
The other contains new locations with postcodes and map coordinates
tblpostcodes - tblnewLocations. eg:
ID Postcodes Col1 Col2
1 GU6 8NQ 504.5 139
2 GL20 5PA 389.2 232.6
ID address postcode coords1 coords2
661 1 street GU1 3BJ 500 150
657 26 street GL7 1JH 402 202
What I'm trying to do is set up a dts package, where I can select the top 3
by distance using a where query of the first postcode.
This is the sql query I'm using for the source:
SELECT TOP 3 tblpostcodes.collectpointID, tblpostcodes.col1,
tblpostcodes.col2, tblpostcodes.store, tblpostcodes.postcode,
SQRT(POWER(tblpostcodes.col1 - CONVERT(REAL, tblnewLocations.coord1), 2) +
POWER(tblpostcodes.col2 - CONVERT(REAL, tblnewLocations.coord2), 2)) AS dist,
tblnewLocations.postcode AS Expr1, tblnewLocations.address,
FROM tblpostcodes CROSS JOIN tblnewLocations
WHERE tblpostcodes.postcode = ?
GROUP BY tblpostcodes.collectpointID, tblpostcodes.col1, tblpostcodes.col2,
tblpostcodes.store, tblpostcodes.postcode, tblnewLocations.coord1,
tblnewLocations.coord2, tblnewLocations.postcode, tblnewLocations.address,
ORDER BY dist
But what I'm having trouble working out how I can loop through all the
tblpostcodes.postcode values and pull the nearest 3 new locations and add the
results to a new table - tblNearestNewLoc. eg
ID Col1 Col2 Postcode Dist T_postcode Address
2 389.2 232.6 GL20 5PA 0.44 GL20 5PX 95 HIGH STREET 389
2 389.2 232.6 GL20 5PA 10.96 GL51 7DB 74 TENNYSON ROAD 392
2 389.2 232.6 GL20 5PA 11.63 GL50 2TL UNIT 1 ANDOVER ROAD 394 222
If I add a value to the global variable by hand it adds 3 new rows, but I
want that to loop through all tblpostcodes.postcode values.
Any help would be great
RE: Loop through a table and assign a value to a global variable - 01-14-2005 , 11:31 AM
Either you can use a cursor and insert the values one at a time...
Or you can use a 3/4 level deep set of subqueries/User Defined Functions
Innermost subquery (or UDF):
Return... min(dist) minDist, tblPostCodes.PostCode, excluding any records
that are already in the tblNearestNewLoc based on PostCode & t_PostCode.
(using "not exists" subquery or a left exclusion join. personally I like the
exclusion better than not exists).
This returns you the closest records distance to you
Next subquery or (UDF):
Take the minDist and PostCode, and join that results set to your query on
sub.minDist = Dist and Sub.PostCode = tblPostCodes.PostCode to find the
nearest record that isn't in the tblNearestNewLoc. Return results....
min(tblNewLocations.PostCode) as minCode, tblPostCodes.PostCode.
This returns the closest tblNewLocations.PostCode and ensures that if two or
more records have the exact same distance you are only returning one of them.
Insert Into TblNearestNewLoc(....)
inner join (Subqueries or UDFs) Sub on sub.PostCode =
inner join TblNewLocations on tblNewLocations.PostCode = Sub.minCode
You have just inserted the closest record that is not already in the database.
Repeat insert two more times. You now have the nearest three locations.
Hope this helps and is not too convoluted.
RE: Loop through a table and assign a value to a global variable - 01-17-2005 , 09:29 AM
Thanks Ryan I managed to work it out from what you provided