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

• start mile
• start yard
• end mile
• end yard

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:

• start mile = 234

• start yard = 2954

• end mile = 236

• end yard =2342

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

---

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

---

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.

---

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

---

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)