New to this group and hope it is ok to reposting this from SQLServer to here...
Have the following rate schedule - amount and discount rate
$0-10 = 1% discount
$10-100 = 2%
$100-over = 3%
I created the following rate table with min range and no max range so last entry will handle everything over 100$
I then create a view to translate it as min and max range
$0 10 1%
$10 100 2%
$100 max 3%
Select* from view where $10 between minrange and maxrange
give 2 rows
I hope not to define max range as there are maintanance issues, and not to get into defining pennies. I could write SELECT FROM WHERE $10 >= minrange and $10 < maxrange, but can I use BETWEEN? As you can see BETWEEN returning 2 row is wrong. Question: Is there a way to structure the rate table so I can use SELECT BETWEEN and what is the most common way to setup a rate table. Thanks in advance for any ideas.