Chris Morris (12/5/2008)

---

Did you try this:

DECLARE @Boston_prdID (numeric)

SET @Boston_prdID = 555

SELECT p.*, c.*, d.DistanceToBoston AS [MinDistanceToBoston]

FROM PRODUCTS p

INNER JOIN OTM o ON o.prdID = p.prdID

INNER JOIN CITIES c ON...

:blush:

my mistake. Just replace latitude by lambertX and longitude by lambertY in the CITIES definitions.

Sorry!

Chris

here you go 🙂

--creation of the getDistanceFromID scalar function

CREATE FUNCTION [dbo].[getDistanceFromID]

(

@localID numeric, @localX float, @localY float

)

RETURNS float

AS

BEGIN

DECLARE @i float

DECLARE @lX float,...

Chris,

Just read the Best practices,...

Chris Morris (12/5/2008)

---

Here's a query which will work.

[font="Courier New"]DECLARE @Boston_prdID (numeric)

SET @Boston_prdID = 555

SELECT p.*, c.*

FROM PRODUCTS p

INNER JOIN OTM o ON o.prdID = o.prdID

INNER JOIN CITIES c...

Yes unfortunately.

I've heard of the OVER PARTITION option that's available...

To give you an idea, I tried this subquery to retrieve an individual product and the distance to the nearest location. It works great as is but as you can...

Chris Morris (12/5/2008)

---

Ok lets confirm this:

Say your reference city is Boston, your requirement would be:

For each and every individual product (which has been sold), list the nearest city to Boston...

Hi ChrisM,

That's it exactly !!!

Chris