Click here to Skip to main content
13,147,182 members (28,344 online)
Rate this:
 
Please Sign up or sign in to vote.
How Can I Round the dollar about after the output of
OrderItems.qty * Inventory.price - (OrderItems.qty * Inventory.price *
CASE WHEN OrderItems.qty >=10 THEN 0.05 END)


Also, How can I combine the queries below without using UNIION ALL?
SELECT ORDERS.orderid, 
INVENTORY.partid, 
Inventory.description, 
ORDERITEMS.qty,
 Inventory.price,
 (OrderItems.qty * Inventory.price) AS 'Total Original Price',
--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 OrderItems.qty >=10 THEN 0.05 END) AS 'Total Discount Value' ,
 
 OrderItems.qty * Inventory.price - (OrderItems.qty * Inventory.price * 
CASE WHEN OrderItems.qty >=10 THEN 0.05 END)  AS 'Total_Final_Price_After_Discount' 
 
 FROM orders, inventory, orderitems
 WHERE ORDERS.ORDERID = ORDERITEMS.ORDERID  AND ORDERITEMS.PARTID = INVENTORY.PARTID
 AND ORDERITEMS.qty >=10  
 --ORDER BY ORDERS.orderid, INVENTORY.partid
 
 UNION ALL
 
 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 < 10 THEN 0.2  END) as 'Total Discounted Value',
 
  OrderItems.qty * Inventory.price - (OrderItems.qty * Inventory.price * 
CASE WHEN OrderItems.qty < 10 THEN 0.2 END)  AS 'Total_Final_Price_After_Discount'
 
 FROM orders, inventory, orderitems
 WHERE ORDERS.ORDERID = ORDERITEMS.ORDERID  AND ORDERITEMS.PARTID = INVENTORY.PARTID
 AND ORDERITEMS.qty BETWEEN 5  AND  9
 ORDER BY ORDERS.orderid, INVENTORY.partid
Posted 19-Jan-13 16:17pm
7prince414
Updated 19-Jan-13 18:13pm
v2

1 solution

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

Solution 1

<br />
<pre lang="SQL"><br />
SELECT ORDERS.orderid,<br />
       INVENTORY.partid,<br />
       Inventory.description,<br />
       ORDERITEMS.qty,<br />
       Inventory.price,<br />
       (OrderItems.qty * Inventory.price) AS 'Total Original Price',<br />
       (OrderItems.qty * Inventory.price * <br />
                         CASE<br />
                           WHEN OrderItems.qty >= 10 THEN 0.05<br />
                           when orderItems.qty < 10 Then 0.02<br />
                         END) AS 'Total Discount Value',<br />
       <br />
       OrderItems.qty * Inventory.price - (OrderItems.qty * Inventory.price * <br />
                          CASE <br />
                               WHEN OrderItems.qty >= 10 THEN 0.05<br />
                               when OrderItems.qty < 10 THEN 0.02<br />
                               else 0<br />
                          END) AS 'Total_Final_Price_After_Discount'<br />
  FROM orders, inventory, orderitems<br />
 WHERE ORDERS.ORDERID = ORDERITEMS.ORDERID<br />
   AND ORDERITEMS.PARTID = INVENTORY.PARTID<small></small><br />
<br />
</pre><br />
<br />
  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 |
Web02 | 2.8.170915.1 | Last Updated 20 Jan 2013
Copyright © CodeProject, 1999-2017
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