CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Suraj Pangam
Suraj Pangam

Reputation: 23

calculating shipping rates based on additional weight

I am currently designing database for making invoices for shipping company. The point where i stuck is how to calculate rates which exceeds wight limit.

I have one table called tblRates columns {id,from_weight,to_wight,price} rows would be like

[{1,0,499,20$},
 {2,500,1999,40$}, //max weight 2Kg
 {3,2000,'unlimited','$20'}] //this row holds pricing for every add 500gm

as you can see i can easily get rates upto 2kg just by using between query. But when weight exceeds 2kg i want to break it into slice of 500gm and charge 20 bucks for each slice. e.g. if weight is 3300, price will be $40 + $(3 * 20). dont worry about location based pricing. its just for simplicity to understand my question i moved price into this table. its just how to determine the slices when given weight exceeds limit.

//wip sp

CREATE Procedure Sic.getRates
@Weight Decimal(7,3),
AS
 BEGIN
    SELECT Price 
      FROM tblRates
     WHERE @weight BETWEEN from_rates  AND to_rates;
 END

Upvotes: 2

Views: 463

Answers (1)

Leo.W
Leo.W

Reputation: 569

http://sqlfiddle.com/#!6/127b8/31

Create Table tblRates(
  id int,
  from_weight int,
  to_wight int,
  base_price int,
  extra_price_per_500_g int
);

Insert Into tblRates (id,from_weight,to_wight,base_price,extra_price_per_500_g)
Values
(1,0,499,20,0),
(2,500,1999,40,0),
(3,2000,2147483647,40,20);

Declare @Weight int = 3300

Select 
  base_price + extra_price_per_500_g * CEILING((@Weight - from_weight)/500.0) price
From
  tblRates
Where 
  @Weight BETWEEN from_weight AND to_wight;

I redesigned your table, and changed @Weight Decimal(7,3) to @Weight int (or, you will have a problem with using between; try @Weight = 1999.06 to see the mistake).

Upvotes: 2

Related Questions