to calculate a discount?

Question

There are 3 tables - Discounts(Discount) Customers(Clients) and Orders(Orders)

Discount

------------------------------------------------
|id_discount | count_orders | percent_discount |
------------------------------------------------
|          1 |            5 |              1,5 |
|          2 |           10 |              2,5 |

Clients

------------------------------------------------
|  id_client |    Name      |      Surname     |
------------------------------------------------
|          1 | Ivan         | Petrov           |
|          2 | Vasya        | Vasev            |

Orders

------------------------------------------------
| id         |  order_sum   | id_client        |
------------------------------------------------

The question how to calculate the total amount, given the number of orders made by the client? If the total number of orders 5 and above, the discount is 1.5%, if 10 and above, 2.5%. Otherwise, no discount. Thanks in advance

Your client table has no relation with discount table. Add it. Then ....very easy you can calculate sum of orders or count of orders for this client ( for example in stored procedure) and update table client. You can execute this procedure in trigger Orders After update. Do you need an example? — Val Marinov, Jun 10 '16 at 08:57
In the same way you can calculate discount without realation `clients->discount`. Just call the stored procedure when needed. — Val Marinov, Jun 10 '16 at 09:05
@ValMarinov There doesn't need to be a reference to discount. As is clear from the table definition and problem description, the discount depends on the number of items in your order. — Mark Rotteveel, Jun 10 '16 at 09:11
You need to provide more context to your question: how, when and where do you want to apply a discount? — Mark Rotteveel, Jun 10 '16 at 09:13
@Mark Тhen what is needed the table Discount? I understand there are set discounts as constants based on the number of orders. — Val Marinov, Jun 10 '16 at 09:44
@ValMarinov As far as I can tell, the table contains the configuration of the discounts. — Mark Rotteveel, Jun 10 '16 at 11:42

Tobb · Answer 1 · 2016-06-10T10:02:51.613

For this to work properly, you need a discount for 0 orders as well.

insert into Discount (count_orders, percent_discount) values (0, 0);

To find the percent discount for a customer with a given id, something like this:

select d.percent_discount
from Discount d
where d.count_orders = isnull(
       (select max(count_orders) 
       from Discount dI where count_orders <= (
           select count(1) from Orders oI where oI.id_client = 1)), 0)

Gets the discount for each client:

select c.id_client as client_id,
       (select d.percent_discount
       from Discount d
       where d.count_orders = isnull(
          (select max(count_orders) 
          from Discount dI where count_orders <= (
              select count(1) from Orders oI where oI.id_client = c.id_client)), 0)) as discount
from Clients c

Combined with a select to find the price with discount for each order:

select o.id,
c.name,
c.surname,
o.order_sum,
(o.order_sum - (o.order_sum * client_discount.discount / 100)) as sumWithDiscount
from Orders o join Clients c on o.id_client = c.id_client
    join (select cI.id_client,
                 (select d.percent_discount
                 from Discount d
                 where d.count_orders = isnull(
                     (select max(count_orders) 
                     from Discount dI where count_orders <= (
                           select count(1) from Orders oI where oI.id_client = cI.id_client)), 0)) as discount
          from Clients cI) as client_discount on client_discount.id_client = c.id_client

score -1 · Answer 2 · answered Jun 10 '16 at 09:32

You can use PROCEDURE and CURSOR to solve this:

DELIMITER $$
CREATE PROCEDURE `getDiscount`(IN _orderSum INT(11))
  BEGIN
    DECLARE countOrders INT(11) DEFAULT 0;
    DECLARE percentDiscount DOUBLE DEFAULT 0;
    DECLARE done INT(1);
    DECLARE cur CURSOR FOR SELECT count_orders, percent_discount FROM Discount ORDER BY count_orders DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    SET done = 0;
    OPEN cur;
    discountLoop: LOOP
      FETCH cur INTO countOrders, percentDiscount;
      IF done = 1 THEN LEAVE discountLoop; END IF;
      IF _orderSum >= countOrders THEN
          SELECT percentDiscount AS percent_discount FROM dual;
          SET done = 1;
      END IF;
    END LOOP discountLoop;
    CLOSE cur;
  END$$
DELIMITER ;

You are using MySQL syntax to answer a Firebird/Interbase question. — Mark Rotteveel, Jun 10 '16 at 11:44

to calculate a discount?

2 Answers2