Setup Rate table design question

tester · #1 (**permalink**) 03-26-04, 15:31

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.

andrewst · #2 (**permalink**) 03-26-04, 15:50

"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.

Pat Phelan · #3 (**permalink**) 03-26-04, 17:21

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

Internet Services

Web Development

Digital Marketing

Consumer Tech