<!-- https://learnsql.cpetoday.com/analyze/audit-analytics-sql -->
# Audit and controls analytics in SQL

Auditors test whole populations where they can. A journal entry test reads every entry in the period and flags the ones that match a risk. A duplicate test reads every bill and finds the pairs. This guide gives you the tests that matter most, the SQL for each one, what the sample data showed, and what to do with the hits.

Every query here was run against the LearnSQL sample databases. The results describe sample data, not any real client. The point is the method. Use the same steps on your own ledger.

## How the tests fit together

A test has five parts: a population, a risk, a query, a list of hits, and a follow-up. The query only finds candidates. A person decides what the hits mean.

> Diagram: Three columns. Four sample data sources feed four groups of audit tests, and every test flows into one exception list that is then sampled and followed up.

Start every test with the population. Count the rows, then tie that count to a second source, such as the trial balance or a control total. A test on a partial population proves nothing about the rest.

## Which sample data supports which test

Not every database has every column. Check first. This table lists what each test needs and where the sample has it.

| Test | Sample database | Tables and columns used | Supported? |
|---|---|---|---|
| Weekend and holiday postings | `nonprofit` | `gl_journal_entries.entry_date` | Yes, by date only |
| After-hours postings | `qbd` | `JournalEntries.TimeCreated` | Mechanically yes, but the timestamps are old (see below) |
| Round amounts and threshold | `nonprofit` | `gl_journal_lines.debit` | Yes. There is no approval table, so the threshold is an assumption |
| Posting user and unusual users | `nonprofit` | `gl_journal_entries.posted_by` | Yes |
| Entries with no source document | `nonprofit` | `gl_journal_entries.source_id` | Yes |
| Rarely used accounts | `nonprofit` | `gl_journal_lines`, `gl_accounts` | Yes |
| Unbalanced entries | `nonprofit` | Entries and lines | Yes |
| Duplicate bills | `qbd`, `nonprofit`, `bigbear` | `QuickBooks.Bills`, `bills`, `invoices` | Yes |
| Vendor tax ID | `qbd` | `QuickBooks.Vendors.TaxIdentity` | Yes |
| Vendor and employee shared address | `qbd`, `bigbear`, `nonprofit` | `Vendors.Line1`, `Employees.Line1` | Partly. Only `qbd` has street addresses for employees |
| Benford's law | `nonprofit` | `bills.amount` | Yes |
| Cutoff | `nonprofit` | Entry dates and fiscal periods | Only the period tag. The data has no service or receipt dates |
| Check number gaps | `qbd` | `QuickBooks.Checks.ReferenceNumber` | Yes |
| AP aging | `nonprofit` | `bills.due_date`, `status`, `amount` | Yes |

The `posted_by` column exists only in `nonprofit`. The `bigbear` journal has no user column, and `qbd` journal entries have a created time but no user.

> **Audit tip.** A real user-access test needs the system's audit log, which the samples do not include.

**Runs on:** all queries in this guide are written and tested in SQL Server (T-SQL). The qbd database is on SQL Server only. The nonprofit and bigbear databases are also on MariaDB, but there you need MariaDB date functions, such as `DAYOFWEEK()` in place of `DATEPART(weekday, ...)` and `LIMIT` in place of `TOP`. The `DATEPART(weekday, ...)` results below assume the US English default, where Sunday is day 1 and Saturday is day 7.

## Journal entry tests

Journal entries carry the most audit risk, because management can post them directly. The five tests below run on the `nonprofit` ledger.

### Entries on weekends and holidays

**Question.** Did anyone post on a Saturday or Sunday, or on a fixed holiday? Weekend work is not wrong. It is a population to explain. The fixed holidays here are New Year's Day, Independence Day and Christmas.

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT e.source_type,
       COUNT(*) AS entries,
       SUM(CASE WHEN DATEPART(weekday, e.entry_date) IN (1, 7) THEN 1 ELSE 0 END) AS weekend_entries,
       SUM(CASE WHEN (MONTH(e.entry_date) = 1 AND DAY(e.entry_date) = 1)
                  OR (MONTH(e.entry_date) = 7 AND DAY(e.entry_date) = 4)
                  OR (MONTH(e.entry_date) = 12 AND DAY(e.entry_date) = 25)
                THEN 1 ELSE 0 END) AS fixed_holiday_entries
FROM dbo.gl_journal_entries AS e
GROUP BY e.source_type
ORDER BY entries DESC;
```

**What it returns.** About 6,600 of 34,377 entries fall on a weekend, roughly 19 percent. Most of them are bill payments and gift batches. Bill payments alone account for 3,879 weekend entries. That is probably a date rule in the system, such as the date a payment file was created. Ask. Do not assume fraud.

The useful hit list is different. The `Bill` source has 130 entries dated on a fixed holiday. A bill should not carry a holiday date unless the vendor invoiced that day. Sample those first.

**What to do.** Ask the owner of the source system how each date is set. If the date is the system date, the test is looking at processing, not at people. If the date is typed in by a person, read the support.

### Round-dollar amounts

**Question.** Are there lots of round amounts, such as exactly $5,000.00, where the support should show cents? Round amounts can mean estimates, plugs or manual entries. Test by account type, because some round numbers are normal (for example, a fixed monthly rent).

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT a.account_type,
       COUNT(*) AS debit_lines,
       SUM(CASE WHEN l.debit % 1000 = 0 THEN 1 ELSE 0 END) AS round_thousands,
       SUM(CASE WHEN l.debit % 100 = 0 THEN 1 ELSE 0 END) AS round_hundreds
FROM dbo.gl_journal_lines AS l
JOIN dbo.gl_accounts AS a ON a.gl_account_id = l.gl_account_id
WHERE l.debit >= 1000
GROUP BY a.account_type
ORDER BY debit_lines DESC;
```

**What it returns.** Asset lines have 219 round-thousand debits out of 7,965. Expense lines have 5 out of 8,836. Round amounts are common in cash and investment movements (transfers between accounts) and rare in expenses. A round expense is worth a look. A round transfer usually is not.

### Amounts just below an approval threshold

**Question.** Does the population bunch just under an approval limit? This sample has no approval table, so assume a limit of $10,000 and test the bands beneath it. If a band just under the limit is much bigger than the bands around it, people may be splitting or shaving amounts.

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT FLOOR(l.debit / 500) * 500 AS band_start,
       COUNT(*) AS lines
FROM dbo.gl_journal_lines AS l
WHERE l.debit BETWEEN 8000 AND 10999.99
GROUP BY FLOOR(l.debit / 500) * 500
ORDER BY band_start;
```

**What it returns.** The six $500 bands from $8,000 to $10,999 hold 100, 116, 102, 87, 86 and 56 lines. The $9,500 band (87 lines) sits just under the limit and the $10,000 band (86) just above it. The counts fall steadily as amounts rise. There is no spike below $10,000. This population does not show threshold avoidance at this limit.

**What to do.** Replace the assumed limit with your real one. A limit on purchase orders, a signing authority, or a board limit is the number to use. Run the test again at that number.

### Entries by poster and entries with no source

**Question.** Who posts, and do some people post entries no one else would? Two tests help. First, look at posters with few entries, since a rarely used user posting in a sensitive account is a classic flag. Second, look for entries with no source document (a blank `source_id`).

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT e.posted_by,
       COUNT(*) AS entries,
       SUM(CASE WHEN DATEPART(weekday, e.entry_date) IN (1, 7) THEN 1 ELSE 0 END) AS weekend_entries,
       COUNT(DISTINCT e.source_type) AS source_types
FROM dbo.gl_journal_entries AS e
GROUP BY e.posted_by
HAVING COUNT(*) <= 20
ORDER BY entries, e.posted_by;
```

**What it returns.** Fifteen posters have 20 or fewer entries. They post in one to four source types, and most of their entries fall on weekdays. Nothing about them stands out as a group.

The no-source test is more useful when you know which entries should have no source. Run it like this:

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT e.source_type, COUNT(*) AS entries_without_source_id
FROM dbo.gl_journal_entries AS e
WHERE e.source_id IS NULL
GROUP BY e.source_type;
```

**What it returns.** All 189 `Monthly` entries and the one `Opening` entry have no source ID. The monthly entries are the system's allocation entries, so they are expected. Filter known types out, then read what is left. In this sample nothing is left.

### Rarely used accounts

**Question.** Which accounts have very few postings? A new or seldom-used account, especially one tied to cash, revenue or an estimate, deserves a look.

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT a.account_number, a.account_name, a.account_type,
       COUNT(*) AS lines,
       SUM(l.debit) AS debits,
       SUM(l.credit) AS credits
FROM dbo.gl_journal_lines AS l
JOIN dbo.gl_accounts AS a ON a.gl_account_id = l.gl_account_id
GROUP BY a.account_number, a.account_name, a.account_type
HAVING COUNT(*) <= 50
ORDER BY lines, a.account_number;
```

**What it returns.** Four accounts have 50 lines or fewer. Two of them are opening balances (1010 with one line for $900,000 and 3000 with two lines). Pledge Write-offs (6900) has 29 lines and $62,615 of debits. Write-offs are rare by design, so the answer is to read the 29 entries and check that each one has an approval.

### Unbalanced entries

**Question.** Does every entry balance, with total debits equal to total credits? This is the first control.

> **Audit tip.** Run it before anything else. If an entry does not balance, the ledger is wrong, and every other test is in doubt.

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT COUNT(*) AS entries_checked,
       SUM(CASE WHEN x.dr <> x.cr THEN 1 ELSE 0 END) AS unbalanced
FROM (SELECT e.journal_entry_id, SUM(l.debit) AS dr, SUM(l.credit) AS cr
      FROM dbo.gl_journal_entries AS e
      JOIN dbo.gl_journal_lines AS l ON l.journal_entry_id = e.journal_entry_id
      GROUP BY e.journal_entry_id) AS x;
```

**What it returns.** 34,377 entries checked and none unbalanced.

**After-hours postings.** The `qbd` sample has a created time on each journal entry, so you could test for postings between 7 PM and 7 AM. The test runs, but this sample is not usable for it. Its timestamps were created between 2003 and 2011, while the entries they describe are dated 2021 to 2023. The 18 night-time hits are opening balance entries created in 2007. Run the same test on a live system with current timestamps, and check the time zone first.

**Runs on:** `qbd`, SQL Server. This query shows the created-year gap that makes the after-hours test unreliable on this sample.

```sql
SELECT YEAR(j.TimeCreated) AS created_year,
       YEAR(j.Date) AS entry_year,
       COUNT(*) AS entries,
       SUM(CASE WHEN DATEPART(hour, j.TimeCreated) >= 19
                  OR DATEPART(hour, j.TimeCreated) < 7 THEN 1 ELSE 0 END) AS outside_7_to_7
FROM QuickBooks.JournalEntries AS j
GROUP BY YEAR(j.TimeCreated), YEAR(j.Date)
ORDER BY 1, 2;
```

> Exercise: Which posters entered journal entries dated on a Saturday or Sunday? Show the posted_by value and the number of weekend entries. Order does not matter.


## Duplicate payments and invoices

A duplicate payment is the same bill paid twice, or the same work billed twice under two numbers. Auditors test three ways: an exact match on vendor, amount and date; a near match within a short window; and a match on the invoice number.

> Diagram: Two bill cards with the same vendor, amount and date, joined by a line labeled as a duplicate candidate, then three steps to clear the candidate.

**Exact match, `qbd` bills.** This query finds bills from the same vendor with the same amount on the same date.

**Runs on:** `qbd`, SQL Server.

```sql
SELECT b.VendorName, b.Amount, b.Date,
       COUNT(*) AS bills,
       STRING_AGG(b.ReferenceNumber, ', ') AS reference_numbers
FROM QuickBooks.Bills AS b
GROUP BY b.VendorName, b.Amount, b.Date
HAVING COUNT(*) > 1;
```

**What it returns.** One pair: East Bayshore Auto Mall, $532.97, 2023-04-03, bill references 028 and 029. This is a candidate, not a finding. Read both bills. Two bills with consecutive numbers on the same day may be two real jobs, or one job entered twice.

**Near match, `nonprofit` bills.** This query finds the same vendor and amount within 30 days, which catches duplicates that were entered on different dates.

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT a.bill_id, b.bill_id AS other_bill_id, a.vendor_id, a.amount,
       a.bill_date, b.bill_date AS other_bill_date
FROM dbo.bills AS a
JOIN dbo.bills AS b
  ON b.vendor_id = a.vendor_id
 AND b.amount = a.amount
 AND b.bill_id > a.bill_id
 AND ABS(DATEDIFF(day, a.bill_date, b.bill_date)) <= 30;
```

**What it returns.** No rows. No vendor in this ledger was billed the same amount twice within 30 days.

**Invoice number, `bigbear` invoices.** Two invoices with the same vendor and invoice number are a classic duplicate.

**Runs on:** `bigbear`, SQL Server.

```sql
SELECT i.vendor_id, i.invoice_number,
       COUNT(*) AS invoices,
       SUM(i.amount) AS total
FROM dbo.invoices AS i
GROUP BY i.vendor_id, i.invoice_number
HAVING COUNT(*) > 1;
```

**What it returns.** No rows. Each vendor and invoice number pair appears once.

**What to do.** For each candidate, check four things: the vendor's remittance address, the payment date and check or ACH number, whether the vendor sent one invoice or two, and the approval on each. If the second payment exists, get a credit or refund in writing. Record the result on the exception list.

## Vendor master checks

The vendor master is the list of people you pay. A bad record there is how fake vendors and duplicate payments get in. Check four things.

**Blank tax IDs.** Each vendor paid in a year needs a tax ID for Form 1099 reporting. This query counts vendors with no tax ID, and vendors with no bills or checks at all.

**Runs on:** `qbd`, SQL Server.

```sql
WITH flags AS (
    SELECT v.ID, v.TaxIdentity,
           CASE WHEN EXISTS (SELECT 1 FROM QuickBooks.Bills AS b WHERE b.VendorId = v.ID)
                  OR EXISTS (SELECT 1 FROM QuickBooks.Checks AS c WHERE c.PayeeId = v.ID)
                THEN 1 ELSE 0 END AS has_activity
    FROM QuickBooks.Vendors AS v
)
SELECT COUNT(*) AS vendors,
       SUM(CASE WHEN TaxIdentity IS NULL OR LTRIM(RTRIM(TaxIdentity)) = '' THEN 1 ELSE 0 END) AS no_tax_id,
       SUM(CASE WHEN has_activity = 0 THEN 1 ELSE 0 END) AS no_bills_or_checks
FROM flags;
```

**What it returns.** Of 54 vendors, 43 have no tax ID and 9 have no bills or checks. A vendor with no activity is not a problem by itself, but a vendor with no activity and an active status is a candidate for cleanup. A vendor with no tax ID that you paid is a real reporting gap.

**Same name, different record.** The `bigbear` vendor list has several records for one company. This query finds vendor records that share a street address.

**Runs on:** `bigbear`, SQL Server.

```sql
SELECT a.address_line1,
       COUNT(*) AS vendor_records,
       STRING_AGG(a.vendor_name, ' | ') AS vendors
FROM dbo.vendors AS a
WHERE a.address_line1 IS NOT NULL
GROUP BY a.address_line1
HAVING COUNT(*) > 1
ORDER BY vendor_records DESC, a.address_line1;
```

**What it returns.** Ten addresses with two records each. In nine of them the two names are the same company written two ways, such as `Cedar Security` and `CEDAR SECURITY`, or a name with and without `Inc`. One pair, Valley Equipment and Pioneer Equipment, has different names at one address and needs a question to the owner. Duplicate vendor records are how a payment gets sent twice.

**Vendor and employee address match.** Compare the vendor address with the employee address. A shared address is a classic conflict-of-interest flag.

**Runs on:** `qbd`, SQL Server.

```sql
SELECT v.Name AS vendor_name, v.Line1 AS vendor_address,
       e.Name AS employee_name, e.Line1 AS employee_address
FROM QuickBooks.Vendors AS v
JOIN QuickBooks.Employees AS e
  ON UPPER(LTRIM(RTRIM(v.Line1))) = UPPER(LTRIM(RTRIM(e.Line1)));
```

**What it returns.** No rows. The three `qbd` employees have home street addresses, and none matches a vendor. The `bigbear` employees table has only a home city and postal code, and the `nonprofit` employees table has no address. This test can only run where both sides have street addresses. In this sample, only `qbd` qualifies.

## Benford's law

Benford's law says that in many natural sets of numbers, the first digit is 1 about 30 percent of the time and 9 about 5 percent. It is a screening test, not proof of fraud. Human-made amounts, fixed prices and rounded estimates break the pattern for innocent reasons. Use it to decide where to look.

> Diagram: Bar pairs for first digits 1 to 9. Benford's expected share is an outline bar and the actual share of nonprofit bill amounts is a filled bar. Digit 1 runs high and digit 2 runs low.

**Runs on:** `nonprofit`, SQL Server.

```sql
WITH digits AS (
    SELECT CAST(LEFT(CONVERT(varchar(40), CAST(b.amount AS decimal(18, 2))), 1) AS int) AS first_digit
    FROM dbo.bills AS b
    WHERE b.amount >= 1
)
SELECT v.digit AS first_digit,
       COUNT(d.first_digit) AS actual_count,
       ROUND(100.0 * COUNT(d.first_digit) / (SELECT COUNT(*) FROM digits), 1) AS actual_pct,
       ROUND(100 * LOG10(1 + 1.0 / v.digit), 1) AS benford_pct
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS v(digit)
LEFT JOIN digits AS d ON d.first_digit = v.digit
GROUP BY v.digit
ORDER BY v.digit;
```

**What it returns.** Across 13,871 bills, the first digit is 1 in 35.5 percent of bills (Benford expects 30.1) and 2 in 10.1 percent (Benford expects 17.6). Digits 3 to 9 are within about 2.5 points of the expected shares. Digits 1 and 2 are the outliers.

**What to do.** An excess of 1s with a shortage of 2s can come from a fixed price list, a vendor that bills in round dollar blocks, or a standing monthly charge. Sample bills whose amounts start with 1, beginning with the largest, and check whether the same vendor billed the same figure repeatedly. Do not conclude anything from the test alone.

> Exercise: Count the journal lines whose debit is a round amount: a whole multiple of 10,000 and at least 10,000. Return the count and the total of those debits.


## Cutoff around period end

Cutoff asks whether transactions landed in the right period. A bill for June services posted in July overstates July expenses and understates June. A real cutoff test compares the service date with the posting date. This sample has no service or receipt date, so the test below only checks the period tags.

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT COUNT(*) AS entries_outside_their_period
FROM dbo.gl_journal_entries AS e
JOIN dbo.fiscal_periods AS p ON p.fiscal_period_id = e.fiscal_period_id
WHERE e.entry_date < p.start_date OR e.entry_date > p.end_date;
```

**What it returns.** Zero. Every entry sits inside the fiscal period it was tagged to.

**What to do.** Zero is a good result, but it does not test cutoff. Ask for the receipt date on the largest bills in the last five days of each period. Compare that date with the period tag. That is the test auditors run, and it needs a column the sample does not have.

## Gaps in check numbers

A check sequence should have no holes, and no number should appear twice. A gap can mean a voided check that was never recorded, a check that was pulled from the sequence, or a check printed and never entered. Count the gaps, then account for every one.

> Diagram: A bar for the check number range 93 to 515. About 185 of the 423 numbers in that range are used, and 238 are missing.

**Runs on:** `qbd`, SQL Server.

```sql
WITH nums AS (
    SELECT CAST(c.ReferenceNumber AS int) AS check_no
    FROM QuickBooks.Checks AS c
    WHERE c.ReferenceNumber NOT LIKE '%[^0-9]%'
)
SELECT COUNT(*) AS numeric_checks,
       COUNT(DISTINCT check_no) AS distinct_numbers,
       MIN(check_no) AS first_number,
       MAX(check_no) AS last_number,
       MAX(check_no) - MIN(check_no) + 1 - COUNT(DISTINCT check_no) AS numbers_missing_in_range
FROM nums;
```

**What it returns.** 185 numeric checks, all distinct, running from 93 to 515. That leaves 238 numbers missing from the range. No number is used twice.

Before you call those 238 gaps, clear the non-numeric entries. The next query shows them.

**Runs on:** `qbd`, SQL Server.

```sql
SELECT c.ReferenceNumber, COUNT(*) AS checks, SUM(c.Amount) AS amount
FROM QuickBooks.Checks AS c
WHERE c.ReferenceNumber IS NULL OR c.ReferenceNumber LIKE '%[^0-9]%'
GROUP BY c.ReferenceNumber;
```

**What it returns.** The reference `DRAFT` on 22 checks, totaling $275.00. Each check is for $12.50, and the payee on most of them is a bad check charge. These are not check numbers. A check reference of `DRAFT` should never reach a printed check. Ask why the system allowed it, and make sure a bank charge like this is posted once.

**What to do.** Get the voided-check log and the bank's list of cleared checks. Each missing number should appear in one of them. Any number that appears in neither is an exception.

> Exercise: How many checks were written in each calendar year, and what was their total amount rounded to cents? Oldest year first. Skip checks with no date.


## AR and AP aging reasonableness

Aging is a reasonableness test. The total should agree to the ledger, and the buckets should agree to the due dates. The test below ages open bills against the as-of date in the sample, 2026-10-24.

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT CASE WHEN days_past_due <= 0 THEN '1. Not yet due'
            WHEN days_past_due <= 30 THEN '2. 1 to 30 days'
            WHEN days_past_due <= 60 THEN '3. 31 to 60 days'
            WHEN days_past_due <= 90 THEN '4. 61 to 90 days'
            ELSE '5. Over 90 days' END AS bucket,
       COUNT(*) AS bills,
       SUM(amount) AS amount
FROM (SELECT amount, DATEDIFF(day, due_date, '2026-10-24') AS days_past_due
      FROM dbo.bills
      WHERE status IN ('Open', 'Overdue')) AS x
GROUP BY CASE WHEN days_past_due <= 0 THEN '1. Not yet due'
              WHEN days_past_due <= 30 THEN '2. 1 to 30 days'
              WHEN days_past_due <= 60 THEN '3. 31 to 60 days'
              WHEN days_past_due <= 90 THEN '4. 61 to 90 days'
              ELSE '5. Over 90 days' END
ORDER BY 1;
```

**What it returns.** 226 bills not yet due ($258,948.23), 30 bills one to 30 days late ($24,599.24) and 2 bills 31 to 60 days late ($1,782.07). Total open: $285,329.54. The 32 past-due bills match the 32 bills flagged `Overdue` in the status column. The aging and the status agree.

For receivables, the same pattern works on the `cpafirm` invoice balances. The [SQL for accountants](/analyze/sql-for-accountants) guide builds that aging step by step.

## What to do with exceptions

Every test produces a list of hits. Most hits are explained by a normal process. Some are not. The work is to sort them, sample the ones you cannot explain, and follow up in writing.

> Diagram: Four steps in a row: define the population, filter for the risky items, sample and read the support, then conclude and follow up.

Use these rules to decide the next step:

- **Explain the population first.** A weekend bill payment may be a system date. Confirm that before you read a single entry.
- **Sample the unexplained hits.** Read the support for a sample. Pick the largest hits, the newest, and any that repeat a pattern. If the sample shows a problem, widen it. If it does not, document the sample and the result.
- **Record each hit.** Keep the test name, the key (bill ID, check number or entry number), the amount, the owner and the result. Save the query so the test can be rerun.
- **Follow up with the owner, in writing.** Ask the person who posted or approved the item. Their answer is evidence, but it is not the end. Check it against the support.
- **Escalate when the answer does not match the support.** A missing invoice, a second payment, or an approval that cannot be found goes to the person who owns the control.

| Test | Typical hit | First step | Escalate when |
|---|---|---|---|
| Weekend and holiday | Bill dated on a holiday | Ask how the date is set | A person typed a date that has no vendor invoice |
| Round amounts | Round expense | Read the invoice | The amount has no invoice or matches no order |
| Threshold | A band just under the limit | Compare with the real limit | Several entries from one person, one day |
| Duplicate bill | Same vendor, amount and date | Check both bills and payments | Two payments went out |
| Vendor tax ID | Paid vendor with no tax ID | Request a W-9 | No W-9 for a vendor over the reporting limit |
| Check gap | Missing number | Check the void log and bank list | A missing check has no void record |
| Benford | Excess of 1s | Sample the top of the range | A repeated amount with no contract |

## What the sample data cannot test

These tests need data the samples do not have. Each one is worth running on a real system.

- **After-hours postings** need current timestamps and a time zone. The `qbd` timestamps are from 2003 to 2011.
- **Entry approval** needs an approval table, a signing authority list, and the approver's name on each entry. Nothing in the samples records who approved an entry.
- **Who created or changed a record** needs an audit log. The `nonprofit` ledger has `posted_by`, but the `bigbear` and `qbd` ledgers do not.
- **Real cutoff** needs service dates and receipt dates on bills.
- **Employee and vendor address conflicts** need street addresses on the employee record. Only `qbd` has them, and none matched.
- **Approval thresholds** need your real limits, not a guess.

If your system has these fields, use them. The method stays the same: population, test, hits, sample, follow-up.
