Last date of invoice in report

Advertisement
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]
Advertisement

Replay

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