Thank you very much! matt

Ok thanks Jeff - I am going to try, and try hard, to implement multiple CTEs like you suggest.. (gulp, wish me luck!) - Matt

Thanks Jeff! - p.s. is my sql ok? I repeat the fnDistance() function three times..

Matt

Wow, Jeff, you are the man! I will try to implement the "narrow corridor".. so i should just calculate the Max vars and add "WHERE Lat2 BETWEEN @MaxNorthLat...

Wow! I wouldn't have guessed that the formula in the query would be faster!

Michael - This topic utilized the same code, but was about a 'clustered index scan' ... i suppose I should have categorized it with the first post.. sorry! -...

thank you very much - will try your code..

my clustered index is on (zip, long, lat) .. yes, changing to WHERE clause to t2 results in a...

but Lowell - i've read that an index scan == table scan!

Matt,

NYC

Yes, Zip code is in an index..

fnGetDistance (latitude1, long1, lat2, long2):

===============

SELECT @Distance = 3963.0 * ACOS(SIN(RADIANS(@Lat1)) * SIN(RADIANS(@Lat2)) + COS(RADIANS(@Lat1)) * COS(RADIANS(@Lat2)) * COS(RADIANS(@Long2 - @Long1)))

ok.. thanks!

thank you.. But.. for my original post and question - how can I use a UDF to simplify? tx!

Michael Valentine Jones (10/19/2009)

---

Dave Ballantyne (10/19/2009)

---

a sarg is a searchable argument. By making the Longitude and latitude searchable as below, this will quickly eliminate all those zipcodes outside...

Dave Ballantyne (10/19/2009)

---

I would wrap it in a Cte and then filter on the result of that.

However , what will hurt your performance more is unnecessary calculations.

In my Sarg i...

Now I understand!!! Thanks a lot J-F!!!

Matt, NYC

Thank you - that helps. For some reason the CASE statement below, for sorting, is not resulting in the specified sort order.. any ideas? tx Matt

SELECT * FROM

(

SELECT...