Skip to Content
0
Former Member
Jan 09, 2012 at 07:20 PM

Query for highest balance for each BP

133 Views

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.