cancel
Showing results for 
Search instead for 
Did you mean: 

Query duplicating lines

ebie
Explorer
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member185682
Active Contributor
0 Kudos

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

Answers (3)

Answers (3)

ebie
Explorer
0 Kudos

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

gonzalogomez
Active Contributor
0 Kudos

add in the select max(t0.itemname) and remove it from de group by

former_member185682
Active Contributor
0 Kudos

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