I'm working on a query to assist with determining appropriate Credit Limits for our customers. I have been asked to research if we can develop a query to give what the highest balance has been for each BP all in one report. The target query will return back all of our current BP's and what their highest balance have been.
I found the following query by Gordon that will return the highest balance for a specified BP and have been working to use this as a base to create my query, but haven't had any luck so far.
SELECT T.SHORTNAME, MAX(T.Balance) Max
FROM
(SELECT T0.ShortName, (SELECT SUM(T2.DEBIT-T2.CREDIT) FROM dbo.JDT1 T2 WHERE T2.SHORTNAME = T0.ShortName AND DateDiff(d,T2.DueDate,T0.Duedate) >=0) 'Balance'
FROM dbo.JDT1 T0
WHERE T0.ShortName = '[%0]' AND DateDiff(yy,T0.DueDate,GetDate()) = 0) T
GROUP BY T.SHORTNAME
Any help would be appreciated.