Skip to Content

For a Product last Business partner to find Query SAP B1

Hello

How can i find for a product last order(Business partner) in Query? (Query will not give another orders for Product (Only last order))

Thank you

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Feb 20, 2019 at 11:18 AM

    Hi Onurcan,

    The attached Query provides results in a manner and form that you have requested.

    Select W1.Cardcode,W1.CardName, W1.Docnum, w1.DocTotal, W1.Docdate
    From
    (SELECT  T0.CardCode, T0.CardName, T0.DocNum, T0.DocTotal, t0.Docdate
    ,ROW_NUMBER() OVER (PARTITION BY t0.Cardcode  ORDER BY t0.Docnum DESC) AS rn
    FROM OPOR T0  
    WHERE (select Top 1  ShipDate from POR1 where POR1.Docentry=T0.Docentry)  - CONVERT (date, GETDATE()) BETWEEN -200 AND 0
    ---Order by t0.Cardname asc , Docnum desc
    ) 
    as W1 where rn=1 
    order by  W1.CardCode asc
    

    Thank you,

    Aziz

    Add a comment
    10|10000 characters needed characters exceeded

    • Here you go ...

      Select W1.ItemCode,W1.Dscription,W1.Price ,W1.CardName, W1.Docnum, w1.DocTotal, W1.Docdate as 'Posting Date'
      From
      (
      SELECT  T1.ItemCode,T1.Dscription,T1.Price,T0.CardCode, T0.CardName, T0.DocNum, T0.DocTotal, t0.Docdate
      ,ROW_NUMBER() OVER (PARTITION BY t1.ItemCode  ORDER BY t0.Docnum DESC) AS rn
      FROM OPOR T0 inner Join POR1 t1 on T0.Docentry=T1.Docentry  
      WHERE (select Top 1  ShipDate from POR1 where POR1.Docentry=T0.Docentry)  - CONVERT (date, GETDATE()) BETWEEN -200 AND 0
      ) 
      as W1 where rn=1 
      order by  W1.ItemCode asc
      
  • author's profile photo Former Member
    Former Member
    Posted on Feb 20, 2019 at 09:37 AM

    Try this

    SELECT TOP 1 T0.CardCode, T0.CardName, T0.DocNum, T0.DocDate, T0.DocTotal
    
    FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry =  T1.DocEntry
    
    WHERE T1.ItemCode = [%0]
    
    ORDER BY T0.DocDate DESC, T0.DocNum Desc
    Add a comment
    10|10000 characters needed characters exceeded

    • Thank you for your answer

      Can you help with my Query?

      I want to see only last order(Business Partner) for Products

      SELECT T1.[ShipDate], T1.[ItemCode], T1.[Dscription], T1.[Price], T0.[CardName] FROM OPOR T0  INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
      WHERE T1.[ShipDate] - CONVERT (date, GETDATE()) BETWEEN -200 AND 0
      ORDER BY T1.[ShipDate]
      
  • author's profile photo Former Member
    Former Member
    Posted on Feb 20, 2019 at 11:51 AM

    Try this modified query

    SELECT TOP 1 T1.[ShipDate], T1.[ItemCode], T1.[Dscription], T1.[Price], T0.[CardName] FROM OPOR T0  INNER JOIN POR1 T1 ON T0.[DocEntry]= T1.[DocEntry] WHERE T1.[ShipDate]- CONVERT(date, GETDATE()) BETWEEN -200 AND 0 ORDER BY T1.[ShipDate]
    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.