SQL Join on Table A value within Table B range

Question

I have two tables that can be seen in accompanying image.

Table A contains Department, Month and Average.

Table B contains Month, Year, RangeStart, RangeEnd and Colour.

If you look at the screen shot of Table B, you will see for each Month you have a Green, Yellow, Orange and Red value. You also have a range.

What I need.........

I need a new column on Table A named 'Colour'. In this column, I need either Green, Yellow, Orange or Red. The deciding factor on which colour is assigned to the month will be the 'Average' column.

For example:

DepartmentA for May's Average is equal to 0.96 Upon referencing Table B, I can see that line 8, 0.75+ will be the range this fits into. Therefore Red is the colour I want placed in table A next to Mays average.

I have left RangeEnd for the highest range per month as NULL as it is basically 75+, anything greater than 0.75 slots in here.

Can anyone point me in the right direction that is not too time consuming.

Answer 1

you can use it straight forward:

select *
from table a
    join table b
        on a.month = b.month
           and a.average between b.rangestart and isnull(b.rangeend,10000) -- 100000 = greater than max value