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 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 17:17pm
7prince409
Edited 19-Jan-13 19:13pm
v2

1 solution

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

Solution 1

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 >= 10 THEN 0.05
                           when orderItems.qty < 10 Then 0.02
                         END) AS 'Total Discount Value',
       
       OrderItems.qty * Inventory.price - (OrderItems.qty * Inventory.price * 
                          CASE 
                               WHEN OrderItems.qty >= 10 THEN 0.05
                               when OrderItems.qty < 10 THEN 0.02
                               else 0
                          END) AS 'Total_Final_Price_After_Discount'
  FROM orders, inventory, orderitems
 WHERE ORDERS.ORDERID = ORDERITEMS.ORDERID
   AND ORDERITEMS.PARTID = INVENTORY.PARTID<small></small>
 
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 680
1 Maciej Los 300
2 Suvendu Shekhar Giri 229
3 Sascha Lefévre 229
4 Richard Deeming 195
0 Sergey Alexandrovich Kryukov 9,678
1 OriginalGriff 9,135
2 Peter Leow 5,082
3 Kornfeld Eliyahu Peter 3,373
4 Maciej Los 2,606


Advertise | Privacy | Mobile
Web04 | 2.8.150327.1 | Last Updated 20 Jan 2013
Copyright © CodeProject, 1999-2015
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