Last Price Paid - Customers

[former_member596919]

Hi all,

I wonder if someone could help. I'm trying to write a query that returns the list of items that a customer has bought from us with the last price they have been charged. The below query shows the max delivery date for each price ever charged and therefore has duplicates for certain customers and items. I only want to see the last price charged for each customer. Any idea how I can just show the last price paid? I'd also like to only look at customers who have bought from us this year. I'm aware of the report in SAP "Last Prices Report" but this is not suitable as I need the full list for all customers and items.

Thanks

Kirstie

SELECT T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Currency], T1.[Price], MAX(T0.[DocDueDate]) AS 'Latest Del Date' FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDueDate] > '01/01/2014' GROUP BY T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Currency], T1.[Price]

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try:

SELECT T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Currency], max(T1.[Price]), T0.[DocDueDate] AS 'Latest Del Date' FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDueDate] > '01/01/2014' GROUP BY T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Currency], T0.[DocDueDate]

Thanks & Regards,

Nagarajan

[former_member596919 · Accepted solution]

Hi,

Been looking at this again, I'm afraid the above does not work either, as this shows the highest price ever paid, not the last price paid.

Kind Regards,

Kirstie

[former_member212181 · Accepted solution]

Hi Kirstie,

Please try below Query. I used OINV and INV1 Table to pick last sales data. If you need ORDR and RDR1 then you can replace table name accordingly

--- Query Starts

;WITH LastARPerCustomer AS

(SELECT T0.[CardCode],Max(T0.[DocEntry])[Entry]

FROM OINV T0

WHERE T0.[CANCELED] ='N'

  and T0.[DocDate] >=[%0]

  and  T0.[DocDate] <=[%1]

GROUP BY T0.[CardCode])

Select A.CardCode, C.CardName, B.ItemCode, B.Dscription, B.Currency, B.Price, A.DocDueDate [Latest Del Date]

From OINV A

  Inner Join INV1 B on A.DocEntry = B.DocEntry

  Inner Join OCRD C on A.DocEntry = C.DocEntry

  Inner Join LastARPerCustomer D on A.CardCode = D.CardCode and A.DocEntry = D.Entry

---Query End

Thanks

Unnikrishnan

[former_member596919 · Accepted solution]

Hi Unnikrishnan,

Thank you, I have tried the above, but unfortunately the report is coming back with no matching results.

Kind Regards,

Kirstie

[former_member212181 · Accepted solution]

Hi Kirstie,

Above query is working in my system.

in last query i tried to pick data from AR Invoice table ie OINV

Below query will pick data from Sales Order. Please try and and let me know the results.

-----Query Starts--------

;WITH LastARPerCustomer AS

(SELECT T0.[CardCode],Max(T0.[DocEntry])[Entry]

FROM ORDR T0

WHERE T0.[CANCELED] ='N'

  and T0.[DocDate] >=[%0]

  and  T0.[DocDate] <=[%1]

GROUP BY T0.[CardCode])

Select A.CardCode, C.CardName, B.ItemCode, B.Dscription, B.Currency, B.Price, A.DocDueDate [Latest Del Date]

From ORDR A

  Inner Join RDR1 B on A.DocEntry = B.DocEntry

  Inner Join OCRD C on A.DocEntry = C.DocEntry

  Inner Join LastARPerCustomer D on A.CardCode = D.CardCode and A.DocEntry = D.Entry

--------Query Ends-------

Regards

Unnikrishnan

[former_member596919 · Accepted solution]

Hi Unnikrishnan,

I have tried the above, but again the report is running fine but is displaying no results. This is when I enter dates into greater than and less than.

When I enter today's date into less than, results are shown but these are very limited and are showing dates from years ago, not the latest delivery.

Kind Regards,

Kirstie

[former_member212181 · Accepted solution]

Please try this one,

I hard coded "From date" input criteria as 2014 -Jan-01 and data is taking from ORDR.

-----Query Starts--------

;WITH LastARPerCustomer AS

(SELECT T0.CardCode, T1.ItemCode, Max(T0.[DocEntry])[Entry],Max(T1.LineNum)[Line]

FROM ORDR T0

  Inner Join RDR1 T1 on T0.DocEntry = T1.DocEntry

WHERE T0.[CANCELED] ='N'

  and T0.[DocDate] >= '20140101'

GROUP BY T0.CardCode, T1.ItemCode)

Select A.DocNum, A.DocDate,  A.CardCode, C.CardName, B.ItemCode, B.Dscription, B.Currency, B.Price, A.DocDueDate [Latest Del Date]

From ORDR A

  Inner Join RDR1 B on A.DocEntry = B.DocEntry

  Inner Join OCRD C on A.DocEntry = C.DocEntry

  Inner Join LastARPerCustomer D on A.DocEntry = D.Entry and B.LineNum = D.Line

--------Query Ends-------

Thanks

Unnikrishnan

[Former Member · Accepted solution]

Hi Unni,

I had checked your query on my system , one thing which is not correct is BP name, code and name both are different.

Regards,

Bhushan verma

[former_member212181 · Accepted solution]

Hi bhushan,

Many thanks, I got it.

There was an issue in linking OCRD

Wrong one used :   Inner Join OCRD C on A.DocEntry = C.DocEntry

Corrected to :   Inner Join OCRD C on A.CardCode = C.CardCode

Thanks Again

Unnikrishnan

[former_member212181 · Accepted solution]

Please ignore last query and try below one.

---------------------

;WITH LastARPerCustomer AS

(SELECT T0.CardCode, T1.ItemCode, Max(T0.[DocEntry])[Entry],Max(T1.LineNum)[Line]

FROM ORDR T0

  Inner Join RDR1 T1 on T0.DocEntry = T1.DocEntry

WHERE T0.[CANCELED] ='N'

  and T0.[DocDate] >= '20140101'

GROUP BY T0.CardCode, T1.ItemCode)

Select A.DocNum, A.DocDate,  A.CardCode, A.CardName, B.ItemCode, B.Dscription, B.Currency, B.Price, A.DocDueDate [Latest Del Date]

From ORDR A

  Inner Join RDR1 B on A.DocEntry = B.DocEntry

  Inner Join OCRD C on A.CardCode = C.CardCode

  Inner Join LastARPerCustomer D on A.DocEntry = D.Entry and B.LineNum = D.Line

----------------

Thanks

Unnikrishnan

[former_member596919 · Accepted solution]

Hi Unnikrishnan,

That seems to have done it now, thank you for your help!

Kind  Regards,

Kirstie

[Former Member · Accepted solution]

Hi Unni,

Thank you for providing us with the query.

good work done.

Regards,

Bhushan Verma