Calculating Discount Percentage

admin February 13, 2010 Uncategorized Comments Off

Question

I sell widgets, I have a table of orders which includes the number of widgets for that order.

I would like to generate a discount percentage for each order based on the rule :

1 – If the order is for 10 or less widgets the discount is 10%
2 – For every 5 additional wigets I want the discount to go up by a percent.

So an order of 10 widgets gets a 10% discount, an order of 15 widgets gets an 11% discount, an order of 20 widgets gets
a discount of 12% etc…

Answer :

Well suppose your excel table looks like this :

Order_ID Widget_AMT
86773 6
86332 13
86222 18
86002 24
86324 29

You could run the following SQL to provide the required discounts :

SELECT
ORDER_ID,
WIDGET_AMT,
CASE
WHEN (WIDGET_AMT<10) THEN 0
WHEN (WIDGET_AMT>=10) THEN (floor((WIDGET_AMT / 5) – 2) + 10)
END AS DISCOUNT
FROM A
SELECT
  ORDER_ID,
  WIDGET_AMT,
  CASE
    WHEN (WIDGET_AMT<10) THEN 0
    WHEN (WIDGET_AMT>=10) THEN (floor((WIDGET_AMT / 5) – 2) + 10)
  END AS DISCOUNT
FROM A

This SQL run in QueryCell will provide the table :

Order_ID Widget_AMT DISCOUNT
86773 6 0
86332 13 10
86222 18 11
86002 24 12
86324 29 13

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.