Click here to Skip to main content
12,635,428 members (24,817 online)
Rate this:
 
Please Sign up or sign in to vote.
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
Posted 19-Jan-13 11:04am
7prince414
Updated 19-Jan-13 11:11am
v2
Comments
7prince 19-Jan-13 18:30pm
   
I adjusted it as follows: and got qty >=10 and qty >=5 orders included but this time qty >=5 orders column for 'Total Final Price of Each Product After Discount' shows NULL. What is it that I am doing wrong?

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
ORDER BY ORDERS.orderid, INVENTORY.partid

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

1. Look on your where clause

AND ORDERITEMS.qty >=5 AND ORDERITEMS.qty >=10


This equals to just ORDERITEMS.qty >=10

2. Look on your case

CASE WHEN OrderItems.qty >= 5

you need to limit interval from right, if you want case qty>=10 to get into action
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161208.2 | Last Updated 19 Jan 2013
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