Hi,
I have 2 tables: 1 contains postcodes and map coordinates - tblpostcodes.
The other contains new locations with postcodes and map coordinates
tblpostcodes - tblnewLocations. eg:

tblpostcodes
------------
ID Postcodes Col1 Col2
1 GU6 8NQ 504.5 139
2 GL20 5PA 389.2 232.6

tblnewLocations
---------------
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,
tblnewLocations.coord1,
tblnewLocations.coord2
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,
tblnewLocations.coord1,
tblnewLocations.coord2
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

tblNearestNewLoc
----------------
ID Col1 Col2 Postcode Dist T_postcode Address
coords1 coords2
2 389.2 232.6 GL20 5PA 0.44 GL20 5PX 95 HIGH STREET 389
233
2 389.2 232.6 GL20 5PA 10.96 GL51 7DB 74 TENNYSON ROAD 392
222
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
Nik

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
(UDFs)

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.

Outermost query/Insert:
Insert Into TblNearestNewLoc(....)
Select ....
From TblPostCodes
inner join (Subqueries or UDFs) Sub on sub.PostCode =
TblPostCodes.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.

Ryan

"Nik" wrote:

Thanks Ryan I managed to work it out from what you provided

Nik

"Ryan" wrote: