SQL Between in where clause help. Very Urgent!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Between in where clause help. Very Urgent!

Author

Topic

jeffmdemers
Starting Member

4 Posts

Posted - 2010-11-18 : 12:25:30

This is very urgent, hopefully someone can help with my understand of this! It would be very much appreciated thanks!

The problem is that i am using a between in my where clause and for some reason it is not recognizing that certain numbers are actually between others. For clarification, I print out the variables used in the code at the bottom.

Any insight?


Declare @RADIUSDEGREES float
Declare @LATITUDE float
Declare @LONGITUDE FLOAT

SET @RADIUSDEGREES = (50 / 69.0468) --1 degree of Lat = 60 minutes = 60 nm = 69.04677 statute miles (approx)
SET @LATITUDE = (39.887687)
SET @LONGITUDE = (-75.233918)

SELECT 1
where
	    -75.233918 between @LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE) AND 
		 @LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE) 

		
		

select  
	@LATITUDE as origlat, @LONGITUDE as origLong,
	@LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE) as longlow,
	@LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE) as longhigh

OrigLat:39.887687
OrigLong:-75.233918
longLow:-76.4841148868476
longHigh:-73.9837211131524

Isn't -75 between -76 and -73?!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 12:30:48

wat about this? i think you had ordered changed



SELECT 1
where
	    -85.907023 between @LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE)  AND @LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 12:33:37

-75 is between -76 and -73

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jeffmdemers
Starting Member

4 Posts

Posted - 2010-11-18 : 12:36:08

I posted to hastily, had to change the number I was searching for.

Here is how I found the problem.

If I have the numbers like this:

SET @RADIUSDEGREES = (50 / 69.0468) --1 degree of Lat = 60 minutes = 60 nm = 69.04677 statute miles (approx)
SET @LATITUDE = (36.974371)
SET @LONGITUDE = (-85.907023)

SELECT 1
where
-85.907023 between @LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE) AND
@LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE)

No results.

But like this:
SET @RADIUSDEGREES = (50 / 69.0468) --1 degree of Lat = 60 minutes = 60 nm = 69.04677 statute miles (approx)
SET @LATITUDE = (39.887687)
SET @LONGITUDE = (-75.233918)

SELECT 1
where
-75.233918 between @LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE) AND
@LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE)

I get results.

The problem is the longlow is -84 and the longhigh is -86 on the second one, and for a between statement need the low one to be listed first. However this is for searching within x miles of two zipcodes for customers, and I need both zipcodes (19153, and 42141) to return the proper results.

Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 12:40:25

[code]
DECLARE @Long1 float,@Long2 float
SET @RADIUSDEGREES = (50 / 69.0468) --1 degree of Lat = 60 minutes = 60 nm = 69.04677 statute miles (approx)
SET @LATITUDE = (36.974371)
SET @LONGITUDE = (-85.907023)

SELECT @Long1= CASE WHEN (@LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE)) > (@LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE)) THEN @LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE) ELSE @LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE) END,
@Long2 = CASE WHEN (@LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE)) < (@LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE)) THEN @LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE) ELSE @LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE) END

SELECT 1
where
-85.907023 between @Long1 AND
@Long2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jeffmdemers
Starting Member

4 Posts

Posted - 2010-11-18 : 12:50:34

quote:

Originally posted by visakh16


DECLARE @Long1 float,@Long2 float
SET @RADIUSDEGREES = (50 / 69.0468) --1 degree of Lat = 60 minutes = 60 nm = 69.04677 statute miles (approx)
SET @LATITUDE = (36.974371)
SET @LONGITUDE = (-85.907023)

SELECT @Long1= CASE WHEN (@LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE)) > (@LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE)) THEN  @LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE) ELSE @LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE) END,
@Long2 = CASE WHEN (@LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE)) < (@LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE)) THEN  @LONGITUDE - @RADIUSDEGREES/COS(@LATITUDE) ELSE @LONGITUDE + @RADIUSDEGREES/COS(@LATITUDE) END


SELECT 1
where
-85.907023 between @Long1 AND 
@Long2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Great! That did it! Thank you so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 12:52:17

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources