Hello everyone. There are 3 tables – Discounts, Clients and Orders
id_discount count_orders percent_discount 1 5 1,5 2 10 2,5
id_client Name Surname 1 Ivan Petrov 2 Vasya Vasev
id order_sum id_client
The question is, how to calculate the total amount, given the number of orders that the client has made? If the total number of orders is 5 or more, then there is a 1.5% discount, if 10 or more, then 2.5%. Otherwise, there is no discount. Thanks in advance
Firebird is not at hand, so I write a query that is as close to standards as possible, it will work with a high probability. You did not indicate to which specific amount you want to apply the discount, so you thought that you should issue all orders, calculating the amount with a discount for each of them, with the assumption that at the moment the amount in orders is without a discount.
select O.id,O.id_client, O.order_sum-(O.order_sum/100*coalesce(D.percent_discount,0)) from Orders O left join (select id_client,max(D.count_orders) as d_cnt from (select id_client,count(1) as cnt from Orders group by id_client) as C, Discount as D where D.count_orders<=cnt group by id_client) as S on S.id_client=O.id_client left join Discount as D on D.count_orders=S.d_cnt
- Subquery C selects the current order quantities by customer
- All discounts with a smaller or equal amount are glued to them.
- Subquery S. The quantity for the discount is taken for the most suitable number of orders
- On the left (since there may not be any discounts), discounts are added, taking into account the required amount
Glue the table clients as desired.