Finding values in one table between those in another 1

[xplo5iv]

Hi
(I'm not quite sure what you call this, so I've had trouble searching)

I have two tables
Salaries
NINO
Salary

ScalePoints
SCP
AnnualPay

I need a query which will tell me the nearest SCP for each NINO.
(Salary is actual amount earned, SCP is point on the pay scale, e.g. if SCP 5 is £10k and SCP 6 is £12k, then if a person earns £10.5k the query should return a 5 against their NINO)

Can anyone point me in the right direction?

Thanks for your help.

Jonathan

 

[Golom]

This seems to work for your specific test case

Select NINO, Salary

     , (Select TOP 1 P.SCP
        From ScalePoints P
        GROUP BY SCP
        ORDER BY MIN(Abs(P.AnnualPay - S.Salary))) As SCP

From Salaries S

 

[xplo5iv]

Thanks Golom, really useful