Last invoice date for a customer with a matching line item

[lsauser]

Hey all,

I thought this one would have been an easy one for me by now. I need to display the last invoice date for a customer with an invoice item of PM. I've tried placing the where condition in a few different areas of the query and joining it in different areas with wildy different results. The code below is one i got off another page that asked for last invoice date but i couldnt see anything on 'invoice date containing X item from INV1'

SELECT DISTINCT T1.CardCode, T1.CardName,

(SELECT TOP1 T0.DocDate 
FROM OINV T0 WHERE T0.CardCode = T1.CardCode 
ORDERBY T0.DocDate DESC)AS'Last DocDate'

FROM OINV T1 

[former_member595093]

Pls try this

SELECT
	A.CardCode,
	A.CardName,
	Max(B.DocDate) AS 'Last DocDate',
	Max(B.DocEntry) AS 'Last DocEntry'
FROM OCRD A LEFT OUTER JOIN OINV B ON A.CardCode = B.CardCode
GROUP BY A.CardCode,A.CardName
HAVING Max(B.DocDate) IS NOT NULL
ORDER BY A.CardCode

[lsauser]

Hey hakan_ucar_90,

Thanks for your reply. This is still showing customers with the last docdate which is okay, but i need it to also work on the condition that INV1.itemCode LIKE '%%PM%%'

EDIT: I modified it a bit and it seems to work alright however some customers are showing an extra record that does not match this condition;

SELECT
A.CardCode,
A.CardName,
Max(B.DocDate) AS 'Last DocDate'
FROM OCRD A LEFT OUTER JOIN OINV B ON A.CardCode = B.CardCode
INNER JOIN INV1 C ON C.DocEntry = B.DocEntry
GROUP BY A.CardCode,A.CardName, C.itemCode
HAVING C.itemCode LIKE '%%PM%%'
ORDER BY A.CardCode

[lsauser]

Ok this code seems to work alright for showing the last docdate with a 'PM' in it;

SELECT
T0.CardCode,
T0.CardName,
MAX(T1.DocDate) AS 'Last PM Date'
FROM OCRD T0 
INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode
INNER JOIN INV1 T2 ON T2.DocEntry = T1.DocEntry
GROUP BY T0.CardCode,T0.CardName,T2.itemCode,T2.Dscription
HAVING T2.itemCode LIKE '%%PM%%' AND T2.Dscription LIKE '%%Preventative%%'
ORDER BY T0.CardCode

I am trying to include the DocNum as well as the line total for the 'PM' but it seems to give me more unrelated entries.

How would I go about fixing this up?