SQL join in a table. Value in the range of table B

Question

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

Table A contains Department , Month, and Average .

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

If you look at the screenshot of Table B, you will see that for each month you have the values ​​"Green", "Yellow", "Orange" and "Red". You also have a range.

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

I need a new column in table A named Color. In this column, I need either green, yellow, orange, or red. The deciding factor for which the color is assigned to the month is the Medium column.

For example:

DepartmentA for May. The average value is 0.96 After referring to table B, I see that line 8, 0.75+ will be the range into which it fits. Therefore, the red color is the color that I want to place in table A, next to the average value of Mays.

I left the RangeEnd for the maximum range per month as NULL, since it is basically 75+ , something more than 0.75 here.

Can someone point me in the right direction, which is not too time consuming.

Answer 1

you can use it directly:

 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

So what you really 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)) 

I do not promise that it works, as I did not have time to enter some tables and data

Answer 3

If you want to add a new column to TableA and then update it with the values ​​from table B, this will be 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 the LEFT OUTER JOIN and provide a default value if it is possible that you do not have data in table B for this month and the average value 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) 

In addition, you should check whether your data in table B depends on the year, as happens with table A.

In this case, you should add this field to the Join 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 starting the update, you can verify that you get the desired values ​​by first executing this request:

 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 :)