Start here
Lessons and reference
Look things up
Start here
SQL
Power Query
Data modeling
Power BI and DAX
Finance playbooks
Run queries
Build and check
A small-business CPA firm: clients, engagements, tax returns, time entries, invoices, payments, retainers, a balanced general ledger and document tracking. About 1,000 clients, six years of history, a client map and a July-June fiscal calendar.
CPA firm practice management (cpafirm): Clients, engagements, tax returns, time, billing, trust and a general ledger for a small-business CPA firm, with realization, WIP and AR aging queries.
Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.
Server learnsql.cpetoday.com,1433, database cpafirm, in Excel or Power BI (Import mode) or
DBeaver. These tables are in the dbo schema, so once you have picked the database you can use plain names such as client_relationships.
Download the Excel connection (.odc)
How to use it: download the file and open it. Excel opens it as a connection to cpafirm
and asks for the SQL Server username and password from your CPE Today course.
The file holds no password. The first table is selected by default, and you can pick others from the Data window.
Setup details for other tools are on Connect.
Click a table to preview it in the Playground.
What each column means, its type and keys, and for codes and statuses the values seen in this copy. 18 of 34 tables are described so far. Read it as Markdown.
Click a table in the diagram above to jump to it.
The firm's clients: the business or person the firm serves, with office, industry and entity type.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
client_id |
int | PK | Unique client ID. | |
client_number |
varchar(10) | Client number used on files and invoices. | ||
client_name |
varchar(120) | Legal name of the client. | ||
trade_name |
varchar(120) | Not described yet | ||
entity_type_id |
int | FK to entity_types | Legal entity type (entity_types), such as S corporation or partnership. | 1 380 2 160 3 160 4 120 5 60 6 60 7 60 |
industry_id |
int | FK to industries | Industry (industries). | |
referral_source_id |
int | FK to referral_sources | Not described yet | |
office_id |
int | FK to offices | Office that serves the client (offices). | 1 568 2 432 |
partner_id |
int | FK to staff | Partner responsible for the client (staff). | 2 484 3 265 1 251 |
manager_id |
int | FK to staff | Not described yet | |
tax_id_last4 |
char(4) | Not described yet | ||
filing_status |
varchar(30) | Not described yet | ||
fiscal_year_end_month |
tinyint | Month number in which the client's fiscal year ends. | 12 964 6 30 9 6 | |
status |
varchar(20) | Client status (see the values). | Active 900 Inactive 60 Prospect 40 | |
onboarded_date |
date | Date the client was onboarded. | ||
terminated_date |
date | Date the relationship ended, if it did. | ||
annual_revenue |
decimal | Reported annual revenue, when known. | ||
employee_count |
int | Not described yet | ||
billing_terms_days |
smallint | Not described yet | ||
address_line1 |
varchar(100) | Not described yet | ||
city |
varchar(60) | Not described yet | ||
state |
char(2) | Not described yet | ||
postal_code |
varchar(10) | Not described yet | ||
phone |
varchar(20) | Not described yet | ||
email |
varchar(120) | Not described yet | ||
county |
varchar(60) | Not described yet | ||
country |
char(2) | Not described yet | ||
latitude |
decimal | Not described yet | ||
longitude |
decimal | Not described yet | ||
geocode_quality |
varchar(20) | Not described yet | ||
full_address |
varchar(220) | Not described yet |
People at a client, such as owners and bookkeepers.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
contact_id |
int | PK | Unique contact ID. | |
client_id |
int | FK to clients | Client (clients). | |
first_name |
varchar(40) | First name. | ||
last_name |
varchar(40) | Last name. | ||
job_title |
varchar(60) | Job title. | ||
email |
varchar(120) | Not described yet | ||
phone |
varchar(20) | Not described yet | ||
is_primary |
bit | 1 for the main contact. | True 1000 False 529 | |
is_billing_contact |
bit | 1 if the contact receives invoices. | True 1154 False 375 |
Documents the firm has asked a client to send, and whether they came back.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
document_request_id |
int | PK | Unique request ID. | |
client_id |
int | FK to clients | Client (clients). | |
engagement_id |
int | FK to engagements | Engagement (engagements). | |
requested_by |
int | FK to staff | Not described yet | |
document_name |
varchar(100) | Document requested. | ||
requested_date |
date | Date requested. | ||
due_date |
date | Date needed by. | ||
received_date |
date | Date received, if it arrived. | ||
status |
varchar(20) | Request status (see the values). | Received 20192 Not Applicable 608 Outstanding 83 | |
reminder_count |
tinyint | Reminders sent. | 0 9650 1 9558 2 861 3 783 4 18 5 13 |
Work the firm agrees to do for a client in a tax year or period, such as a tax return or an audit. Billing and time roll up to engagements.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
engagement_id |
int | PK | Unique engagement ID. | |
client_id |
int | FK to clients | Client (clients). | |
service_id |
int | FK to services | Service being provided (services). | |
tax_year |
smallint | Tax year covered. | (blank) 2931 2025 924 2024 861 2023 768 2022 722 2021 667 2020 621 | |
period_start |
date | Not described yet | ||
period_end |
date | Not described yet | ||
fee_type |
varchar(10) | How the fee is set: fixed or hourly, for example. | Fixed 4583 Monthly 2155 Hourly 756 | |
fee_amount |
decimal | Agreed fee. | ||
status |
varchar(20) | Engagement status (see the values). | Complete 6910 In Progress 489 Cancelled 64 Awaiting Client 15 In Review 15 On Hold 1 | |
open_date |
date | Date the engagement opened. | ||
due_date |
date | Date the work is due. | ||
completed_date |
date | Date the work was completed. | ||
partner_id |
int | FK to staff | Responsible partner (staff). | 2 3535 3 2014 1 1945 |
manager_id |
int | FK to staff | Manager on the engagement (staff). | 5 3581 4 2196 6 1717 |
fiscal_year |
smallint | Not described yet | ||
fiscal_quarter |
tinyint | Not described yet | ||
fiscal_period |
tinyint | Not described yet |
One row per calendar day with its fiscal year, quarter and period, and tax-season flags.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
fiscal_calendar_id |
int | PK | Not described yet | |
calendar_date |
date | The calendar day. | ||
fiscal_year |
smallint | Fiscal year. | 2024 366 2021 365 2022 365 2023 365 2025 365 2026 365 2027 365 | |
fiscal_year_label |
varchar(10) | Not described yet | ||
fiscal_quarter |
tinyint | Fiscal quarter. | 1 644 2 644 4 637 3 631 | |
fiscal_period |
tinyint | Fiscal period (month) number. | 1 217 2 217 4 217 6 217 7 217 9 217 11 217 3 210 5 210 10 210 12 210 8 197 | |
fiscal_period_name |
varchar(12) | Not described yet | ||
fiscal_period_start |
date | Not described yet | ||
fiscal_period_end |
date | Not described yet | ||
fiscal_week |
tinyint | Not described yet | ||
fiscal_day_of_year |
smallint | Not described yet | ||
fiscal_year_start |
date | Not described yet | ||
fiscal_year_end |
date | Not described yet | ||
is_period_end |
bit | 1 on the last day of the fiscal period. | False 2472 True 84 | |
is_year_end |
bit | Not described yet | ||
tax_season |
varchar(20) | Tax season this date falls in. | Extension season 1281 Busy season 638 Off season 637 | |
tax_deadline |
varchar(100) | Not described yet |
The firm's chart of accounts.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
gl_account_id |
int | PK | Unique account ID. | |
account_number |
varchar(10) | Account number. | ||
account_name |
varchar(80) | Account name. | ||
account_type |
varchar(20) | Account type (see the values). | Expense 11 Revenue 8 Asset 3 Equity 1 Liability 1 | |
parent_account_id |
int | FK to gl_accounts | Parent account, for sub-accounts. | (blank) 8 6 7 17 7 14 2 |
Journal entries posted by the firm's books, one row per entry.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
journal_entry_id |
int | PK | Unique entry ID. | |
entry_date |
date | Posting date. | ||
source_type |
varchar(20) | Where the entry came from (see the values). | Invoice 21282 Payment 20222 Adjustment 850 Trust 125 Operating 69 Opening 1 | |
source_id |
int | ID of the source record. | ||
memo |
varchar(200) | Entry memo. | ||
fiscal_year |
smallint | Not described yet | ||
fiscal_quarter |
tinyint | Not described yet | ||
fiscal_period |
tinyint | Not described yet |
Debit and credit lines of each journal entry. A client can be tagged on a line.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
journal_line_id |
int | PK | Unique line ID. | |
journal_entry_id |
int | FK to gl_journal_entries | Entry (gl_journal_entries). | |
gl_account_id |
int | FK to gl_accounts | Account (gl_accounts). | |
client_id |
int | FK to clients | Client tagged on the line, if any (clients). | |
debit |
decimal | Debit amount. | ||
credit |
decimal | Credit amount. |
Write-downs, write-offs and other changes to an invoice, approved by a partner.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
adjustment_id |
int | PK | Unique adjustment ID. | |
invoice_id |
int | FK to invoices | Invoice adjusted (invoices). | |
approved_by |
int | FK to staff | Staff member who approved it. | 2 335 1 328 3 187 |
adjustment_date |
date | Date of the adjustment. | ||
adjustment_type |
varchar(20) | Kind of adjustment (see the values). | Bad Debt 294 Discount 203 Courtesy Credit 187 Write-off 166 | |
amount |
decimal | Amount of the adjustment. | ||
reason |
varchar(200) | Why it was made. | Uncollectible after repeated follow-up 294 Small balance written off 166 Fee concession after scope change 107 Billing correction 98 Client relationship adjustment 95 Early-pay discount 90 |
Lines on each invoice: the service, quantity, rate and amount.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
invoice_line_id |
int | PK | Unique line ID. | |
invoice_id |
int | FK to invoices | Invoice (invoices). | |
engagement_id |
int | FK to engagements | Engagement the line bills (engagements). | |
line_number |
smallint | Line order on the invoice. | 1 21282 2 6174 3 880 4 52 5 1 | |
description |
varchar(200) | What the line bills for. | ||
quantity |
decimal | Hours or units billed. | ||
unit_price |
decimal | Rate per unit. | ||
amount |
decimal | Line amount. |
Invoices the firm sends to clients for its work.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
invoice_id |
int | PK | Unique invoice ID. | |
invoice_number |
varchar(20) | Invoice number shown to the client. | ||
client_id |
int | FK to clients | Client billed (clients). | |
billing_contact_id |
int | FK to contacts | Not described yet | |
issued_by |
int | FK to staff | Not described yet | |
issue_date |
date | Date the invoice was issued. | ||
due_date |
date | Date payment is due. | ||
status |
varchar(20) | Invoice status (see the values). | Paid 20330 Sent 393 Written Off 294 Partial 136 Overdue 129 | |
total_amount |
decimal | Invoice total. | ||
fiscal_year |
smallint | Fiscal year of the invoice date. | 2026 4435 2025 4056 2024 3640 2023 3350 2022 3031 2027 1550 2021 1220 | |
fiscal_quarter |
tinyint | Fiscal quarter of the invoice date. | 4 6072 1 5255 2 5078 3 4877 | |
fiscal_period |
tinyint | Fiscal month (period) of the invoice date. | 10 2863 4 2256 3 1972 9 1931 2 1679 11 1618 1 1604 12 1591 8 1560 5 1423 6 1399 7 1386 |
The firm's offices.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
office_id |
int | PK | Unique office ID. | 1 1 2 1 |
office_name |
varchar(80) | Office name. | Main Office - Bellevue 1 Satellite Office - Portland 1 | |
city |
varchar(60) | City. | Bellevue 1 Portland 1 | |
state |
char(2) | State code. | OR 1 WA 1 | |
phone |
varchar(20) | Phone number. | (425) 555-0100 1 (503) 555-0140 1 | |
address_line1 |
varchar(100) | Not described yet | ||
postal_code |
varchar(10) | Not described yet | ||
county |
varchar(60) | Not described yet | ||
country |
char(2) | Not described yet | ||
latitude |
decimal | Not described yet | ||
longitude |
decimal | Not described yet | ||
geocode_quality |
varchar(20) | Not described yet | ||
full_address |
varchar(220) | Not described yet |
Applies each payment to one or more invoices.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
payment_application_id |
int | PK | Unique row ID. | |
payment_id |
int | FK to payments | Payment applied (payments). | |
invoice_id |
int | FK to invoices | Invoice paid (invoices). | |
amount_applied |
decimal | Amount of the payment applied to the invoice. |
Payments clients make, one row per payment received.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
payment_id |
int | PK | Unique payment ID. | |
client_id |
int | FK to clients | Client who paid (clients). | |
received_by |
int | FK to staff | Not described yet | |
payment_date |
date | Date received. | ||
amount |
decimal | Amount received. | ||
payment_method |
varchar(20) | How the client paid (see the values). | ACH 8985 Check 5504 Credit Card 5282 Trust Application 442 Wire 9 | |
reference_number |
varchar(30) | Check number or reference. | ||
fiscal_year |
smallint | Not described yet | ||
fiscal_quarter |
tinyint | Not described yet | ||
fiscal_period |
tinyint | Not described yet |
Firm staff: partners, managers, seniors, staff and admins, with rate and office.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
staff_id |
int | PK | Unique staff ID. | |
first_name |
varchar(40) | First name. | ||
last_name |
varchar(40) | Last name. | ||
email |
varchar(100) | Not described yet | ||
role_id |
int | FK to staff_roles | Role (staff_roles). | 4 5 1 3 2 3 3 3 5 2 6 1 |
office_id |
int | FK to offices | Home office (offices). | 1 11 2 6 |
manager_id |
int | FK to staff | Manager (staff). | (blank) 3 5 3 1 2 7 2 8 2 2 1 3 1 4 1 6 1 9 1 |
hire_date |
date | Start date. | ||
termination_date |
date | Leave date, if any. | (blank) 16 2024-06-30 1 | |
is_cpa |
bit | 1 if the person is a licensed CPA. | False 9 True 8 | |
hourly_cost |
decimal | Internal cost per hour. |
To-do items on engagements.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
task_id |
int | PK | Unique task ID. | |
engagement_id |
int | FK to engagements | Engagement (engagements). | |
assigned_to |
int | FK to staff | Staff member (staff). | |
task_name |
varchar(100) | What has to be done. | Manager review 4499 Partner review 4499 Collect client documents 4271 File and deliver to client 4271 Prepare return 4271 Fieldwork 228 Issue report 228 Planning and risk assessment 228 | |
priority |
varchar(10) | Priority (see the values). | Normal 16721 Low 5719 High 55 | |
status |
varchar(20) | Task status (see the values). | Complete 22384 Open 60 In Progress 51 | |
due_date |
date | Due date. | ||
completed_date |
date | Date finished. |
Tax returns prepared for an engagement, one row per return and jurisdiction.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
tax_return_id |
int | PK | Unique return ID. | |
engagement_id |
int | FK to engagements | Engagement (engagements). | |
client_id |
int | FK to clients | Client (clients). | |
tax_year |
smallint | Tax year. | 2025 1333 2024 1248 2023 1108 2022 1059 2021 972 2020 915 | |
jurisdiction |
varchar(10) | Federal or state jurisdiction. | Federal 4271 OR 1037 CA 818 AZ 261 ID 248 | |
form_type |
varchar(20) | Form filed, such as 1120S or 1065. | ||
due_date |
date | Due date. | ||
is_extended |
bit | 1 if the return was extended. | False 4055 True 2580 | |
extended_due_date |
date | Due date after the extension. | ||
filed_date |
date | Date filed. | ||
filing_method |
varchar(10) | Not described yet | ||
status |
varchar(20) | Return status (see the values). | Accepted 6005 Filed 556 In Review 49 Preparing 25 | |
preparer_id |
int | FK to staff | Not described yet | |
reviewer_id |
int | FK to staff | Not described yet | |
total_income |
decimal | Total income on the return. | ||
refund_or_balance_due |
decimal | Refund (negative) or balance due (positive). |
Hours staff record against engagements. Billable hours are the ones invoiced.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
time_entry_id |
int | PK | Unique time entry ID. | |
engagement_id |
int | FK to engagements | Engagement the time is for (engagements). | |
staff_id |
int | FK to staff | Person who worked (staff). | |
invoice_id |
int | FK to invoices | Invoice the time was billed on, if billed (invoices). | |
work_date |
date | Date the work was done. | ||
hours |
decimal | Hours worked. | ||
is_billable |
bit | 1 when the time can be billed to the client. | True 68295 False 1749 | |
bill_rate |
decimal | Rate charged per hour. | ||
cost_rate |
decimal | Internal cost per hour. | ||
description |
varchar(200) | What was done. | ||
fiscal_year |
smallint | Not described yet | ||
fiscal_quarter |
tinyint | Not described yet | ||
fiscal_period |
tinyint | Not described yet |