CODEWITHSUNDEEP
sql-server
happyneil
happyneil

Reputation: 75

querying data where 2 columns number

I have this issue that I can't seem to get my head around... Basically I have a form that the user inputs a

I am trying to query my SQL Server database using the values, that the user inputs... I can retrieve the information fine, but I just need help with the query.

The issue is the data is in the following format in the database:

 ID | mile | yard | gps lat | gps long | rotten | split | wheel_cut | broken | quality |
  1, 234, 2342,   35.23143, -123.32423, 1, 2, 1, 2, 2 
  2, 234, 2954,   35.23143, -123.32423, 1, 2, 1, 2, 2 
  3, 234, 2994,   35.23143, -123.32423, 1, 2, 1, 2, 2 
  4, 235,   42,   35.23143, -123.32423, 1, 2, 1, 2, 2 
  5, 235, 2842,   35.23143, -123.32423, 1, 2, 1, 2, 2 
  6, 236,  242,   35.23143, -123.32423, 1, 2, 1, 2, 2 
  7, 236, 2342,   35.23143, -123.32423, 1, 2, 1, 2, 2 
  8, 237,  282,   35.23143, -123.32423, 1, 2, 1, 2, 2 
  9, 238, 2342,   35.23143, -123.32423, 1, 2, 1, 2, 2 
 10, 238, 4342,   35.23143, -123.32423, 1, 2, 1, 2, 2 
 11, 239,  742,   35.23143, -123.32423, 1, 2, 1, 2, 2

I need to query both mile and yard at the same time, so for example the user types in:

The query should output IDs 3, 4, 5, 6 and 7.

If I just query WHERE mile > 234 AND yard > 2954... It will ignore ID 4 because the yard is less than 2954.

SELECT   
    id, mile,  yard, gps_lat, gps_long, rotten, split, wheel_cut, broken, quality
FROM    
    database
WHERE 
    gps_lat IS NOT NULL 
    AND (???????)
ORDER BY 
    mile, yard.

Is there anyone that can help me with this query? Any advice would be welcomed!

I hope I explained the situation clearly enough....

Cheers,

Neil

Upvotes: 1

Views: 91

Answers (4)

happyneil
happyneil

Reputation: 75

Thank you to all that responded! I ended up using the below in the end... Cheers Neil

--set values
declare @startmile_int AS int
declare @endmile_int AS int
declare @startyard_int AS int 
declare @endyard_int AS int
declare @startyard AS int
declare @endyard AS int

SET @startmile_int=529 
SET @endmile_int=535
SET @startyard_int=34 
SET @endyard_int=406

--turn miles into yards
set @startyard=@startyard_int+(@startmile_int*1760)
set @endyard=@endyard_int+(@endmile_int*1760)

SELECT   id, mile,yard, gps_lat, gps_long, rotten, split, wheel_cut, broken, quality
FROM         database WHERE gps_lat IS NOT NULL AND yard+(mile*1760) between @startyard and @endyard ORDER BY mile, yard

Upvotes: 1

datagod
datagod

Reputation: 1051

I think the problem is caused by storing distance in two columns, in two different formats.

Can you store miles as a decimal and remove the yards column?

If not, what if you converted Mile and Yard columns to Feet?

select *
  from YourTable
 where ((Mile * 5280) + (Yard * 3)) 
  between ((StartMile * 5280) + (StartYard +3))
      and ((StopMile * 5280) + (StopYard * 3))

Upvotes: 3

Anthony Faull
Anthony Faull

Reputation: 17957

If we avoid doing calculations then the optimizer can use an index and the query may execute quicker.

WHERE (mile > @startMile OR mile = @startMile AND yard >= @startYard)
AND (mile < @endMile OR mile = @endMile AND yard <= @endYard)

Upvotes: 0

DForck42
DForck42

Reputation: 20357

this isn't the best way, but you could try converting your mile data points into yards, then adding both datapoints, and then selecting between the start and stop yardage.

so for example:

declare @startmile int, @endmile int, @startyard int, @endyard int

set @startyard=@startyard+(@startmile*1760)
set @endyard=@endyard+(@endmile*1760)


select *
from dbo.Table_1
where yard+(mile*1760) between @startyard and @endyard

there's probably a better way to write that, but i can't think of it.

Upvotes: 1

Related Questions