Skip to Content
0
Sep 09, 2010 at 08:18 AM

Duplicate Data in Stock Query

82 Views

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.