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 water and electric utility's field operations in six regions: assets and their condition, 25,000 work orders with labor and materials, vendors with purchase orders and invoices, customer accounts with a year of monthly bills, and service interruptions. Lookup tables are unique and every relationship is a declared key.
Utility field operations (fieldops): A water and electric utility: assets, work orders with labor and materials, vendors with purchase orders and invoices, customer billing and service interruptions, all keyed.
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 fieldops, 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 Assets.
Download the Excel connection (.odc)
How to use it: download the file and open it. Excel opens it as a connection to fieldops
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. 13 of 13 tables are described so far. Read it as Markdown.
Click a table in the diagram above to jump to it.
Field assets such as poles, transformers and meters, with age and condition.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
AssetID |
int | PK | Unique asset ID. | |
AssetType |
nvarchar(12) | Kind of asset (see the values). | Utility Pole 327 Meter 293 Transformer 221 Hydrant 201 Valve 166 Pipe Segment 154 Pump 71 Breaker 67 | |
RegionID |
int | FK to Regions | Region (Regions). | 2 269 3 258 4 251 5 251 6 249 1 222 |
Manufacturer |
nvarchar(80) | Manufacturer. | ||
InstallDate |
date | Date installed. | ||
ReplacementCost |
decimal | Estimated cost to replace. | ||
Condition |
nvarchar(8) | Condition rating (see the values). | Good 786 Fair 434 Poor 216 Critical 64 |
Customer accounts that receive the utility bills.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
AccountID |
int | PK | Unique account ID. | |
CustomerName |
nvarchar(60) | Account holder. | ||
RegionID |
int | FK to Regions | Region (Regions). | 1 865 4 858 2 850 5 813 6 813 3 801 |
RateClass |
nvarchar(11) | Rate class the account is billed under. | Residential 4027 Commercial 685 Municipal 182 Industrial 106 | |
ConnectDate |
date | Date service was connected. | ||
Email |
nvarchar(120) | Email. | ||
Phone |
nvarchar(20) | Phone. |
Departments and their cost centers.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
DepartmentID |
int | PK | Unique department ID. | 1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 1 10 1 |
DepartmentName |
nvarchar(21) | Department name. | Customer Service 1 Electric Operations 1 Engineering 1 Finance 1 Fleet and Facilities 1 IT Services 1 Meter Services 1 Procurement 1 Safety and Compliance 1 Water Operations 1 | |
CostCenter |
int | Cost center code used for reporting. | 1000 1 1010 1 1020 1 1030 1 1040 1 1050 1 1060 1 1070 1 1080 1 1090 1 |
Field and office employees.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
EmployeeID |
int | PK | Unique employee ID. | |
FullName |
nvarchar(60) | Name. | ||
Email |
nvarchar(120) | Not described yet | ||
DepartmentID |
int | FK to Departments | Department (Departments). | 9 51 3 50 7 44 4 41 2 39 6 39 8 38 10 37 5 35 1 26 |
RegionID |
int | FK to Regions | Home region (Regions). | 5 76 4 75 1 65 6 65 3 63 2 56 |
JobTitle |
nvarchar(16) | Job title. | Field Technician 88 Lineworker 75 Meter Reader 61 Analyst 39 Clerk 39 Engineer 36 Supervisor 25 Dispatcher 19 Planner 18 | |
HireDate |
date | Start date. | ||
HourlyRate |
decimal | Pay per hour. |
Material catalog: what can be used on a job, and its unit cost.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
MaterialID |
int | PK | Unique material ID. | |
Category |
nvarchar(14) | Material category. | Hardware 54 Safety 51 Chemicals 49 Fittings 41 Wire and Cable 37 Meters 35 Pipe 33 | |
Description |
nvarchar(400) | What the material is. | ||
UnitOfMeasure |
nvarchar(4) | Unit it is counted in. | box 68 each 60 ft 60 lb 59 gal 53 | |
UnitCost |
decimal | Cost per unit. |
Monthly bills sent to customer accounts for usage.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
BillID |
int | PK | Unique bill ID. | |
AccountID |
int | FK to CustomerAccounts | Account billed (CustomerAccounts). | |
BillDate |
date | Bill date. | 2025-01-01 5000 2025-02-01 5000 2025-03-01 5000 2025-04-01 5000 2025-05-01 5000 2025-06-01 5000 2025-07-01 5000 2025-08-01 5000 2025-09-01 5000 2025-10-01 5000 2025-11-01 5000 2025-12-01 5000 | |
UsageUnits |
int | Units of usage billed. | ||
AmountBilled |
decimal | Amount billed. | ||
DaysToPay |
int | Days the customer took to pay. | ||
PaymentMethod |
nvarchar(8) | How the customer paid (see the values). | Auto-Pay 22761 Online 19329 Check 8307 Cash 4830 Phone 4773 |
Purchase orders placed with vendors, with approval and status.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
POID |
int | PK | Unique PO ID. | |
VendorID |
int | FK to Vendors | Vendor (Vendors). | |
DepartmentID |
int | FK to Departments | Department that placed the order. | 1 737 9 718 3 711 10 706 6 703 2 694 5 694 4 690 8 677 7 670 |
RegionID |
int | FK to Regions | Region. | 3 1209 5 1192 6 1181 2 1160 4 1133 1 1125 |
ApprovedBy |
int | FK to Employees | Employee who approved the PO. | |
PODate |
date | Date of the PO. | ||
Amount |
decimal | Order amount. | ||
Status |
nvarchar(9) | PO status (see the values). | Closed 4184 Received 1431 Approved 796 Cancelled 370 Draft 219 |
Service regions.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
RegionID |
int | PK | Unique region ID. | 1 1 2 1 3 1 4 1 5 1 6 1 |
RegionName |
nvarchar(12) | Region name. | Central 1 Coastal 1 Desert 1 Mountain 1 North Valley 1 South Bay 1 | |
State |
nvarchar(2) | State the region is in. |
Outages: when a region lost service, how long it lasted and how many customers were affected.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
EventID |
int | PK | Unique event ID. | |
RegionID |
int | FK to Regions | Region affected (Regions). | 2 564 5 523 3 499 6 499 4 498 1 417 |
AssetID |
int | FK to Assets | Asset that failed (Assets). | |
StartDate |
date | When the outage began. | ||
DurationMinutes |
int | How long it lasted. | ||
CustomersAffected |
int | Customers without service. | ||
Cause |
nvarchar(19) | Cause of the outage (see the values). | Equipment Failure 942 Weather 709 Planned Maintenance 519 Vegetation 286 Animal Contact 236 Vehicle Accident 187 Unknown 121 |
Invoices from vendors against purchase orders.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
InvoiceID |
int | PK | Unique invoice ID. | |
POID |
int | FK to PurchaseOrders | Purchase order (PurchaseOrders). | |
InvoiceDate |
date | Invoice date. | ||
Amount |
decimal | Invoice amount. | ||
DaysToPay |
int | Days taken to pay. | ||
Disputed |
bit | 1 if the invoice is disputed. | False 8556 True 444 |
Vendors that supply materials and services.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
VendorID |
int | PK | Unique vendor ID. | |
VendorName |
nvarchar(80) | Vendor name. | ||
Category |
nvarchar(19) | Category of goods or services. | Contract Labor 24 Safety Equipment 21 Tools 21 Pipe and Fittings 20 Vehicles and Fleet 19 IT and Software 17 Chemicals 15 Electrical Supplies 13 | |
PaymentTerms |
nvarchar(6) | Payment terms. | Net 30 79 Net 45 39 Net 60 20 Net 15 12 | |
State |
nvarchar(2) | State. | CA 92 NV 20 TX 17 OR 12 AZ 9 | |
Phone |
nvarchar(20) | Phone. |
Materials used on each work order.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
LineID |
int | PK | Unique line ID. | |
WorkOrderID |
int | FK to WorkOrders | Work order (WorkOrders). | |
MaterialID |
int | FK to Materials | Material used (Materials). | |
Quantity |
int | Quantity used. |
Field work orders: the job, the asset it's on, who it's assigned to, priority and labor.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
WorkOrderID |
int | PK | Unique work order ID. | |
AssetID |
int | FK to Assets | Asset worked on (Assets). | |
RegionID |
int | FK to Regions | Region (Regions). | 3 4437 2 4402 5 4207 4 4199 6 4158 1 3597 |
DepartmentID |
int | FK to Departments | Department that owns the job (Departments). | 6 2588 1 2545 5 2530 8 2516 10 2508 2 2497 3 2478 7 2475 4 2442 9 2421 |
AssignedTo |
int | FK to Employees | Employee assigned (Employees.EmployeeID). | |
WorkType |
nvarchar(22) | Type of work (see the values). | Repair 6984 Preventive Maintenance 6315 Inspection 5468 Replacement 2440 Emergency 2044 Installation 1749 | |
Priority |
nvarchar(9) | Priority of the job (see the values). | Medium 11231 Low 7470 High 5057 Emergency 1242 | |
OpenedDate |
date | Date the order was opened. | ||
DaysToComplete |
int | Days taken from opening to completion. | ||
Status |
nvarchar(11) | Order status (see the values). | Completed 19442 Open 2498 In Progress 2037 Cancelled 1023 | |
LaborHours |
decimal | Hours of labor. | ||
LaborCost |
decimal | Labor cost. | ||
Notes |
nvarchar(400) | Free-text notes from the crew. |