Lane Profitability with SQL: Revenue per Mile, Deadhead and Margin

Why lane profitability hides in plain sight

Ask a carrier which lanes make money and you will usually get a confident answer from dispatch, a different one from sales, and a third from accounting. Each is looking at a different slice: dispatch sees how hard the lane is to cover, sales sees the rate, accounting sees totals by customer at month-end. Nobody is wrong, but nobody has the full picture of revenue, miles, empty miles and cost tied together load by load.

The good news is that most of that data already sits in your TMS database. Order entry captured the rate and the lane. Dispatch captured the loaded and empty miles. Billing captured fuel surcharge and accessorials. Settlements and carrier pay captured the largest direct costs. SQL is simply the tool that joins them up.

This guide is written for the owner, CFO, operations manager or analyst who wants real numbers. You do not need to be a database administrator, but you do need read access to your data and a willingness to define terms precisely.

Define the metrics before you write a query

Most arguments about lane profitability are really arguments about definitions. Agree on these first and write them down.

Three decisions to make up front

• Which miles? Use the same mileage source for every load (for example, practical route miles from your mileage software, or ELD-recorded miles). Mixing billed miles, practical miles and odometer miles in one report quietly distorts every RPM figure.
• Where does deadhead go? The most common rule charges empty miles driven to reach a pickup to the load being picked up. An alternative charges empty miles after delivery to the load that left the truck there. Pick one.
• What counts as a lane? State to state is easy but coarse. City pairs or three-digit ZIP pairs are more precise but thin. Many carriers report at market or region level with drill-down to city pairs.

The example schema

-- Hypothetical example schema (not from any real TMS)
loads(load_id, customer_id, origin_city, origin_state,
      dest_city, dest_state, delivered_date, status,
      linehaul_amt, fsc_amt, accessorial_amt,
      loaded_miles, empty_miles)

customers(customer_id, customer_name)

load_costs(load_id, cost_type, amount)
  -- cost_type examples: 'DRIVER_PAY', 'CARRIER_PAY', 'FUEL', 'TOLLS'

stops(load_id, stop_seq, stop_type, arrived_at, departed_at,
      free_minutes)

accessorial_charges(load_id, charge_code, amount)
  -- charge_code examples: 'DET', 'LAY', 'STOP', 'LUMP', 'TONU'

Query 1: Revenue per loaded mile, per total mile and deadhead by lane

This query rolls delivered loads up to state-to-state lanes for a date range and returns volume, revenue, both RPM figures and deadhead percentage. Multiplying by 1.0 forces decimal math so integer miles do not truncate the result. NULLIF prevents divide-by-zero errors when miles are missing.

-- Example only: adapt names and date syntax to your database
SELECT
    l.origin_state,
    l.dest_state,
    COUNT(*)                                        AS loads,
    SUM(l.linehaul_amt + l.fsc_amt + l.accessorial_amt) AS revenue,
    SUM(l.loaded_miles)                             AS loaded_miles,
    SUM(l.empty_miles)                              AS empty_miles,
    1.0 * SUM(l.linehaul_amt + l.fsc_amt + l.accessorial_amt)
        / NULLIF(SUM(l.loaded_miles), 0)            AS rev_per_loaded_mile,
    1.0 * SUM(l.linehaul_amt + l.fsc_amt + l.accessorial_amt)
        / NULLIF(SUM(l.loaded_miles + l.empty_miles), 0) AS rev_per_total_mile,
    1.0 * SUM(l.linehaul_amt)
        / NULLIF(SUM(l.loaded_miles), 0)            AS linehaul_rpm,
    100.0 * SUM(l.empty_miles)
        / NULLIF(SUM(l.loaded_miles + l.empty_miles), 0) AS deadhead_pct
FROM loads l
WHERE l.status = 'DELIVERED'
  AND l.delivered_date >= '2026-01-01'
  AND l.delivered_date <  '2026-07-01'
GROUP BY l.origin_state, l.dest_state
HAVING COUNT(*) >= 10
ORDER BY rev_per_total_mile DESC;

The HAVING clause hides lanes with fewer than ten loads, which keeps one odd load from topping the list. Adjust the threshold to your volume.

How to read it

• A lane with strong revenue per loaded mile but weak revenue per total mile is paying well for the loaded leg and then stranding trucks. Look at what happens after delivery.
• A lane with modest RPM but very low deadhead may be one of your best, because it keeps trucks moving and balances the network.
• A large gap between linehaul RPM and all-in RPM tells you how much the lane leans on FSC and accessorials.

Query 2: Direct margin by customer

Revenue tells you who is busy. Margin tells you who pays. This query totals direct costs per load first, then joins them to revenue so a load with several cost lines is not counted more than once, a common mistake that inflates revenue.

-- Example only
WITH cost_per_load AS (
    SELECT load_id, SUM(amount) AS direct_cost
    FROM load_costs
    GROUP BY load_id
)
SELECT
    c.customer_name,
    COUNT(*)                                             AS loads,
    SUM(l.linehaul_amt + l.fsc_amt + l.accessorial_amt)  AS revenue,
    SUM(COALESCE(k.direct_cost, 0))                      AS direct_cost,
    SUM(l.linehaul_amt + l.fsc_amt + l.accessorial_amt)
      - SUM(COALESCE(k.direct_cost, 0))                  AS direct_margin,
    100.0 * (SUM(l.linehaul_amt + l.fsc_amt + l.accessorial_amt)
             - SUM(COALESCE(k.direct_cost, 0)))
      / NULLIF(SUM(l.linehaul_amt + l.fsc_amt + l.accessorial_amt), 0)
                                                         AS margin_pct,
    SUM(CASE WHEN k.load_id IS NULL THEN 1 ELSE 0 END)   AS loads_missing_cost
FROM loads l
JOIN customers c        ON c.customer_id = l.customer_id
LEFT JOIN cost_per_load k ON k.load_id = l.load_id
WHERE l.status = 'DELIVERED'
  AND l.delivered_date >= '2026-01-01'
  AND l.delivered_date <  '2026-07-01'
GROUP BY c.customer_name
ORDER BY direct_margin DESC;

The loads_missing_cost column matters. If a customer shows a beautiful margin but half its loads have no cost recorded, the margin is fiction until settlements or carrier pay catch up. Run margin reports only for periods where pay is closed.

For brokerage, the same pattern works with carrier pay as the main cost line, and the result is gross margin per load. For asset fleets, you may also want a fully loaded view that allocates fixed costs such as equipment, insurance and overhead per mile. Keep that as a separate, clearly labeled report so the direct margin stays clean.

Query 3: Accessorial capture, starting with detention

Missed accessorials are revenue you already earned and never billed. Detention is the most common one. If your TMS or ELD integration captures arrival and departure times at each stop, you can find stops where dwell time exceeded free time but no detention charge exists.

-- Example only. DATEDIFF(minute, a, b) is SQL Server style.
-- PostgreSQL equivalent: EXTRACT(EPOCH FROM (b - a)) / 60
WITH dwell AS (
    SELECT
        s.load_id,
        s.stop_seq,
        s.stop_type,
        DATEDIFF(minute, s.arrived_at, s.departed_at) AS dwell_minutes,
        s.free_minutes
    FROM stops s
    WHERE s.arrived_at IS NOT NULL
      AND s.departed_at IS NOT NULL
)
SELECT
    l.load_id,
    c.customer_name,
    d.stop_seq,
    d.stop_type,
    d.dwell_minutes,
    d.free_minutes,
    d.dwell_minutes - d.free_minutes AS billable_minutes
FROM dwell d
JOIN loads l     ON l.load_id = d.load_id
JOIN customers c ON c.customer_id = l.customer_id
LEFT JOIN accessorial_charges a
       ON a.load_id = d.load_id
      AND a.charge_code = 'DET'
WHERE d.dwell_minutes > d.free_minutes
  AND a.load_id IS NULL
  AND l.delivered_date >= '2026-01-01'
ORDER BY billable_minutes DESC;

Each row is a candidate, not an automatic invoice. Some customers have contracts that waive detention, some stops are the carrier's fault, and some arrival times are wrong. But the list gives billing a short, specific worklist instead of a vague sense that detention is slipping. The same pattern works for extra stops (stop count versus stop charges), layovers and lumper receipts without a matching charge.

Query 4: Data quality checks to run first

Before anyone makes pricing decisions from these numbers, check the inputs. A five-minute data quality query saves a lot of embarrassing meetings.

-- Example only: count suspicious delivered loads by issue
SELECT 'Zero or missing loaded miles' AS issue, COUNT(*) AS loads
FROM loads
WHERE status = 'DELIVERED' AND COALESCE(loaded_miles, 0) = 0
UNION ALL
SELECT 'Zero linehaul revenue', COUNT(*)
FROM loads
WHERE status = 'DELIVERED' AND COALESCE(linehaul_amt, 0) = 0
UNION ALL
SELECT 'Missing empty miles (NULL)', COUNT(*)
FROM loads
WHERE status = 'DELIVERED' AND empty_miles IS NULL
UNION ALL
SELECT 'No direct cost recorded', COUNT(*)
FROM loads l
WHERE l.status = 'DELIVERED'
  AND NOT EXISTS (SELECT 1 FROM load_costs k WHERE k.load_id = l.load_id);

Data quality checklist

• Duplicate customers merged, so one account is not split across several names.
• One consistent mileage source for loaded and empty miles.
• Rebills, credits and cancelled loads handled consistently (excluded, or netted against the original load).
• Accessorials billed on supplemental invoices tied back to the original load ID.
• Costs posted at the load level, not only as lump sums in accounting.
• Driver pay and carrier pay closed for the period you are reporting.

Turning numbers into decisions

A lane report earns its keep when it changes what people do on Monday morning. Common uses:

• Pricing and bid season. Bring actual revenue per total mile and deadhead by lane into rate reviews and RFP responses instead of relying on memory.
• Network balance. Pair lanes that strand trucks with backhaul freight, or price the deadhead into the headhaul rate.
• Customer conversations. Use dwell and detention data to discuss facility performance with shippers, with facts in hand.
• Billing discipline. Turn the accessorial capture query into a daily or weekly exception report for billing.
• Sales targeting. Focus sales effort on freight that fills weak lanes, not just more freight.

Schedule the queries, put the results in a simple dashboard or report, and review them on a fixed rhythm. A monthly lane review with operations, sales and finance in the same room does more than any one-off analysis.

How Strong Tower Consulting helps

Strong Tower Consulting has been mining carrier databases since 2008. We map where your TMS actually stores revenue, miles, stops and costs, clean up the data that skews results, write and validate the queries, and build the recurring reports your team will use. We work with the system you already have.

• SQL Data Mining & Analytics: lane, customer and driver profitability straight from your data.
• Reporting & Business Documents: scheduled reports, dashboards, invoices and statements.
• Business Process Optimization: fixing the capture problems that leave accessorials unbilled.

Frequently asked questions

SQL Server is a trademark of Microsoft Corporation. PostgreSQL is a trademark of the PostgreSQL Community Association of Canada. All trademarks belong to their respective holders. Strong Tower Consulting is independent and not affiliated with or endorsed by these companies.