Click here to Skip to main content
12,298,379 members (44,082 online)

Sql query question - give discounts to order items using case

7prince asked:

Open original thread
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',
 
--If an order contains five or more units of a given product,  give a 2% discount for that line item. 
 (OrderItems.qty * Inventory.price * 
 CASE WHEN OrderItems.qty >= 5 THEN 0.05 ELSE 0.1 END) as 'Total Discounted Value',
 
--If an order contains ten or more units of a given product,   give a 5% discount on that line item.
(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
Tags: SQL-Server (SQL-server-2005)

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).



Advertise | Privacy | Mobile
Web02 | 2.8.160525.2 | Last Updated 26 Mar 2009
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100