Changing decimal places in a query

[bikefixxer]

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!

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

[Delerna]

Hi Bikefixxer
You can cinvert it to integer with the convert function

SELECT Products.[Store UPC] AS 'SKU', 
    Products.UPC, 
    SalesItems.Description, 
    SalesItems.Price, 
    SalesItems.MSRP, 

    [B]convert(int,[/B]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

[bikefixxer]

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!

[ck9663]

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

[Delerna]

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