• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

SQL question - showing 'Total Discount Value' as percenteges in the column.

My quey works fine.  I just need to show 'Total Discount Value' as percenteges in the 'Total Discount Value'  column  instead value.  Like 5% for the items >=10  and  2% for the items.   If this is not clear, I attached a file for an example.

SELECT ORDERS.orderid,
       INVENTORY.partid,
       Inventory.description,
       ORDERITEMS.qty,
       Inventory.price AS 'Unit 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 'Final Cost'
  FROM orders, inventory, orderitems
 WHERE ORDERS.ORDERID = ORDERITEMS.ORDERID
 AND ORDERITEMS.qty >=5
   AND ORDERITEMS.PARTID = INVENTORY.PARTID
example.doc
0
ocdc
Asked:
ocdc
  • 2
1 Solution
 
gplanaCommented:
I'm not sure, but try this: I added a column which maybe is what you want.

SELECT ORDERS.orderid,
       INVENTORY.partid,
       Inventory.description,
       ORDERITEMS.qty,
       Inventory.price AS 'Unit 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',
       CASE
                           WHEN OrderItems.qty >= 10 THEN 0.05
                           when orderItems.qty < 10 Then 0.02
                         END) AS 'Total Discount Percentage',
       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 'Final Cost'
  FROM orders, inventory, orderitems
 WHERE ORDERS.ORDERID = ORDERITEMS.ORDERID
 AND ORDERITEMS.qty >=5
   AND ORDERITEMS.PARTID = INVENTORY.PARTID 

Open in new window

0
 
RinilCommented:
try using  this after the number + CHAR(37) that is ascii of % character
0
 
ocdcAuthor Commented:
I am trying to  modify this code:  
CASE                  WHEN OrderItems.qty >= 10 THEN 0.05
                           when orderItems.qty < 10 Then 0.02
                         END) AS 'Total Discount Value',

to include:

STR((CASE  WHEN Orderitems.qty BETWEEN 5 AND  9 THEN '2%'
                    WHEN  Orderitems.qty >= 10  THEN '5%'  
                    END) AS CHAR(5))   AS  'Total Discount Value'   but this one give an error.

so that  'Total Discount Value' would look like the one in the attached file.    Also, need to format Final Cost column so that it will also look like the one in the attached file as well.  

 Thank you.
example2.doc
0
 
ocdcAuthor Commented:
thanks.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now