Closest number match

Transact-SQL
1

Hi all,

I have two tables

Student

ID - Name - Mark
232 - Mike - 49
859 - Joanna  -77
849 - Clark - 83

Band

ID - Range - Score
1 - 50 - D
2 - 60 - C
3 - 70 - B
4 - 80 - A

I want the following results:

Mike - Fail
Joanna - B
Clarke - A

Ive tried the IN statement in where clause but if the Mark is not an exact match to Score it returns nothing. I want it to return within the band score range, like above, and any score < 50 = FAIL.

Many thanks

2 
-- *** Test Data ***
-- Please provide in future
CREATE TABLE #Students
(
	ID int NOT NULL
	,Name varchar(20) NOT NULL
	,Mark tinyint NOT NULL
);
INSERT INTO #Students
VALUES(232, 'Mike', 49)
	,(859, 'Joanna', 77)
	,(849, 'Clark', 83);

CREATE TABLE #Bands
(
	ID tinyint NOT NULL
	,[Range] tinyint NOT NULL
	,Score char(1) NOT NULL
);
INSERT INTO #Bands
VALUES (1, 50, 'D')
	,(2, 60, 'C')
	,(3, 70, 'B')
	,(4, 80, 'A');
-- *** End Test Data ***

WITH Ranges
AS
(
SELECT B1.[Range] AS Lo
	,COALESCE(B2.[Range], 101) As Hi
	,B1.Score
FROM #Bands B1
	LEFT JOIN #Bands B2
		ON B1.ID = B2.ID - 1
)
SELECT S.Name, COALESCE(R.Score, 'Fail') AS Grade
FROM #Students S
	LEFT JOIN Ranges R
		ON S.Mark >= R.Lo
			AND S.Mark < R.Hi
ORDER BY Grade DESC;
3

Exactly what I wanted @Ifor.
Much appreciated.
Sorry about the test data, the tables have a lot of other sensitive data, but I will provide test data next time.
Thanks

4 
SELECT s.*, COALESCE(oa1.Score, 'Fail') AS Score
FROM #Students s
OUTER APPLY (
    SELECT TOP (1) Score
    FROM #Bands b
    WHERE
        s.Mark >= b.Range 
    ORDER BY b.Range DESC
) AS oa1
5

If performance becomes an issue, you can resolve it easily by creating an additional column in bands that define the upper and lower limits (e.g. (70, 79, 'B'). A small change to the data could yield big savings in processing.

6

Performance did become an issue @stephen_hendricks, thanks for the tip.