Whole Numbers in SQL Queiries

[Former Member]

I have produced the query below so that it will alert our purchaser when he needs to chase up undelivered goods inwards. I want the qty only to display as a whole number rather than having 3 decimal places after it, i am sure there is way of doing this just not sure how to

SELECT T0.[DocNum] AS Doc, T0.[CardName]AS Supplier, T1.[LineNum] AS Row, T1.[ItemCode] AS Code, T1.[Dscription] As Desciption, T1.[Quantity] AS Qty, T1.[ShipDate] AS [Del. Date], T1.[OpenQty] AS [Qty to Deliver] FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[ShipDate] > GETDATE() -7 AND T1.[ShipDate] < GETDATE() +7 AND T0.[DocStatus] = 'O' AND T1.[OpenQty] > 0 AND T1.[LineStatus] = 'o'

[Former Member · Accepted solution]

Hi David ,

Try:

SELECT T0.DocNum AS Doc, T0.CardName AS Supplier, T1.LineNum AS Row, T1.ItemCode AS Code,

T1.Dscription As Desciption, cast(T1.Quantity as integer) AS Qty, T1.ShipDate AS 'Del. Date', cast(T1.OpenQty as integer) AS 'Qty to Deliver'

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T1.ShipDate > GETDATE() -7 AND T1.ShipDate < GETDATE() +7 AND T0.DocStatus = 'O'

AND T1.OpenQty > 0 AND T1.LineStatus = 'o'

Thanks,

Gordon

[Former Member]

I just want a whole number no decimals is that possible

[Former Member]

Hi David ,

The first query which i have posted is the same , which you want .

Thanks

--

Ashish

[Former Member]

It only gets rid of one decimal! Nearly there!

[Former Member]

Hi ,

if need 2 decimal place , try this

SELECT T0.DocNum AS Doc, T0.CardName AS Supplier, T1.LineNum AS Row, T1.ItemCode AS Code,

T1.Dscription As Desciption, cast(Round(T1.Quantity,1)as decimal(30,2)) AS Qty, T1.ShipDate AS 'Del. Date'

,cast(Round(T1.OpenQty,1)as decimal(30,2))AS 'Qty to Deliver'

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T1.ShipDate > GETDATE() -7 AND T1.ShipDate < GETDATE() +7 AND T0.DocStatus = 'O'

AND T1.OpenQty > 0 AND T1.LineStatus = 'o'

regards

--

Ashish

Edited by: ASHISH RANJAN on Mar 1, 2011 5:56 PM

[Former Member]

Hi David ,

Try this :

SELECT T0.DocNum AS Doc, T0.CardName AS Supplier, T1.LineNum AS Row, T1.ItemCode AS Code,

T1.Dscription As Desciption, cast(Round(T1.Quantity,1)as decimal(30,0)) AS Qty, T1.ShipDate AS 'Del. Date'

,cast(Round(T1.OpenQty,1)as decimal(30,0))AS 'Qty to Deliver'

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T1.ShipDate > GETDATE() -7 AND T1.ShipDate < GETDATE() +7 AND T0.DocStatus = 'O'

AND T1.OpenQty > 0 AND T1.LineStatus = 'o'

Thanks

--

Ashish