Applying a dynamic between statement in SQL

Question

I have two SQL tables. Employees and SalaryBand. I'm using TSQL

Employees:

Name|ID|Salary
John|1 |1000
Jack|2 |5000
Mark|3 | 0

SalaryBand:

PayGrade|SalaryMin|SalaryMax
Low     |0        |999
Med     |1000     |4999
High    |5000     |

How do I achieve the following in SQL? I know how I would do it with a CASE WHEN BETWEEN these values e.g. Case when Salary between 0 and 1000 then 'Low' as Paygrade.

How do I join the table and "dynamically" apply this transformation such when the values in Salary Band change, so can my calculations.

Output:

Name|PayGrade|Salary
John|Med     |1000
Jack|High    |5000
Mark|Low     |0

Answer 1

The join would be most naturally written as:

select E.Name, S.PayGrade, E.Salary
from Employees E left join
     SalaryBand S
     on E.Salary >= S.SalaryMin and
        (E.Salary <= S.SalaryMax or S.SalaryMax is null) ;

I would suggest that you store the bands with the higher end equal to the next start:

PayGrade|SalaryMin|SalaryMax
Low     |0        |1000
Med     |1000     |5000
High    |5000     |

And then use:

select E.Name, S.PayGrade, E.Salary
from Employees E left join
     SalaryBand S
     on E.Salary >= S.SalaryMin and
        (E.Salary < S.SalaryMax or S.SalaryMax is null) ;

This makes the data much less prone to errors.