Average Days to Pay Calculation (SQL Code)

Posted on by

Average Days To Pay (ADTP) is a very crucial information for us to assess a customer or group of customers and their nature of paying the invoices.

In GP, we have this calculated and displayed when we select a customer in Customer Credit Summary window (shown below):

The following is the definition & formula taken from GP Receivables Management manual, which clearly explains how this is calculated:

After a customer has paid his or her first invoice, the average days to pay (ADTP) is calculated based on the number of invoices a customer has, the time taken to pay the first invoice, and the time taken to pay the most recent invoice.

The formula for calculating the average days to pay is:

ADTP = (Current ADTP) x (Number of Invoices) + (Number of Days Taken to Pay Most Recent Invoice) / (Number of Invoices + 1)

The time it took to pay the first invoice would provide the initial value for the Current ADTP. Any later invoices paid by this customer will provide the values for the number of invoices and the number of days taken to pay the most recent invoice. The ADTP calculated on the customer’s initial invoices then becomes the “Current ADTP.” You can use this value when you recalculate the ADTP for later invoices.

With this as basis, I have written a SQL script (link for which is provided at the end of this post) which will calculate ADTP group by a Customer ID.

Parameters of this SQL Procedure:

1. @FROMDATE – Self-Explanatory

2. @TODATE – Self-Explanatory

So pass two dates and the results are shown based on invoices between those two dates.

I have heard from several GP users that the result what GP shows on Customer Credit Summary window is not correct at times. This SQL script will give you a chance to compare between two and decide which one’s correct.

As always, your feedbacks are welcome.

RM_AverageDaysToPay.sql

VAIDY

31 thoughts on “Average Days to Pay Calculation (SQL Code)”

  2. In this example there is no way to have a weighted average. If there are two invoices and one is 10k and the other is 1K the amount of time its takes to pay each is treated equally

    Like

    Reply

    • Hi Mike, it’s late, but I thought I would post my opinion on this. I am not sure how we could calculate ADP based on the invoice amounts. As I understood from the concept, it’s time based and not amount based. Time taken to pay an invoice is how we calculate ADP, I guess. Please do reach me to my email ID: vaidyDOTdyngpATgmailDOTcom, if you would like to explain me on this.

      Like

      Reply

  3. This formula only takes into consideration invoices/documents that have been fully paid, What about when customer has outstading invoices, for example if a customer pays on time the first few invoices and then stops paying several other invoices the ADTP will show a good number that will misslead the “grade” as to how good of paying customer really is

    Like

    Reply

      • Vaidy,
        I think that this exactly what I am looking for. How do I get it to run ad where does the output go? I tried running it in a query window in SQL mgmt. studio and the response I get is the ‘command completed successfully’. What am I missing?

        Tim

        Like

      • Hi Tim, the script creates this stored procedure and makes it available for you to run it anytime. To get the results, you have to run following T-SQL statement:

        EXEC RM_AverageDaysToPay '2013-01-01', '2013-12-31'

        Please note the format in which I have entered “From Date” and “To Date” parameters. It’s YYYY-MM-DD.

        Hope that helps.

        Vaidy

        Like

      • Vaidy,
        your instructions worked perfectly. We have not done paid transaction removal on any of our companies to move the paid ar trx to the rm30101. is there any way that it could be altered to run against the rm20201 which is the apply open tbl?

        Like

      • PTR is run specifically to confirm that our invoices/payments are applied and cleared. At any point in time, you could unapply the payment against an invoice prior to PTR.

        So in a scenario like I have a payment applied against an invoice, I run the report today to analyse ADTP, tomorrow I unapply and run the report, my ADTP is not same anymore.

        But when I run PTR, I know for sure that my invoices are paid fully and are ready for ADTP analysis. That would make more sense, IMO.

        Like

      • I understand your concern with the PTR not being done, however we want the ability to void invoices without having to do a return or credit. Our people understand that if an invoice is paid and then voided either that invoice would have to be reentered and reapplied or the payment refunded. They also realize that the ADTP number would change and the report would need to be rerun. So given those situations above, could the ADTP be run against the rm20201.

        Like

      • Vaidy,
        This is tim. Were you able to modify the code to extract the records out of the rm20201 instead of the rm30101 for the ADTP calculation? I had sent an emaill from my yahoo acct to you gmail acct so you could get my contact info. did you ever get that?

        Like

  5. Hi Vaidy – Thanks for the SQL Script. I have inherited a client that has not closed AR in several years. I have updated the RM00103 fields from the values in RM00104 and from your scipt. However, two fields have me stumped. NUMADTPY (Number of ADTP Documents Year) and NUMADTPR (Number of ADTP Documents last year).

    My guess is that these are used during posting of apply documents to update the AVGDTPYR and AVDTPLYR fields. I cannot determine how to count an ADTP document. I have considered payments, credit memos, fully paid invoices, etc. In reviewing the TWO data (Fabrikam) I cannot find a way to match the RM00103 values to counts od any combination of document type (RMDTYPAL).

    I think it is important to get these values correct in RM00103 so that calculations of AVGDTPYR during the year will be accurate. Any assistance would be appreciated.

    Like

    Reply

    • Hi Jeff, sorry for this late reply. Your observation on NUMADTPY and NUMADTPR is totally correct. They get incremented (or decremented if any RM document is voided/reversed) as and when an RM document is posted and moved to history by Paid Transaction Removal process.

      Concept is to find how long it took for an invoice to get paid. This can be calculated only after we apply a payment and confirm (by removing paid transactions using PTR) that they are paid fully.

      Values on RM00103 will be correct only when we complete PTR process. Having said that, even my SQL program will work only when receivables users enter the correct apply date. If a customers pays me against an invoice, but I have not applied it yet, RM00103 won’t have that invoice added to the count. When I finally apply that payment, I must enter correct apply date. If there is any delay in that my ADTP graph will be wrong.

      Hope this helps.

      Like

      Reply

  6. Hi Vaidy,

    This script looks really good. I do have a couple questions.

    The first question is regarding the from and to data parameters. If we want ADTP from the very beginning of data entry into GP to current, do we just put ‘2013-01-01’ to ‘2014-04-11’, assuming we started on 2013?

    Also, is the Average Days Past Due based on the days past the doc date or days past the due date?

    Thanks!

    Steve

    Like

    Reply

    • Hi Steve,

      Ans #1: If you want to see ADTP from very beginning, use ‘1900-01-01’ as your From Date. This will bring all invoices that are posted in the system.

      Ans #2: The script what I have written calculates days taken to pay a document past due date.

      Vaidy

      Like

      Reply

      • Thanks for replying so soon! I believe my customer wants to have days from the document date, but I was able to update the script you provided by subbing the doc date for the due date.

        Also, a prior post by Tim asked if this script can be modified to use the RM20201 instead of the History table. Is this possible?

        Thank you!

        Like

  10. Pingback: Average Days To Pay Calculation – History & Open (SQL Script) | Dynamics GP - Learn & Discuss

  12. Pingback: Average Days To Pay Calculation – History & Open (SQL Script) - Microsoft Dynamics GP Community

  13. Pingback: Average Days To Pay Calculation – History & Open (SQL Script) : Interesting Findings & Knowledge Sharing

Leave a comment