Skip to Content
0
Former Member
Feb 18, 2009 at 09:11 AM

Advance Query for Sale Analysis

25 Views

Hi experts!

I found a qury in forum :

SELECT T0.ITEMCODE, 
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = 
T0.ITEMCODE) AS 'JAN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE = 
T0.ITEMCODE) AS 'FEB QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE = 
T0.ITEMCODE) AS 'MAR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE = 
T0.ITEMCODE) AS 'APR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE = 
T0.ITEMCODE) AS 'MAY QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = 
T0.ITEMCODE) AS 'JUN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE = 
T0.ITEMCODE) AS 'JUL QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE = 
T0.ITEMCODE) AS 'AUG QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE = 
T0.ITEMCODE) AS 'SEP QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE = 
T0.ITEMCODE) AS 'OCT QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE = 
T0.ITEMCODE) AS 'NOV QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) 
WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE = 
T0.ITEMCODE) AS 'DEC QTY'
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

In this query, it is only Itemcode quatity of Itemcode by the month.

How can i have additional information about : Item decription, Customer

Thanks!