Analyze · Case study 1 · SQL
SQL exception queries
Most review work is looking for the few rows that are wrong among thousands that are right. These queries run against the sample dataset, live in your browser, and each one answers a question a JIB supervisor asks every month. Pick one, read it, change it, run it.
Loading…
Loading SQLite in your browser…
What the queries find
The sample data has errors planted in it on purpose, the same kinds that turn up in real review. Here's what each query catches, and what a reviewer does next.
| Query | Finds | Next step |
|---|---|---|
| Billed, not paid | Ten October owner lines with costs but no revenue. Miller 3H and Harlan 1 are expected (drilling, and a P&A). Smith 3H is shut in. Smith 2H's Partner B is in revenue suspense. Jones Unit 1 is the surprise. | Clear the expected ones; send Partner B to revenue for the suspense reason; investigate Jones Unit 1. |
| Lookalike cost center | Jones Unit 1 revenue for September and October booked to CC-2210 instead of CC-2201. That's why Jones Unit 1 showed up as billed, not paid. | Reclass the revenue to CC-2201; both cost centers' LOS are wrong until it's fixed. |
| NRI check | Baker 1 Partner A paid at 0.1875 instead of 0.175 (WI 0.20 × (1 − 0.125)). | Pull the division order and correct the revenue deck; the overpayment needs recouping. |
| AFE spend | Davis 4H's workover AFE is 103.2% spent, about $40,000 over. | Supplemental AFE to partners before the next JIB; see why partners hold AFE charges on the JIB-AR page. |
| Coding exceptions | Six lines: a charge after Clark 2 was sold, a duplicate invoice, Jones Unit 1H coded to CC-2201, a charge on a closed AFE, and a Capital line on a P&A AFE. | Fix before the lines reach the JIB. The invoice coding checker does the same checks by paste. |
| LOE per BOE | Harlan 1 is the most expensive barrel at $6.54/BOE, ahead of its plugging; Jones Unit 1H the cheapest at $3.13. | Review the high-cost wells with Operations. |
| Accrual accuracy | The biggest misses are AFE accruals where the field estimate lagged (Ortega 3H in May, Davis 4H in August) and Smith 3H accruing its old average after it was shut in. | Get Operations' guidance before the accrual, and zero out shut-in wells. See monthly accruals. |
How the queries are built
- Join on well name and cost center, always. Never on the name alone. That one habit is what surfaces the lookalike cost centers.
- Anti-joins find what's missing. A LEFT JOIN with a null check on the other side lists costs with no revenue, or revenue with no well master record.
- Compare to an expected value, not just to zero. The NRI check computes what each decimal should be from the WI and royalty, then reports only the differences.
- One row per thing to do. Each query returns a list a reviewer can work top to bottom, sorted by dollars or severity.
The database runs entirely in your browser using SQLite (sql.js); nothing is sent anywhere. All wells, owners, vendors and figures are made up.