AR Aging - Latest month end

What data it expects

Connect QuickBooks Online or drop a CSV / Excel export with a similar layout — the AI adapts the workflow if your columns differ.

How it works — every step, readable

1. 01Load QuickBooks AR Detail

   SELECT * FROM read_csv_auto('QBO_GeneralLedger.csv', header=true)

2. 02AR Aging Schedule — Latest Month End

   combines data from multiple inputs · aggregates rows into summary totals · buckets values by condition

   WITH clean_ar AS (
     SELECT
       CAST("date" AS DATE) AS txn_date,
       COALESCE(NULLIF(TRIM(CAST(name AS VARCHAR)), ''), 'Unassigned Customer') AS customer,
       CAST(account AS VARCHAR) AS account,
       COALESCE(TRY_CAST(REPLACE(CAST(amount AS VARCHAR), ',', '') AS DOUBLE), 0) AS amount
     FROM input_1
     WHERE account ILIKE '%Accounts Receivable%'
       AND TRY_CAST("date" AS DATE) IS NOT NULL
   ),
   as_of AS (
     SELECT last_day(MAX(txn_date)) AS as_of_date
     FROM clean_ar
   ),
   aged AS (
     SELECT
       c.customer,
       a.as_of_date,
       date_diff('day', c.txn_date, a.as_of_date) AS age_days,
       c.amount
     FROM clean_ar c
     CROSS JOIN as_of a
     WHERE c.txn_date <= a.as_of_date
   ),
   customer_aging AS (
     SELECT
       customer AS "Customer",
       MAX(as_of_date) AS "As Of Date",
       ROUND(SUM(CASE WHEN age_days <= 30 THEN amount ELSE 0 END), 2) AS "Current",
       ROUND(SUM(CASE WHEN age_days BETWEEN 31 AND 60 THEN amount ELSE 0 END), 2) AS "1-30 Days",
       ROUND(SUM(CASE WHEN age_days BETWEEN 61 AND 90 THEN amount ELSE 0 END), 2) AS "31-60 Days",
       ROUND(SUM(CASE WHEN age_days BETWEEN 91 AND 120 THEN amount ELSE 0 END), 2) AS "61-90 Days",
       ROUND(SUM(CASE WHEN age_days BETWEEN 121 AND 150 THEN amount ELSE 0 END), 2) AS "91-120 Days",
       ROUND(SUM(CASE WHEN age_days > 150 THEN amount ELSE 0 END), 2) AS "Over 120",
       ROUND(SUM(amount), 2) AS "Total"
     FROM aged
     GROUP BY customer
     HAVING ABS(SUM(amount)) > 0.005
   ),
   final_report AS (
     SELECT
       "Customer",
       "As Of Date",
       "Current",
       "1-30 Days",
       "31-60 Days",
       "61-90 Days",
       "91-120 Days",
       "Over 120",
       "Total",
       1 AS sort_order
     FROM customer_aging
   
     UNION ALL
   
     SELECT
       'TOTAL' AS "Customer",
       MAX("As Of Date") AS "As Of Date",
       ROUND(SUM("Current"), 2) AS "Current",
       ROUND(SUM("1-30 Days"), 2) AS "1-30 Days",
       ROUND(SUM("31-60 Days"), 2) AS "31-60 Days",
       ROUND(SUM("61-90 Days"), 2) AS "61-90 Days",
       ROUND(SUM("91-120 Days"), 2) AS "91-120 Days",
       ROUND(SUM("Over 120"), 2) AS "Over 120",
       ROUND(SUM("Total"), 2) AS "Total",
       2 AS sort_order
     FROM customer_aging
   )
   SELECT
     "Customer",
     "As Of Date",
     "Current",
     "1-30 Days",
     "31-60 Days",
     "61-90 Days",
     "91-120 Days",
     "Over 120",
     "Total"
   FROM final_report
   ORDER BY sort_order, ABS("Total") DESC