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

Answer 2

If you want to add the new column to TableA and then update it with values from table B, this is the final UPDATE:

UPDATE TableA
SET Colour = B.Colour
FROM TableA A
INNER JOIN TableB B ON B.Month = A.Month AND B.RangeStart < A.Average AND (B.RangeEnd IS NULL OR B.RangeEnd > A.Average)

You should use LEFT OUTER JOIN and provide a default value if it is possible that you don't have data in table B for a given month and average in table A.

UPDATE TableA
SET Colour = ISNULL(B.Colour, N'SomeColour')
FROM TableA A
LEFT OUTER JOIN TableB B ON B.Month = A.Month AND B.RangeStart < A.Average AND (B.RangeEnd IS NULL OR B.RangeEnd > A.Average)

Besides, you should check whether your data in Table B depends on the year, as it seems to happen with table A.

In this case, you should add this field to the Join's On clause:

UPDATE TableA
SET Colour = B.Colour
FROM TableA A
INNER JOIN TableB B ON B.Year = A.Year AND B.Month = A.Month AND B.RangeStart < A.Average AND (B.RangeEnd IS NULL OR B.RangeEnd > A.Average)

Before you run the update, you can check that you are getting the desired values by running this query first:

SELECT Department, A.Month, Average, B.Colour
FROM @tableA A
INNER JOIN @tableB B ON B.Month = A.Month AND B.RangeStart < A.Average AND (B.RangeEnd IS NULL OR B.RangeEnd > A.Average)

Hope this helps :)

Answer 3

So really you want

select a.*,b.colour from a 
left join table b on a.month=b.month 
 and ((b.rangeend is null and a.average>b.rangestart) 
    or (a.average between b.rangestart and b.rangeend))

Im not promising it works as I didnt have time to enter some tables and data