# Thread: Setup Rate table for SELECT BETWEEN

1. Registered User
Join Date
Nov 2002
Posts
6

## Unanswered: Setup Rate table for SELECT BETWEEN

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\$
\$0 1%
\$10 2%
\$100 3%

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
1%
2%

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

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I'd structure it like:
PHP Code:
``` CREATE TABLE ranges    low_end MONEY NOT NULL ,  high_end MONEY NOT NULL ,  discount REAL NOT NULL    ) INSERT ranges (low_end, high_end, discount)    SELECT  \$0.00, \$10.00, .01    UNION ALL SELECT \$10.0001, \$100.00, .02    UNION ALL SELECT \$100.0001, \$922337203685477.5807, .03  ```
You could argue that the high_end values should be pennies, but in the case where you have fractional cent values, these values are safer.

-PatP

3. Registered User
Join Date
Nov 2002
Posts
6

## Re: Setup Rate table for SELECT BETWEEN

>> 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 [/SIZE][/QUOTE]

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. Thanks in advance for any ideas.

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
BETWEEN is inclusive (includes the end values). So \$10 would fall between both (0-10) and (10-100).

For this reason, I seldom use BETWEEN, prefering instead

where TestValue >=MinValue and TestValue < MaxValue

To handle NULLS in the MaxValue, try

where TestValue >=MinValue and TestValue < isnull(MaxValue, TestValue + 1)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•