When One Number Has Three Different Answers

Most reporting inconsistency investigations become easier after three questions: which rows, which moment, and which unit? Two correct calculations can disagree because they answer different business questions.

Write Down the Meaning of the Number

Ask each report owner to finish a precise sentence about the metric. For example, count completed orders by order date, excluding cancellations. Revenue, collected cash, and invoiced value need different definitions even when their totals look similar.

Record currency, time zone, status rules, and the treatment of refunds. Confirm whether the number represents events during a period or a balance at one moment. Do this before reviewing the SQL.

A shared label does not guarantee a shared definition. Rename genuinely different metrics instead of forcing them to agree. The purpose of reconciliation is understanding, not making every dashboard display the same value.

Build a Small Example With Visible Differences

The following temporary table represents order lines rather than orders. Two lines belong to one order, and another order is cancelled. Run the examples together in a fresh query session.

CREATE TABLE #ReportLines
(
    OrderId int NOT NULL,
    LineId int NOT NULL,
    OrderDate date NOT NULL,
    Status varchar(12) NOT NULL,
    LoadedAtUtc datetime2(0) NOT NULL,
    Amount decimal(12,2) NOT NULL,
    PRIMARY KEY (OrderId, LineId)
);
INSERT #ReportLines VALUES
(1,1,'20260921','Complete','2026-09-22T01:00:00',100),
(1,2,'20260921','Complete','2026-09-22T01:00:00',50),
(2,1,'20260921','Cancelled','2026-09-22T01:00:00',80),
(3,1,'20260921','Complete','2026-09-22T03:00:00',120);

These values are illustrative test data, not production measurements. Their purpose is to make each difference explainable. A tiny example often reveals a definition problem faster than a large aggregate query.

Match the Filters

Start with visible slicers, then inspect hidden filters, security rules, and source-query conditions. Check whether blank values are included or excluded. Also check whether a date filter applies to orders, invoices, or payments.

SELECT Status, COUNT_BIG(*) AS line_count,
       COUNT(DISTINCT OrderId) AS order_count,
       SUM(Amount) AS amount
FROM #ReportLines
WHERE OrderDate >= '20260921'
  AND OrderDate < '20260922'
GROUP BY Status;

Separating status groups shows which rows enter each total. Repeat this breakdown for the first dimension where the reports disagree. Keep the comparison small enough that individual keys can be inspected.

Do not use rounding to hide a population mismatch. First reconcile the underlying rows and calculation rules. Rounding becomes relevant only after those fundamentals agree.

Match the Cutoff

One report may have refreshed before the latest source load completed. Another may query the live source or refresh later. Both can use identical formulas while observing different data.

DECLARE @CutoffUtc datetime2(0) = '2026-09-22T02:00:00';
SELECT COUNT(DISTINCT OrderId) AS completed_orders,
       SUM(Amount) AS completed_amount
FROM #ReportLines
WHERE Status = 'Complete'
  AND LoadedAtUtc <= @CutoffUtc;

This example uses an immutable load timestamp to demonstrate an arrival cutoff. Real historical reconstruction also needs to account for later updates and deletes. A current table cannot necessarily reproduce yesterday's state from one timestamp.

Use a retained snapshot or appropriate history when the comparison requires past values. Record successful upstream loads separately from report refreshes. A successful refresh can still import an incomplete source.

Match the Grain

Grain means what one row represents. Order lines, orders, customers, and daily balances are different units. COUNT of rows only counts the unit actually stored.

SELECT COUNT_BIG(*) AS completed_line_count,
       COUNT(DISTINCT OrderId) AS completed_order_count,
       SUM(Amount) AS completed_line_amount
FROM #ReportLines
WHERE Status = 'Complete';

A join can duplicate a value when its two sides have different grains. Joining order totals to multiple lines repeats the order total. Aggregate deliberately before joining or calculate at the correct grain.

DISTINCT is not a universal repair for this problem. Different valid rows can contain equal amounts, and removing equal values can lose real money. Fix the relationship between keys and measures.

Keep a Reconciliation Record

Save the agreed definition, source objects, filters, cutoff, and grain together. Include the query or measure version and a small repeatable example. Make the owner of each business rule visible.

When the numbers still differ, compare keys and subtotals rather than only grand totals. Classify the first difference and trace it upstream. The next report should inherit the agreed definition instead of recreating the argument.

A matching total is not a shared definition, it is the result of matching scope, time, and grain.

This post was rewritten from scratch in September 2026. The original, published on 2012-09-16, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.