# Thread: Setup Rate table design question

1. Registered User
Join Date
Nov 2002
Posts
6

## Setup Rate table design question

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\$
\$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%

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.

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171

## Re: Setup Rate table design question

"x BETWEEN y AND z" is nothing more than a convenient shorthand for "x >= y AND x <= z". You would have to shave a fraction of a penny off the "z" value like this:

Select * from view where \$10 between minrange and maxrange-0.001

...or, perhaps better, change the view definition so that it deducts 0.001 from the maxrange value.

3. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Granted that this code is perverse (I cooked it up on my lunch hour, in a bit of a rush), but I think it does exactly what you've requested. Please try it out and see if you agree.
PHP Code:
``` CREATE TABLE tDiscount (    amount        MONEY        NOT NULL ,  discount        DECIMAL (3, 2)    NOT NULL    ) INSERT INTO tDiscount (amount, discount)    SELECT         0, 0.01    UNION SELECT  10, 0.02    UNION SELECT 100, 0.03 CREATE TABLE tSample (    amount        MONEY        NOT NULL    ) INSERT INTO tSample (amount)    SELECT          1    UNION SELECT    5    UNION SELECT   10    UNION SELECT   50    UNION SELECT  100    UNION SELECT  500    UNION SELECT 1000 SELECT *    FROM tSample AS s    JOIN tDiscount AS d       ON (d.amount <= s.amount)    WHERE  d.amount = (SELECT Max(d1.amount)       FROM tDiscount AS d1       WHERE  d1.amount <= s.amount) DROP TABLE tDiscount DROP TABLE tSample  ```
Note that this is ugly code from a data modeling perspective, but I still think it will acheive your stated goals with tolerable efficiency.

-PatP

#### Posting Permissions

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