Purpose of this query
This SuiteQL statement produces one summary row showing total open Accounts Receivable and the amounts in Current, 1–30, 31–60, 61–90, and 90+ day buckets. It uses a two-leg UNION ALL pattern to combine customer transactions and journal entries, filters to posting lines and the primary accounting book, and applies FLOOR to the CURRENT_DATE minus Due Date calculation so bucket boundaries are stable and match NetSuite aging precisely. Change the subsidiary, accounting book, or replace CURRENT_DATE with a fixed as-of date to run historical aging.
SuiteQL
-- AR Aging: one row, total open AR by aging bucket
-- Same 2-leg pattern — drop GROUP BY for a single summary line
SELECT
SUM( "Net Amount" ) AS "Total Open AR",
SUM( CASE WHEN FLOOR( CURRENT_DATE - "Due Date" ) < 1 THEN "Net Amount" ELSE 0 END ) AS "Current",
SUM( CASE WHEN FLOOR( CURRENT_DATE - "Due Date" ) BETWEEN 1 AND 30 THEN "Net Amount" ELSE 0 END ) AS "1-30 Days",
SUM( CASE WHEN FLOOR( CURRENT_DATE - "Due Date" ) BETWEEN 31 AND 60 THEN "Net Amount" ELSE 0 END ) AS "31-60 Days",
SUM( CASE WHEN FLOOR( CURRENT_DATE - "Due Date" ) BETWEEN 61 AND 90 THEN "Net Amount" ELSE 0 END ) AS "61-90 Days",
SUM( CASE WHEN FLOOR( CURRENT_DATE - "Due Date" ) > 90 THEN "Net Amount" ELSE 0 END ) AS "90+ Days"
FROM (
-- LEG 1: All customer transactions
SELECT
COALESCE( tal.amountunpaid, 0 )
- COALESCE( tal.paymentamountunused, 0 ) AS "Net Amount",
COALESCE( t.duedate, t.trandate ) AS "Due Date"
FROM Transaction t
INNER JOIN TransactionAccountingLine tal ON ( tal.transaction = t.id )
INNER JOIN Account a ON ( a.id = tal.account )
INNER JOIN Customer c ON ( c.id = t.entity )
WHERE t.posting = 'T'
AND tal.posting = 'T'
AND tal.accountingbook = 1
AND t.voided = 'F'
AND t.subsidiary = 1
AND a.accttype = 'AcctRec'
AND a.eliminate = 'F'
AND ( tal.amountunpaid <> 0 OR tal.paymentamountunused > 0 )
UNION ALL
-- LEG 2: Journal entries (entity on TransactionLine, not header)
SELECT
COALESCE( tal.amountunpaid, 0 ) AS "Net Amount",
t.trandate AS "Due Date"
FROM Transaction t
INNER JOIN TransactionLine tl ON ( tl.transaction = t.id )
INNER JOIN TransactionAccountingLine tal
ON ( tal.transaction = t.id AND tal.transactionline = tl.id )
INNER JOIN Account a ON ( a.id = tal.account )
WHERE t.posting = 'T'
AND tal.posting = 'T'
AND tal.accountingbook = 1
AND t.voided = 'F'
AND t.subsidiary = 1
AND t.recordtype = 'journalentry'
AND t.entity IS NULL
AND a.accttype = 'AcctRec'
AND a.eliminate = 'F'
AND tal.amountunpaid > 0
)
WHERE "Net Amount" <> 0
Sample Output
How the query works
Two-leg pattern to capture all AR activity
The first leg pulls customer transactions (invoices, credits, payments) using transaction accounting lines so unpaid amounts and unapplied payments net correctly. The second leg captures AR-related journal entries where the entity is on the transaction line. UNION ALL merges both sources into one dataset.
Net amount per row
Net Amount is calculated as amountunpaid minus paymentamountunused for normal transactions and as amountunpaid for journal lines, giving the true open balance per posted line.
Using FLOOR for stable bucket assignment
The bucket calculation uses FLOOR(CURRENT_DATE - Due Date) to convert fractional day differences into whole days before evaluating ranges. This prevents off-by-one issues caused by time-of-day fractions and ensures bucket assignment matches expected aging boundaries.
Single summary row
After UNION ALL, the outer query sums Net Amount across all rows and places totals into the configured aging buckets, returning one row with Total Open AR plus each aging bucket.
Primary book and subsidiary filters
The query restricts to tal.accountingbook = 1 to avoid double counting in multi-book environments and limits the results to t.subsidiary = 1. Change these values to run for different books or subsidiaries.
Customization notes
• Change the subsidiary: modify t.subsidiary = 1.
• Change accounting book: modify tal.accountingbook = 1.
• As-of date: replace CURRENT_DATE with TO_DATE(‘YYYY-MM-DD’,‘YYYY-MM-DD’) or another fixed date for historical aging.
• Adjust buckets: change the FLOOR(…) ranges in the CASE expressions.
• Consolidation: add BUILTIN.CONSOLIDATE if you need consolidated currency balances across subsidiaries.