Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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
7prince409
Edited 19-Jan-13 11:11am
v2
Comments
7prince at 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
0 DamithSL 375
1 Sergey Alexandrovich Kryukov 217
2 OriginalGriff 210
3 Peter Leow 135
4 Uma Shankar Patel 80
0 OriginalGriff 7,380
1 DamithSL 5,359
2 Sergey Alexandrovich Kryukov 4,944
3 Maciej Los 4,906
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 19 Jan 2013
Copyright © CodeProject, 1999-2014
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