For a Product last Business partner to find Query SAP B1

[former_member599250]

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

[azizelmir · Accepted solution]

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

[former_member599250 · Accepted solution]

Thank you for your answer

I can't see my products name or Products Price

How can I see this informations?

[azizelmir · Accepted solution]

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

[former_member294403]

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]

[former_member599250]

This Query give me only last order for last product. I want to see for all products last business partner.

Thank you

[former_member294403]

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

[former_member599250]

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]