Query for 6 month high credit

[Former Member]

I have two UDF in business partners 1- last 6 months high credit and last 12 months high credit. How should i build my query so i can get the highest credit amount of a client in the past 182 days (6 months) and 365 days (12 months).

Thanks !!!

[keshavabest]

Here is a simple query that will work on SQL 2012 or later. Recommended to cache results each night to avoid performance impacts on large databases:

;WITH RunningBalance AS (

SELECT ShortName, RefDate,

SUM(Debit-Credit) OVER (PARTITION BY ShortName ORDER BY RefDate) as RunningBalance

FROM JDT1

JOIN OCRD ON JDT1.ShortName = OCRD.CardCode

WHERE OCRD.CardType != 'S' )

SELECT ShortName, MAX(RunningBalance) as [90DayHighCredit]

FROM RunningBalance

WHERE RefDate > GetDate()-90

GROUP BY ShortName

[Former Member · Accepted solution]

Hi Alain,

Which amunt do you refer to if you say credit amount? Is this the total balance of the business partner or the biggest document?

Adele

[Former Member · Accepted solution]

Hi Adele !

That would be the highest total balance due.

Thanks

[former_member184566 · Accepted solution]

Hi Alain

That could be a bit more difficult than it seems. If i was you id use SBO_SP_TransactionNotification (SAP stored procedure that comes with every sap database) to right to a UDT every transaction that effects his credit balance, then also right his balance at that time time. Then you can use a simple query to say get the highest in that table with in this period. Then you should also dlete values from the table thats older than two years to avoid the table from getting larger and larger.

Hope this helps

[Former Member · Accepted solution]

Hi Louis !

I understand your solution but i have like +/- 2500 active clients and there paiement method are most often little amounts each time. So that would cause alot of transaction in the table. Pretty soon it would get enormous... The query i would like to build could be run during night time to update the clients user fields. I understand that it could be very demanding on the server. If there nothing i can do about it how do i set up that SBO_SP_Trans... ?

Thanks

[Former Member · Accepted solution]

Hi Alain,

Your other option could be (seeing that it could run at night to update all records) is to calculate the value of the account balance based on the documents you use in your company that has an accounting effect on the customer's balance. You can add all invoice totals up to a certain date and then deduct all incoming payments, etc. This is a tedious process, but I don't know what else to suggest

Hope it helps,

Adele

[former_member184566 · Accepted solution]

Hi Alian

SBO_Transaction is a stored procedure, that executes a sql statement. SQL statement are relatively quick to run on a decent server. Basically you say if it is this "Object" do this query and so on.

Your other option is what Adele suggested. But you must also include credit notes and anything that hits that account like journal adjustments and so on (look at journals as a source), but that will not reflect highest credit balance. It will reflect "highest credit balance at the end of each working day". The "highest credit balance at the end of each working day" is what i'd be interested in if i was an account. Basically you would run a mini debtors statment at the end of the night to achieve this.

Do you agree?

Got to go, but i'll try log on later to answer anymore questions of yours.

Later

[Former Member · Accepted solution]

Thanks Adele ,

I'll take in mind both your suggestions. I think maybe i should have a routine that calculates, like you wrote, day by day for every client the invoices made minus the paiements, store the amount and the doc date in variables, and so on until date - 182... i'm just glad i'm not the server hahaha... I would have to find a way to lock the Shift F2 key on that field or else....

Thanks Adele

[Former Member · Accepted solution]

Hi Alain,

If I was you I would not let the customer calculate this value using a formatted search. I would make a process that runs every now and again (Like you said in the evenings) and then calculate the values with all the financial transactions taken into account, like invoices, credit notes, journals, payments, etc, etc. If you do not take all the transactions into account your balance will be wrong and the query will not help. You can make a user defined field and make it disabled and only display a value, not updateable.

Good Luck!

Adele

[Former Member · Accepted solution]

Thanks to you both,

Will let you know what the result sounds like...

Have a nice day