Downloads
Work orders, assets, purchasing and billing

Field Operations (utility)

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.

fieldopsSQL ServerMariaDB 13 tables
WorkOrdersAssetsEmployeesMonthlyBillsPurchaseOrders

Read the guide

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.

Good for

Work order and maintenance analysisProcurement and invoice agingBilling and seasonal usageReliability measures such as customer-minutes

Questions to try

  • Which asset types cost the most to maintain?
  • How long do work orders take by priority?
  • Which vendors get paid slowest?
  • How does usage change by month and rate class?

Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.

Connect to 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.

Tables (13)

Click a table to preview it in the Playground.

Data dictionary

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.

Assets one row per asset 7 columns

Field assets such as poles, transformers and meters, with age and condition.

ColumnTypeKeyMeaningValues 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
CustomerAccounts one row per account 7 columns

Customer accounts that receive the utility bills.

ColumnTypeKeyMeaningValues 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 one row per department 3 columns

Departments and their cost centers.

ColumnTypeKeyMeaningValues 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
Employees one row per employee 8 columns

Field and office employees.

ColumnTypeKeyMeaningValues 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.
Materials one row per material 5 columns

Material catalog: what can be used on a job, and its unit cost.

ColumnTypeKeyMeaningValues 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.
MonthlyBills one row per bill 7 columns

Monthly bills sent to customer accounts for usage.

ColumnTypeKeyMeaningValues 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
PurchaseOrders one row per purchase order 8 columns

Purchase orders placed with vendors, with approval and status.

ColumnTypeKeyMeaningValues 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
Regions one row per region 3 columns

Service regions.

ColumnTypeKeyMeaningValues 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.
ServiceInterruptions one row per interruption 7 columns

Outages: when a region lost service, how long it lasted and how many customers were affected.

ColumnTypeKeyMeaningValues 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
VendorInvoices one row per vendor invoice 6 columns

Invoices from vendors against purchase orders.

ColumnTypeKeyMeaningValues 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 one row per vendor 6 columns

Vendors that supply materials and services.

ColumnTypeKeyMeaningValues 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.
WorkOrderMaterials one row per material per work order 4 columns

Materials used on each work order.

ColumnTypeKeyMeaningValues 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.
WorkOrders one row per work order 13 columns

Field work orders: the job, the asset it's on, who it's assigned to, priority and labor.

ColumnTypeKeyMeaningValues 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.

← All databases