The Ops Accountant · Analyze · SQL

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.

QueryFindsNext step
Billed, not paidTen 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 centerJones 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 checkBaker 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 spendDavis 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 exceptionsSix 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 BOEHarlan 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 accuracyThe 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

The database runs entirely in your browser using SQLite (sql.js); nothing is sent anywhere. All wells, owners, vendors and figures are made up.