Min value

CurrentStage resides in the BUSLOCATION table and UserStage resides in the USERLOCATION table.

I need to find out the MIN difference between these two columns

i.e UserStage-CurrentStage=SHOULD BE THE MINIMUM VALUE.

Userstage is just one value but then it shud be subtracted from all the CurrentStage values in the table and the CurrentStage value which gives me the least difference should be extracted out.

Hope i didn't confuse u all too much :P anyone any ideas?so something like

select t1.UserStage - t2.CurrentStage from
BUSLocation as t1
join UserLocation as t2 on whatever your join is
Where t1.UserStage - t2.CurrentStage = min(t1.UserStage - t2.CurrentStage )

yeah???|||you might need to go to...

select t1.UserStage - t2.CurrentStage from
BUSLocation as t1
join UserLocation as t2 on whatever your join is
Where t1.UserStage - t2.CurrentStage =
(select min(t1.UserStage - t2.CurrentStage from
BUSLocation as t1
join UserLocation as t2 on whatever your join is )

not sure though....|||Do u think my code would work?

SELECT CurrentStage, PlateNbr from tblBusLocation a, tblUserLocation b
Where a.CurrentStage < b.UserStageNbr AND a.CurrentStage= (SELECT MIN(b.UserStageNbr-a.CurrentStage))|||Sorry here is an updated code. Is there a problem with the MIN function?

SELECT t1.CurrentStage, t1.PlateNbr

From tblBusLocation t1, tblUserLocation t2

Where t1.CurrentStage < t2.UserStageNbr AND

t1.CurrentStage =

(SELECT t1.CurrentStage from tblBusLocation t1, tblUserLocation t2

where MIN (t2.UserStageNbr - t1.CurrentStage)
)|||SELECT t1.CurrentStage, t1.PlateNbr
, MIN (t2.UserStageNbr - t1.CurrentStage)
From tblBusLocation t1
, tblUserLocation t2
Where t1.CurrentStage < t2.UserStageNbr
group
by t1.CurrentStage, t1.PlateNbr

rudy|||this might be way off but...

select top 1 (t2.UserStageNbr - t1.CurrentStage)
From tblBusLocation t1, tblUserLocation t2
Where t1.CurrentStage < t2.UserStageNbr
Order by (t2.UserStageNbr - t1.CurrentStage)

???