on 10-19-2010 11:03 AM
Hello,
please, could you give me an advice to change something use this query also for e.g 2009? This query shows only this year.
Thank you,
martina
SELECT T0.ITEMCODE, T0.ItemName,
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUL QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'AUG QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'SEP QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) 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, T0.ItemName, YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())
Hi Martina
Here is a query I use to get the same details. Its easy enough to tailor for specific years by changing the year on the inner subquery. Hope this is of use.
-- Main part of select to transpose the row level details (Months) into columns
SELECT ItemCode, ItemName, TranYear,
SUM(CASE WHEN TranMonth = 1 THEN TranQty ELSE 0 END) As Mth01Amt,
SUM(CASE WHEN TranMonth = 2 THEN TranQty ELSE 0 END) As Mth02Amt,
SUM(CASE WHEN TranMonth = 3 THEN TranQty ELSE 0 END) As Mth03Amt,
SUM(CASE WHEN TranMonth = 4 THEN TranQty ELSE 0 END) As Mth04Amt,
SUM(CASE WHEN TranMonth = 5 THEN TranQty ELSE 0 END) As Mth05Amt,
SUM(CASE WHEN TranMonth = 6 THEN TranQty ELSE 0 END) As Mth06Amt,
SUM(CASE WHEN TranMonth = 7 THEN TranQty ELSE 0 END) As Mth07Amt,
SUM(CASE WHEN TranMonth = 8 THEN TranQty ELSE 0 END) As Mth08Amt,
SUM(CASE WHEN TranMonth = 9 THEN TranQty ELSE 0 END) As Mth09Amt,
SUM(CASE WHEN TranMonth = 10 THEN TranQty ELSE 0 END) As Mth10Amt,
SUM(CASE WHEN TranMonth = 11 THEN TranQty ELSE 0 END) As Mth11Amt,
SUM(CASE WHEN TranMonth = 12 THEN TranQty ELSE 0 END) As Mth12Amt,
SUM(TranQty) As TotalAmt
-- Select from this subquery in order to combine invoice and credit note data
-- Note that aggregation is also done here to make it run a bit faster
FROM (
-- Invoices
SELECT
T3.ItemCode As 'ItemCode',
T3.ItemName As 'ItemName',
Year(T1.DocDate) AS 'TranYear',
Month(T1.DocDate) As 'TranMonth',
SUM(T2.Quantity) As 'TranQty'
FROM OINV AS T1
INNER JOIN INV1 AS T2 ON T1.DocEntry = T2.DocEntry
INNER JOIN OITM AS T3 ON T2.ItemCode = T3.ItemCode AND T3.SellItem = 'Y'
GROUP BY T3.ItemCode, T3.ItemName, Month(T1.DocDate), Year(T1.DocDate)
UNION ALL
-- Credit notes
SELECT
T3.ItemCode As 'ItemCode',
T3.ItemName As 'ItemName',
Year(T1.DocDate) AS 'TranYear',
Month(T1.DocDate) As 'TranMonth',
SUM(T2.Quantity) As 'TranQty'
FROM ORIN AS T1
INNER JOIN RIN1 AS T2 ON T1.DocEntry = T2.DocEntry
INNER JOIN OITM AS T3 ON T2.ItemCode = T3.ItemCode AND T3.SellItem = 'Y'
GROUP BY T3.ItemCode, T3.ItemName, Month(T1.DocDate), Year(T1.DocDate)
) InnerQry
-- Need to set an alias name 'InnerQry' just to make it work
-- Group and order by is for the main select
GROUP BY ItemCode, ItemName, TranYear
ORDER BY ItemCode, ItemName, TranYear
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear specialists,
both last answers will help me a lot!!! I will try to do the same for purchase.
Thank you,
Martina
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Try this:
SELECT P.[ItemCode],
[1] as [Jan],
[2] as [Feb],
[3] as [Mar],
[4] as [Apr],
[5] as [May],
[6] as [Jun],
[7] as [Jul],
[8] as [Aug],
[9] as [Sep],
[10] as [Oct],
[11] as [Nov],
[12] as [Dec]
FROM (SELECT T0.ITEMCODE, T0.QUANTITY,
MONTH(T0.docdate) as [month]
FROM dbo.inv1 T0
WHERE Year(T0.docdate)=2009 AND T0.ItemCode is not NULL ) S
PIVOT (SUM(Quantity) FOR [month] IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
ORDER BY P.[ItemCode]
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi ,
Use below query for 2009
SELECT T0.ITEMCODE, T0.ItemName,
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUL QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'AUG QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'SEP QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) 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, T0.ItemName, YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) <=(YEAR(GETDATE()))
or
SELECT T0.ITEMCODE, T0.ItemName,
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUL QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'AUG QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'SEP QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) 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, T0.ItemName, YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) =(YEAR(GETDATE())-1)
Thanks,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi..
go for this following query.
SELECT T0.ITEMCODE, T0.ItemName,
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) <= YEAR(GETDATE())) AS 'JAN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) <= YEAR(GETDATE())) AS 'FEB QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) <= YEAR(GETDATE())) AS 'MAR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) <= YEAR(GETDATE())) AS 'APR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) <= YEAR(GETDATE())) AS 'MAY QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) <= YEAR(GETDATE())) AS 'JUN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) <= YEAR(GETDATE())) AS 'JUL QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) <= YEAR(GETDATE())) AS 'AUG QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) <= YEAR(GETDATE())) AS 'SEP QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) <= YEAR(GETDATE())) AS 'OCT QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) <= YEAR(GETDATE())) AS 'NOV QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) <= YEAR(GETDATE())) 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, T0.ItemName, YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) <= YEAR(GETDATE())
Regards,
Bhavank
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.