Skip to Content

Duplicate Data in Stock Query

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 09, 2010 at 05:48 PM

    Hi,

    Try this:

    SELECT Distinct T0.DocNum, T1.Dscription, T0.DocDate, T2.BatchNum,T1.ItemCode, T3.PrdDate, T1.Quantity, T1.LineTotal

    FROM dbo.ODLN T0

    INNER JOIN dbo.DLN1 T1 ON T0.DocEntry = T1.DocEntry

    INNER JOIN dbo.IBT1 T2 ON T2.BaseEntry = T0.DocEntry AND T2.BaseLinNum = T1.BaseLine

    INNER JOIN dbo.OIBT T3 ON T2.ItemCode = T3.ItemCode AND T2.BatchNum = T3.BatchNum

    WHERE (T0.DocDate >= CONVERT(DATETIME, '2009-10-01 00:00:00', 102)) AND

    (T3.PrdDate < CONVERT(DATETIME, '2009-01-01 00:00:00', 102))

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi, Gordon.

      I've try to replace T2.BatchNum with T3.BatchNum but some of the data missing and still the information of the item of different batches repeating. Same things happened if I remove Batch Number from query. Our Inventory Stock manages by batches and when someone order for 7 pallets of item then it will/may use 7 different batches of items.

  • author's profile photo Former Member
    Former Member
    Posted on Sep 13, 2010 at 07:52 AM

    Dear Friend ,

    Please Try this Query:

    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.DLN1 ON DBO.DLN1.DOCENTRY =DBO.ODLN.DOCENTRY

    INNER JOIN DBO.IBT1

    ON DBO.IBT1.BASEENTRY = DBO.DLN1.DOCENTRY AND DBO.IBT1.ITEMCODE = DBO.DLN1.ITEMCODE

    AND DBO.IBT1.BASETYPE=15 AND DBO.IBT1.BASELINNUM = DBO.DLN1.BASELINE

    INNER JOIN DBO.OIBT ON DBO.IBT1.BATCHNUM = DBO.OIBT.BATCHNUM

    WHERE (DBO.ODLN.DOCDATE >= ) AND

    (DBO.OIBT.PRDDATE < CONVERT(DATETIME, '2010-09-01 00:00:00', 102))

    Thanks

    Dhiraj

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.