How Can I modify the query below to
give 2% discount for that line item If an order contains five or more units of a given product. AND
give 5% discount for that line item If an order contains 10 or more units of a given product.
For some reason it only gives me an output for the the items that 10 or more. I need to show both discounts in the output. Thank you.
SELECT ORDERS.orderid,
INVENTORY.partid,
Inventory.description,
ORDERITEMS.qty,
Inventory.price,
(OrderItems.qty * Inventory.price) as 'Total Original Price',
(OrderItems.qty * Inventory.price *
CASE WHEN OrderItems.qty >= 5 THEN 0.05 ELSE 0.1 END) as 'Total Discounted Value',
(OrderItems.qty * Inventory.price *
CASE WHEN qty >=10 THEN 0.95/0.9 END) as 'Total Final Price of Each Product After Discount'
FROM orders, inventory, orderitems
WHERE ORDERS.ORDERID = ORDERITEMS.ORDERID AND ORDERITEMS.PARTID = INVENTORY.PARTID
AND ORDERITEMS.qty >=5 AND ORDERITEMS.qty >=10
ORDER BY ORDERS.orderid, INVENTORY.partid