Dear Experts,
I was trying to extract some old stock data from SAP B1 but it returns some duplicate data in my report. Can the experts here help me to check with the query below:
SELECT dbo.ODLN.DocNum, dbo.DLN1.Dscription, dbo.ODLN.DocDate, dbo.IBT1.BatchNum, dbo.DLN1.ItemCode, dbo.OIBT.PrdDate, dbo.DLN1.Quantity, dbo.DLN1.LineTotal FROM dbo.ODLN INNER JOIN dbo.IBT1 INNER JOIN dbo.DLN1 ON dbo.IBT1.BaseEntry = dbo.DLN1.DocEntry AND dbo.IBT1.ItemCode = dbo.DLN1.ItemCode AND dbo.IBT1.BaseLinNum = dbo.DLN1.BaseLine ON dbo.ODLN.DocEntry = dbo.DLN1.DocEntry INNER JOIN dbo.OIBT ON dbo.IBT1.ItemCode = dbo.OIBT.ItemCode AND dbo.IBT1.BatchNum = dbo.OIBT.BatchNum WHERE (dbo.ODLN.DocDate >= CONVERT(DATETIME, '2009-10-01 00:00:00', 102)) AND (dbo.OIBT.PrdDate < CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
The following is the sample result that I get:
DocNum Description Batch Posting Date Item No. Manufacturing Date Quantity Row Total 2886 Cherry Regal Classic Loc 14 x 190 x 2190mm I1703 16/07/2010 1C2N50NBAA 21/05/2008 59.76 6,738.24 2886 Cherry Regal Classic Loc 14 x 190 x 2190mm I1703 16/07/2010 1C2N50NBAA 21/05/2008 59.76 6,738.24 2947 Cherry Classic 14 x 180 x 2190mm D401116 21/08/2010 FG01 1C2N60ABAC 10/04/2007 113.52 5,946.18 2947 Cherry Classic 14 x 180 x 2190mm D401116 21/08/2010 FG01 1C2N60ABAC 10/04/2007 113.52 5,946.18 2947 Cherry Classic 14 x 180 x 2190mm G2657 21/08/2010 FG01 1C2N60ABAC 26/12/2006 113.52 5,946.18 2947 Cherry Classic 14 x 180 x 2190mm G2657 21/08/2010 FG01 1C2N60ABAC 26/12/2006 113.52 5,946.18
and I wish to get the result like this:
DocNum Description Batch Posting Date Item No. Manufacturing Date Quantity Row Total 2886 Cherry Regal Classic Loc 14 x 190 x 2190mm I1703 16/07/2010 1C2N50NBAA 21/05/2008 59.76 6,738.24 2947 Cherry Classic 14 x 180 x 2190mm D401116 21/08/2010 FG01 1C2N60ABAC 10/04/2007 113.52 5,946.18
Kindly guide me in the above issue. Thanks.