on 11-24-2016 2:31 PM
Hi
This query i got off the SCN site.
it works etc and is what i need.
Problem seems to be that line items are duplicated and based on the attached screenshot it seems to be on items where changes has been made to the description field. I would assume it is the the table that store the history? is there a way to only populate the last or latest description instead of duplicating the item? i have this same problem with other reports written and would appreciate a solution to prevent this duplication.
Regards
Hi Ebie,
A new query:
SELECT
DISTINCT p.ItemCode,
p.ItemName,
[1] as 'Jan 16',
[2] as 'Feb 16',
[3] as 'Mar 16',
[4] as 'Apr 16',
[5] as 'May 16',
[6] as 'Jun 16',
[7] as 'Jul 16',
[8] as 'Aug 16',
[9] as 'Sep 16',
[10] as 'Oct 16',
[11] as 'Nov 16',
[12] as 'Dec 16'
FROM
(SELECT
DISTINCT T0.ItemCode,
T2.ItemName,
T0.Quantity AS Qty,
MONTH(T0.DocDate) AS month
FROM
INV1 T0
INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode
WHERE
YEAR(T1.DocDate) = 2016
AND T0.ItemCode >= '%vat%'
AND T0.ItemCode NOT LIKE 'disc%%'
AND T0.ItemCode NOT LIKE 'K______%%'
AND T0.ItemCode NOT LIKE 'x70____%%'
AND T0.ItemCode NOT LIKE 'K_____%%'
AND T0.ItemCode NOT LIKE 'A7_____%%'
AND T0.ItemCode NOT LIKE 'A8_____%%'
AND T0.ItemCode NOT LIKE 'A3______%%'
UNION
SELECT
DISTINCT T0.ItemCode,
T2.ItemName,
T0.Quantity as Qty,
MONTH(T0.DocDate) as month
FROM
RIN1 T0
INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode
WHERE
YEAR(T1.DocDate) = 2016
AND T0.ItemCode >= '%vat%'
AND T0.ItemCode NOT LIKE 'disc%%'
AND T0.ItemCode NOT LIKE 'K______%%'
AND T0.ItemCode NOT LIKE 'x70____%%'
AND T0.ItemCode NOT LIKE 'K_____%%'
AND T0.ItemCode NOT LIKE 'A7_____%%'
AND T0.ItemCode NOT LIKE 'A8_____%%'
AND T0.ItemCode NOT LIKE 'A3______%%'
) S PIVOT (SUM(Qty) FOR month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P ORDER BY p.itemcode
But I believe your query brings wrong results, because your distinct instruction in your subquery, avoid a lot of results.
Hope it helps.
Kind Regards,
Diego Lother
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
here is the query
SELECT distinct p.itemcode, p.dscription, [1] as 'Jan 16', [2] as 'Feb 16', [3] as 'Mar 16', [4] as 'Apr 16', [5] as 'May 16', [6] as 'Jun 16', [7] as 'Jul 16', [8] as 'Aug 16', [9] as 'Sep 16', [10] as 'Oct 16', [11] as 'Nov 16', [12] as 'Dec 16' FROM (SELECT distinct T0.ItemCode, T0.Dscription, T0.Quantity as Qty, MONTH(T0.DocDate) as month FROM inv1 T0 Inner Join OINV T1 ON T0.DocEntry = T1.DocEntry WHERE Year(T1.DocDate) = 2016 and T0.ItemCode >= '%vat%' and T0.ItemCode not like 'disc%%' and T0.ItemCode not like 'K______%%' and T0.ItemCode not like 'x70____%%' and T0.ItemCode not like 'K_____%%' and T0.ItemCode not like 'A7_____%%' and T0.ItemCode not like 'A8_____%%' and T0.ItemCode not like 'A3______%%' UNION SELECT distinct T0.ItemCode, T0.Dscription, T0.Quantity as Qty, MONTH(T0.DocDate) as month FROM RIN1 T0 Inner Join ORIN T1 ON T0.DocEntry = T1.DocEntry WHERE Year(T1.DocDate) = 2016 and T0.ItemCode >= '%vat%' and T0.ItemCode not like 'disc%%' and T0.ItemCode not like 'K______%%' and T0.ItemCode not like 'x70____%%' and T0.ItemCode not like 'K_____%%' and T0.ItemCode not like 'A7_____%%' and T0.ItemCode not like 'A8_____%%' and T0.ItemCode not like 'A3______%%' ) S PIVOT (SUM(Qty) FOR month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P order by p.itemcode
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
add in the select max(t0.itemname) and remove it from de group by
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ebie,
Could you share your query?
About your scenario, one solution is to join OITM table with its document line table and show the value from OITM.ItemName instead dscription.
Hope it helps.
Kind Regards,
Diego Lother
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.