Alex Rivera | Logout

SQL Join on Table A value within Table B range

Asked 2012-09-26T14:34:40.860
19

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.

enter image description here

sql sql-server sql-server-2008 t-sql
Edit
Report

1 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

answered 2012-09-26T14:39:48.160

Your Answer