Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002

    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

    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. #2
    Join Date
    Sep 2002

    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. #3
    Join Date
    Feb 2004
    In front of the computer
    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 (32)    NOT NULL

    INSERT INTO tDiscount (amountdiscount)
    SELECT         00.01
       UNION SELECT  10
       UNION SELECT 100

    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

    FROM tSample AS s
       JOIN tDiscount 
    AS d
    (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.


Posting Permissions

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