rounding

[tonyvee1973]

Hiya

I have a script below and need to get the STK_PHYSICAL rounded (Which i highlighted the line beloow). I believe the round statement i need is ROUND(COLUMN,0) but not sure where to implement below - any ideas?
Thanks as always

SELECT dbo.STK_STOCK.STKCODE, '' AS Spare, dbo.STK_STOCK.STKNAME, '' AS UnitOfMeasure, '' AS UnitOfSales, dbo.STK_STOCK.STK_BASEPRICE,
dbo.STK_STOCK3.STK_USRNUM1 AS RRP, '' AS PriceCode,
[highlight #FCE94F]dbo.STK_STOCK.STK_PHYSICAL - dbo.STK_STOCK.STK_RESERVE_OUT - dbo.STK_STOCK.STK_BACK_ORDER_QTY AS STK_PHYSICAL,[/highlight] dbo.STK_STOCK.STK_ORDER_IN, '' AS NextDElivery, '' AS ColourCode, '' AS ProductGroup, '' AS Discontinued,
(CASE dbo.STK_STOCK3.STK_USRFLAG1 WHEN 1 then 'Y' ELSE 'N' END) as Dead,
dbo.STK_STOCK.STK_BARCODE AS BarCode, '' AS MinimumMultiples, '' AS GenericCode5,
'' AS GenericCode6, dbo.STK_STOCK.STK_SORT_KEY, dbo.STK_STOCK.STK_SORT_KEY1, '' AS FreeType3, '' AS FreeType4, '' AS FreeType5, '' AS FreeType6, '' AS FreeType7,
'' AS FreeType8, '' AS BreakCode, dbo.STK_STOCK.STK_COSTPRICE / dbo.STK_STOCK_2.STK_BUY_UNIT1 AS STK_COSTPRICE, '' AS MinimumPrice,
'' AS RangeCode, '' AS MinimumPromotional, '' AS TaxCode, '' AS MinimumOrderQuantity, '' AS FutureStock
FROM dbo.STK_STOCK INNER JOIN
dbo.STK_STOCK_2 ON dbo.STK_STOCK.STKCODE = dbo.STK_STOCK_2.STKCODE2 INNER JOIN
dbo.STK_STOCK3 ON dbo.STK_STOCK_2.STKCODE2 = dbo.STK_STOCK3.STKCODE3
WHERE (dbo.STK_STOCK3.STK_USRFLAG1 <> 1)

 

[kray4660]

I have not used Round in a SQL statement, but normally you would round as your last operation. It lessens rounding propagation, thus I suggest
ROUND(dbo.STK_STOCK.STK_PHYSICAL - dbo.STK_STOCK.STK_RESERVE_OUT - dbo.STK_STOCK.STK_BACK_ORDER_QTY AS STK_PHYSICAL,0)

 

[djj55]

kray4660 almost has it but I would suggest:

ROUND(dbo.STK_STOCK.STK_PHYSICAL - dbo.STK_STOCK.STK_RESERVE_OUT - dbo.STK_STOCK.STK_BACK_ORDER_QTY, 0[COLOR=#EF2929])[/color] AS STK_PHYSICAL,

Notice the parenthesis is before the AS.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[kray4660]

Opps! I am bad. Copy and paste without really looking at the line.

 

[tonyvee1973]

Hi
tried this and it seems to round too much? for example, on one of the rows there is a result for physical of 9.3677777 - after applying the line above it rounds to 0
Any ideas and thanks again

 

[tonyvee1973]

Ok, rechecked and all is now ok - Thanks for all your help guys!