Turning a Business Question Into a Query: Define the Metric First

Two correct queries can disagree when their metric definitions differ. Write the business question as a precise counting and timing contract before writing its SELECT.

State What the Business Question Counts

This example asks for completed-order net movement during one local calendar month. Count completed order amounts recognized within the period. Subtract accepted returns recorded within the period, including returns for earlier completed orders.

The definition intentionally measures period activity rather than the eventual value of one order cohort. A return next month does not reduce this month's activity. An older order's accepted return this month does reduce this month's activity.

I write those distinctions before comparing competing reports. I also ask the owner to approve the smallest concrete examples. A metric name alone rarely explains timing, exclusions, and attribution well enough.

Use one currency, USD, without currency conversion in this fixture. Order amounts already exclude tax and include agreed discounts. Canceled orders and their associated returns are excluded under this deliberately chosen contract.

Make the Source Grain Visible

The order table has one row per order, with its recognition timestamp stored in UTC. The return table has one row per accepted return event. Its amount is positive and will be subtracted by the reporting query.

Those grains are different and must stay separate during aggregation. Joining every return directly to an order before summing order amounts repeats orders with multiple returns. Aggregate or union the event populations deliberately instead.

CREATE TABLE #MetricOrder
(
    OrderId int NOT NULL PRIMARY KEY,
    CompletedUtc datetime2(0) NULL,
    Status varchar(12) NOT NULL,
    CurrencyCode char(3) NOT NULL,
    NetAmount decimal(12,2) NOT NULL
);
CREATE TABLE #MetricReturn
(
    ReturnId int NOT NULL PRIMARY KEY,
    OrderId int NOT NULL,
    AcceptedUtc datetime2(0) NOT NULL,
    ReturnAmount decimal(12,2) NOT NULL
);
INSERT #MetricOrder VALUES
    (1,'2025-01-31T20:00:00','Completed','USD',100),
    (2,'2025-02-10T10:00:00','Completed','USD',50),
    (3,NULL,'Canceled','USD',70),
    (4,'2025-02-28T20:00:00','Completed','USD',80),
    (5,'2025-01-31T18:00:00','Completed','USD',90),
    (6,'2025-02-05T10:00:00','Completed','USD',200);
INSERT #MetricReturn VALUES
    (1,1,'2025-02-15T10:00:00',30),
    (2,2,'2025-03-02T10:00:00',20),
    (3,5,'2025-02-08T10:00:00',10),
    (4,3,'2025-02-09T10:00:00',5);

The temporary fixture does not enforce its return relationship with a foreign key. The reporting join explicitly requires a matching eligible order. A permanent source should also enforce or validate the appropriate relationship.

Historical status changes need a defined reporting policy. This example assumes finalized completed and canceled classifications for its fixture. Real reports need event history or a known as-of snapshot when current status can rewrite past results.

Convert the Period Boundaries Once

The chosen business calendar uses India Standard Time. SQL Server on Windows exposes supported zone names through sys.time_zone_info. AT TIME ZONE is available from SQL Server 2016 onward.

Define local midnight boundaries and convert each boundary to UTC. The source timestamp predicate can then compare UTC values directly. Avoid applying a time-zone conversion to every source row just to filter the period.

DECLARE @LocalStart datetime2(0) = '2025-02-01T00:00:00';
DECLARE @LocalEnd datetime2(0) = '2025-03-01T00:00:00';
DECLARE @StartUtc datetime2(0), @EndUtc datetime2(0);
SET @StartUtc = CONVERT(datetime2(0),
    @LocalStart AT TIME ZONE 'India Standard Time' AT TIME ZONE 'UTC');
SET @EndUtc = CONVERT(datetime2(0),
    @LocalEnd AT TIME ZONE 'India Standard Time' AT TIME ZONE 'UTC');
SELECT @StartUtc AS PeriodStartUtc, @EndUtc AS PeriodEndUtc;
SELECT @StartUtc AS StartUtc, @EndUtc AS EndUtc INTO #MetricPeriod;

Use an inclusive start and exclusive end. That rule handles adjacent periods without counting a boundary event twice. It also avoids assumptions about the last representable fractional second before midnight.

For zones with daylight-saving transitions, local periods do not always have fixed hour lengths. Convert their actual boundaries under the chosen zone rules. Do not substitute the server's current UTC offset for historical period conversion.

Time-zone rules are maintained outside the query and can change. Store the reporting definition and necessary as-of evidence for reproducibility. The zone name is part of the metric contract rather than an incidental display preference.

Build the Business Question Query in Named Steps

Each CTE should express one part of the approved definition. EligibleOrders establishes status and currency. OrderEvents and ReturnEvents apply their own event timestamps before the signed populations are combined.

UNION ALL preserves legitimate events rather than deduplicating equal amounts. An identical amount on two different events is not a duplicate. Event identifiers and source ingestion rules should handle real duplication.

-- Metric: completed-order net movement, USD, local February 2025.
-- Orders count by CompletedUtc; accepted returns count by AcceptedUtc.
-- Returns for earlier completed orders are included in this period.
-- Canceled orders and their returns are excluded.
WITH EligibleOrders AS
(
    SELECT OrderId, CompletedUtc, NetAmount
    FROM #MetricOrder
    WHERE Status = 'Completed' AND CurrencyCode = 'USD'
), OrderEvents AS
(
    SELECT O.OrderId AS EventId, 'Order' AS EventType, O.NetAmount AS SignedAmount
    FROM EligibleOrders AS O CROSS JOIN #MetricPeriod AS P
    WHERE O.CompletedUtc >= P.StartUtc AND O.CompletedUtc < P.EndUtc
), ReturnEvents AS
(
    SELECT R.ReturnId AS EventId, 'Return' AS EventType,
           -R.ReturnAmount AS SignedAmount
    FROM #MetricReturn AS R
    JOIN EligibleOrders AS O ON O.OrderId = R.OrderId
    CROSS JOIN #MetricPeriod AS P
    WHERE R.AcceptedUtc >= P.StartUtc AND R.AcceptedUtc < P.EndUtc
), Events AS
(
    SELECT EventId, EventType, SignedAmount FROM OrderEvents
    UNION ALL
    SELECT EventId, EventType, SignedAmount FROM ReturnEvents
)
SELECT EventId, EventType, SignedAmount INTO #MetricEvent FROM Events;
SELECT COALESCE(SUM(SignedAmount), 0) AS NetMovement
INTO #MetricResult FROM #MetricEvent;
SELECT * FROM #MetricEvent ORDER BY EventType, EventId;
SELECT NetMovement FROM #MetricResult;

The retained event table makes included amounts inspectable. That supports reviewing disagreements at the event level instead of debating one unexplained total. Avoid retaining sensitive event details beyond the approved reporting process.

Naming CTEs does not make a metric correct by itself. Each predicate still needs to match the signed definition. Review time, status, currency, and event identity independently before optimizing the query.

Check the Sample by Hand

The included order amounts are one hundred, fifty, and two hundred. The included returns are thirty and ten. Their selected fixture arithmetic gives an expected net movement of three hundred ten.

The late February UTC order belongs to March in the chosen local calendar. Order 5, at 18:00 UTC on January 31, falls before local midnight and stays outside the order population. Its February return still belongs to the defined period activity.

IF NOT EXISTS
(
    SELECT 1 FROM #MetricResult WHERE NetMovement = 310.00
)
    THROW 50001, 'The metric differs from the hand-checked fixture.', 1;
IF (SELECT COUNT_BIG(*) FROM #MetricEvent) <> 5
    THROW 50002, 'Unexpected event membership in the fixture.', 1;

These are expected values from supplied inputs, not claimed production query results. Execute the isolated script and inspect every included event. Then challenge the definition with empty periods, boundary timestamps, and multiple returns for one order.

Do not use the query itself to manufacture its expected answer. Hand-check the small fixture independently from its SQL expression. A test that repeats the same error on both sides is reassuring only by accident.

Keep Business Question Changes Visible

Would the owner expect a March return to revise February or reduce March? Get an explicit answer and retain it with the query. Both interpretations exist, but combining them produces inconsistent comparisons.

A new cancellation policy or currency rule changes the metric meaning. Review its effect before comparing new totals against historical ones. Version the definition when its population or timing contract changes.

I treat a business question as a short specification before translating it into SQL. I keep that specification next to the reporting statement. A metric should not need a committee to explain what its plus sign means.

Separate accounting authority from this illustrative operational measure. An official financial report needs its approved recognition and adjustment rules. The query's clarity helps review, while the business owner remains responsible for the meaning.

Check Source Completeness before Trusting the Total

Return to the business question when reviewing late-arriving events. Decide whether the report is provisional or final at its extraction cutoff. A correct event filter cannot include a source record that has not arrived yet.

Use a consistent source snapshot when orders and returns are changing during extraction. Otherwise different query stages can describe different moments. Agree on the supported isolation or reporting-copy process rather than hiding that inconsistency with a CTE name.

Record the extraction cutoff and source freshness with the published metric. Readers need to know when additional events can revise the number. Distinguish a changed definition from a refreshed population when explaining a later difference.

Related reading on this blog: "Clean Data" Is Not a Requirement: Writing Rules People Can Act On and A Walkthrough: DATETRUNC Function in SQL Server.

A reporting query is not the metric definition, it is the executable form of an agreed counting and timing contract.

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