cancel
Showing results for 
Search instead for 
Did you mean: 

last date of invoice in report

former_member377478
Participant
0 Kudos

Dear experts,

could you help me with inserting right column" last date of document" in this query?

Thank you in advance,

jonmar7

SELECT DISTINCT T0.[CardCode], T0.[CardName], T2.[SlpName], T4.[ItmsGrpNam],

(SELECT TOP 1 T1.DocDate AS 'Last DocDate'

FROM OINV T1

ORDER BY T1.DocDate DESC )

, T3.[Discount] FROM [dbo].[OCRD] T0 INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode INNER JOIN OSPG T3 ON T0.CardCode = T3.CardCode, OITB T4 WHERE T4.[ItmsGrpCod] = T3.[ObjKey] AND T2.[SlpName] =[%0] order by T0.[CardCode], T4.[ItmsGrpNam]

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member377478
Participant
0 Kudos

Thank you, Gordon, it works !!!

Former Member
0 Kudos

Hi,

Try:

SELECT T0.CardCode, T0.CardName, T2.SlpName, T4.ItmsGrpNam, MAX(T1.DocDate),T3.Discount

FROM dbo.OCRD T0

INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

INNER JOIN OSPG T3 ON T0.CardCode = T3.CardCode

INNER JOIN OITB T4 ON T4.ItmsGrpCod = T3.ObjKey

WHERE T2.SlpName =[%0\]

GROUP BY T0.CardCode, T0.CardName, T2.SlpName, T4.ItmsGrpNam, T3.Discount

order by T0.CardCode, T4.ItmsGrpNam

Thanks,

Gordon