Prompt Playground

A. Every raw row is accounted for

SELECT status, COUNT(*) AS n, ROUND(SUM(amount), 2) AS total
FROM invoices
GROUP BY status
ORDER BY status;

Open 115 rows, $289,477.74. Paid 106 rows, $356,351.36. Void 20 rows, $46,843.89. The three add to 241, which matches the number of data rows in invoices.csv, and no other status appears.

B. No invoice is counted twice

SELECT invoice_id, COUNT(*) AS copies
FROM invoices
GROUP BY invoice_id
HAVING COUNT(*) > 1;

One row back: INV-10002, 2 copies. Zero rows would mean the IDs are unique. This one is an exact duplicate of an open $1,918.32 invoice.

C. Every invoice matches a customer

SELECT i.invoice_id, i.customer_id, i.amount, i.status
FROM invoices i
LEFT JOIN customers c ON c.customer_id = i.customer_id
WHERE c.customer_id IS NULL;

One row back: INV-10058, K031, $880.61, Open. This is a customer in one table and not the other, and an inner join would have dropped it from the total without any warning.

D. Amounts and dates are usable

SELECT
  SUM(CASE WHEN amount IS NULL OR amount <= 0 THEN 1 ELSE 0 END) AS bad_amounts,
  SUM(CASE WHEN julianday(invoice_date) IS NULL THEN 1 ELSE 0 END) AS bad_dates,
  MIN(invoice_date) AS first_date,
  MAX(invoice_date) AS last_date
FROM invoices;

0 bad amounts, 0 bad dates, first 2025-11-01, last 2026-03-31. Nothing is dated after the as-of date.

E. Rebuild the aging from the raw data

CREATE VIEW open_aged AS
SELECT *,
  CASE
    WHEN days_past_due <= 0  THEN 'Not due'
    WHEN days_past_due <= 30 THEN '1-30'
    WHEN days_past_due <= 60 THEN '31-60'
    WHEN days_past_due <= 90 THEN '61-90'
    ELSE '90+'
  END AS bucket
FROM (
  SELECT DISTINCT i.invoice_id, i.customer_id, i.invoice_date, i.amount,
    CAST(julianday('2026-03-31')
       - julianday(i.invoice_date, '+' || COALESCE(c.terms_days, 30) || ' days')
       AS INTEGER) AS days_past_due
  FROM invoices i
  LEFT JOIN customers c ON c.customer_id = i.customer_id
  WHERE i.status = 'Open'
);

SELECT bucket, COUNT(*) AS n, ROUND(SUM(amount), 2) AS total
FROM open_aged
GROUP BY bucket;

Not due 27, $83,174.21. 1-30 23, $46,353.83. 31-60 31, $74,364.19. 61-90 18, $46,539.40. 90+ 15, $37,127.79. Together that is 114 invoices and $287,559.42, which is the open total from check A less the $1,918.32 duplicate. The LEFT JOIN keeps INV-10058, and DISTINCT drops the second copy of INV-10002.

F. The bucket edges

SELECT invoice_id, days_past_due, bucket
FROM open_aged
WHERE days_past_due IN (0, 1, 30, 31, 60, 61, 90, 91)
ORDER BY days_past_due;

Five rows: two at 31 days in 31-60, one at 60 in 31-60, one at 90 in 61-90, and one at 91 in 90+. These are the rows an off-by-one mistake would move, so compare each to the workbook by hand.

G. Reload the workbook and compare

Save the Summary sheet as summary.csv with two columns, label and value, and import it as a table named template_out. Then recompute the same labels in SQL and list every one that disagrees.

CREATE TABLE sql_out AS
SELECT 'Total open AR' AS label, ROUND(SUM(amount), 2) AS value FROM open_aged
UNION ALL
SELECT 'Open invoice count', COUNT(*) FROM open_aged
UNION ALL
SELECT 'AR ' || bucket, ROUND(SUM(amount), 2) FROM open_aged GROUP BY bucket
UNION ALL
SELECT 'Count ' || bucket, COUNT(*) FROM open_aged GROUP BY bucket
UNION ALL
SELECT 'Share over 60 days (%)',
  ROUND(100.0 * SUM(CASE WHEN days_past_due > 60 THEN amount ELSE 0 END) / SUM(amount), 1)
FROM open_aged;

SELECT s.label, t.value AS workbook, s.value AS recomputed,
  ROUND(t.value - s.value, 2) AS difference
FROM sql_out s
LEFT JOIN template_out t ON t.label = s.label
WHERE t.value IS NULL OR ABS(t.value - s.value) > 0.005;

On the first pass this returned five rows, which is the check doing its job.

Every dollar difference is $1,918.32, which is INV-10002 from check B. The workbook's SUMIFS counted the duplicate twice, and it sits in 31-60 because it is 39 days past due. The share moved too, even though the duplicate is not over 60 days, because it inflated the total underneath the ratio. Removing the duplicate row in the workbook and saving the Summary sheet again brings the difference query to zero rows.

Second pass: zero rows. SQL and the workbook are two separate methods, and they now agree on every label, so the numbers can go in the note.