Client Balances As Of Specific Date

[[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.

[Hillel CorenKeymaster]

Sorry, it isn’t supported

[[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

[Hillel CorenKeymaster]

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

[Author]

Posts