<!-- https://learnsql.cpetoday.com/analyze/field-operations -->
# Utility field operations (fieldops)

`fieldops` is the operating data of a small water and electric utility: the equipment it maintains, the work orders crews complete, the vendors and purchase orders behind the parts, the customers it bills each month, and the outages it has to explain. It is built for **maintenance, procurement, billing and reliability reports** in Excel, Power BI and SQL, and every relationship is a declared key, so a modeling tool finds them on its own.

> **These are invented records.** The people, vendors, customers and dollar amounts are made up. The shape of the work, such as emergency jobs finishing in days and low-priority jobs in weeks, is realistic on purpose.

## Connect to it

- **SQL Server:** server `learnsql.cpetoday.com,1433`, database `fieldops`, tables in the `dbo` schema. See [Connect Excel and Power BI](/connect).
- **In the browser:** open [the database page](/databases/fieldops) for the Playground, the SQL editor and the schema explorer.
- It exists on both SQL Server and MariaDB with identical rows (about 161,000 in all).

## Tables at a glance

| Area | Tables | What they hold |
|---|---|---|
| Organization | `Regions`, `Departments`, `Employees` | 6 regions, 10 departments (each name appears once), 400 employees with job title and hourly rate |
| Equipment | `Assets` | 1,500 transformers, poles, meters, valves, pumps, hydrants, pipe segments and breakers, with install date, replacement cost and condition |
| Maintenance | `WorkOrders`, `WorkOrderMaterials`, `Materials` | 25,000 work orders (type, priority, status, days to complete, labor hours and cost), 50,000 material lines, 300 materials |
| Purchasing | `Vendors`, `PurchaseOrders`, `VendorInvoices` | 150 vendors with payment terms, 7,000 purchase orders, 9,000 invoices with days to pay and a disputed flag |
| Customers | `CustomerAccounts`, `MonthlyBills` | 5,000 accounts in four rate classes and twelve monthly bills each for 2025 |
| Reliability | `ServiceInterruptions` | 3,000 outages with duration, customers affected and cause |

Views that save the joins: `vw_WorkOrderDetail`, `vw_WorkOrderMaterialCost`, `vw_BillingDetail`, `vw_VendorSpend` and `vw_Outages`.

## What the data does

- A work order is in the **same region as its asset**, and its labor cost is the hours times the assigned employee's hourly rate.
- **Urgent work finishes sooner:** emergency orders average about two days, low-priority orders more than a month. Open, in-progress and cancelled orders have no completion time (a blank, on purpose).
- **Worn assets cost more:** poor and critical assets take more labor hours per order.
- **Invoices follow orders:** each invoice belongs to an approved, received or closed purchase order, is dated after it, and is within 3% of its amount. Days to pay track the vendor's terms (Net 15 to Net 60), and disputed invoices take longer.
- **Usage is seasonal:** monthly bills peak in summer, and a commercial or industrial account uses far more than a residential one.

## Questions to try

```sql
-- Which asset types cost the most to maintain?
SELECT AssetType, COUNT(*) AS orders, SUM(LaborCost) AS labor
FROM vw_WorkOrderDetail WHERE Status = 'Completed'
GROUP BY AssetType ORDER BY labor DESC;

-- How long do work orders take, by priority?
SELECT Priority, AVG(DaysToComplete) AS avg_days
FROM vw_WorkOrderDetail WHERE Status = 'Completed'
GROUP BY Priority;

-- Which vendor categories get paid slowest, and how many invoices are disputed?
SELECT Category, AVG(DaysToPay) AS avg_days_to_pay, SUM(CASE WHEN Disputed = 1 THEN 1 ELSE 0 END) AS disputed
FROM vw_VendorSpend WHERE InvoiceID IS NOT NULL
GROUP BY Category ORDER BY avg_days_to_pay DESC;

-- Customer-minutes of outage by cause
SELECT Cause, SUM(CustomerMinutes) AS customer_minutes
FROM vw_Outages GROUP BY Cause ORDER BY customer_minutes DESC;
```

## In Power BI and Excel

- Load the tables (or the views) and let the relationships come in from the declared keys. `Regions`, `Departments` and `Employees` are shared dimensions for work orders, purchasing and accounts.
- `WorkOrders` and `MonthlyBills` are the large facts. A date table related to `OpenedDate`, `PODate`, `InvoiceDate`, `BillDate` and `StartDate` (one active relationship, the others inactive) shows the **role-playing date** pattern.
- Good measures: Work Orders, Completed %, Average Days to Complete, Labor Cost per Order, Backlog (open orders), Spend, Days Payable, Disputed %, Billed Revenue, Usage per Account, Customer-Minutes of Outage (the basis of reliability indexes such as SAIDI).
- `WorkOrders.DaysToComplete` is blank for unfinished work, so averages ignore it; compare with a count of open orders.
