469,337 Members | 6,367 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,337 developers. It's quick & easy.

Changing decimal places in a query

Hello,

I've written a query that works well and that gives me a line by line discount percentage of each item. The part that is annoying me is that instead of giving me a whole number, like 25, it gives me 25.00. Is there a way to change this? I've attached the query so you have an idea of what I'm doing. Thanks for the help!

Expand|Select|Wrap|Line Numbers
  1. SELECT Products.[Store UPC] AS 'SKU', Products.UPC, SalesItems.Description, SalesItems.Price, SalesItems.MSRP, ROUND(100-(100*(SalesItems.Price/SalesItems.MSRP)),0) AS 'Discount' FROM SalesItems LEFT JOIN Products ON SalesItems.ProductID = Products.ID WHERE SalesID = (SELECT ID FROM Sales WHERE Barcode = <<Sale Barcode>>) AND SalesItems.Hide = 0 ORDER BY Discount, SalesItems.Description
Feb 29 '08 #1
4 1758
Delerna
1,134 Expert 1GB
Hi Bikefixxer
You can cinvert it to integer with the convert function
Expand|Select|Wrap|Line Numbers
  1. SELECT Products.[Store UPC] AS 'SKU', 
  2.     Products.UPC, 
  3.     SalesItems.Description, 
  4.     SalesItems.Price, 
  5.     SalesItems.MSRP, 
  6.  
  7.     convert(int,ROUND(100-(100*(SalesItems.Price/SalesItems.MSRP)),0) ) AS 'Discount' 
  8.  
  9. FROM SalesItems 
  10. LEFT JOIN Products ON SalesItems.ProductID = Products.ID 
  11. WHERE SalesID = (SELECT ID FROM Sales WHERE Barcode = <<Sale Barcode>>) AND SalesItems.Hide = 0 ORDER BY Discount, SalesItems.Description
  12.  
  13.  
Feb 29 '08 #2
Worked like a champ! Thank you very much!!

On a side note, I don't have any formal SQL training or anything. All that I've learned has been through online tutorials and trial and error. The tutorials that I've found don't mention the convert function at all. Any good websites anyone can suggest that would talk more about that particular aspect? Thanks again!
Feb 29 '08 #3
ck9663
2,878 Expert 2GB
You can always read the Books Online and the Help that comes with the installation.

or Try here

;)

By the way, in your sample, you're lucky that discounts are in whole numbers. But rounding into whole numbers is not a good practice when dealing with percentage. You still want that .something there even if it's .000

-- CK
Mar 1 '08 #4
Delerna
1,134 Expert 1GB
For books on line you can access that from the help menu on query analyser.
the help menu in enterprise manager brings up help for microsoft management consols.
At least it does on SQL2000
Mar 1 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by tencip | last post: by
reply views Thread by FOGHORN | last post: by
8 posts views Thread by nick | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.