Home Forums Support Client Balances As Of Specific Date

This topic contains 3 replies, has 2 voices, and was last updated by  Hillel Coren 4 weeks ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #22496

    [email protected]
    Participant

    How do I run a report that shows client balances as of a specific date? Our accounting department needs a report that shows the Client Account Balances as of the end of the quarter. I have been running a client report, but if I were to run it now with the custom dates of say 1/1/2000 – 9/30/2019 it still subtracts out the payments which were made AFTER 9/30/2019.

    Is there a way to show balances as of 9/30/2019 which would NOT include payments made after that date?

    Thanks.

    #22497

    Hillel Coren
    Keymaster

    Sorry, it isn’t supported

    #22500

    [email protected]
    Participant

    That is unfortunate. 🙁

    I took the liberty to try to hack my own report. Can you please look at the mySQL statement and see if you see anything wrong with the way I’m doing this to generate the balances due on a certain date? I’m essentially searching the invoices and payments tables for anything before the date I need the report on, excluding any repeating setups and deleted items. I spot checked several of our clients and it seems to be working correctly, but I want to double check as I’m not 100% comfortable with your database structure.

    Thanks in advance!

    SET @useDate = '2019-09-31'; 
    
      SELECT client_id, cli.name, SUM(amount) AS invoiced, SUM(amt) AS paid, SUM(due) AS due, SUM(CASE WHEN due > 0 THEN 1 ELSE 0 END) AS openInvoices, MAX(invoice_date) AS lastInvoiceDate, MIN(aged) AS oldestInvoice
      FROM ( 
        SELECT 
          IF(ISNULL(inv.amount), 0, inv.amount) - IF(ISNULL(pay.amt), 0, pay.amt) AS due,
          IF( IF(ISNULL(inv.amount), 0, inv.amount) - IF(ISNULL(pay.amt), 0, pay.amt) > 0, inv.invoice_date, NULL ) AS aged,
          pay.amt, inv.*
        FROM ( SELECT * FROM ninja.invoices AS inv WHERE invoice_date <= @useDate AND is_deleted = 0 AND is_recurring = 0) AS inv
        LEFT JOIN ( SELECT invoice_id, SUM(amount) AS amt FROM ninja.payments WHERE payment_date <= @useDate AND is_deleted = 0 GROUP BY invoice_id ) AS pay ON pay.invoice_id = inv.id
      ) AS lst
      LEFT JOIN clients AS cli ON cli.id = lst.client_id
      GROUP BY client_id
      ORDER BY ( IF(SUM(due) > 0, 1, 2) ), name
    
    #22503

    Hillel Coren
    Keymaster

    I’m sorry, I’m not able to validate your query is correct

    One potential problem is I don’t think it’s excluding quotes, you’d need to add invoice_type_id = 1

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.

Posted in: