Skip to Content
0
Former Member
Aug 29, 2008 at 06:20 PM

Modifying Query Slightly

11 Views

Hi All --

Is it possible to modify the Query below so that the Amounts are rounded to the nearest Whole number -- no decimal places? Thanks!!

SELECT T0.ITEMCODE,

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'JAN Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'FEB Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'MAR Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'APR Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'MAY Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'JUN Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'JUL Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'AUG Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'SEP Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'OCT Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'NOV Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE

= T0.ITEMCODE) AS 'DEC Amt'

FROM dbo.OITM T0

LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode

WHERE T0.SellItem = 'Y'

GROUP BY T0.ItemCode,YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())

ORDER BY T0. ITEMCODE