SIGN: Classify Positive, Negative and Zero Values

SIGN classifies a numeric value as negative, zero or positive without retaining its magnitude. I use that classification when direction is the question. A missing amount still needs a separate label.

Separate direction from size

The sample includes two negative values with very different magnitudes. Both are expected to produce the direction code negative one. The code describes which side of zero the value occupies.

Two positive values likewise share the expected code one. One is a hundredth, while the other is two hundred. Their shared direction does not make the amounts interchangeable.

Zero has its own expected code zero. That is a present numeric value rather than an absent measurement. I keep the amount beside the direction so the classification does not conceal the original quantity.

Every supplied amount is explicitly decimal(8,2). This makes the representation consistent across the VALUES rows. The final direction code is cast to int for a small and clear display contract.

WITH Amounts AS
(
    SELECT CaseId,Amount
    FROM (VALUES
        (1,CAST(-12.50 AS decimal(8,2))),
        (2,CAST(-0.01 AS decimal(8,2))),
        (3,CAST(0 AS decimal(8,2))),
        (4,CAST(0.01 AS decimal(8,2))),
        (5,CAST(200 AS decimal(8,2))),
        (6,CAST(NULL AS decimal(8,2)))
    ) AS v(CaseId,Amount)
)
SELECT CaseId,Amount,CAST(SIGN(Amount) AS int) AS DirectionCode,
    CASE WHEN Amount IS NULL THEN N'Missing'
         WHEN SIGN(Amount)<0 THEN N'Negative'
         WHEN SIGN(Amount)>0 THEN N'Positive'
         ELSE N'Zero' END AS DirectionLabel
FROM Amounts
ORDER BY CaseId;

Explain missing input before the zero branch

The final row supplies typed NULL. Its expected direction code is NULL. The label expression checks that missing input before evaluating the positive and negative conditions.

Without the missing branch, an ELSE label could misleadingly call that row Zero. Neither comparison with a missing amount establishes that it is zero. The absence needs an explicit outcome.

I’d preserve this difference when summarizing movement. No movement and no supplied movement are different facts. A report can decide how to present each without changing the stored amount.

The expected labels are Negative, Negative, Zero, Positive, Positive and Missing. Reviewing all six together exposes a default branch that accidentally merges missing data with a real boundary value.

Choose the representation before testing the sign

The source type affects which values reach the comparison. A prior conversion can round a tiny nonzero input to zero. SIGN then classifies the converted value it actually receives.

The hundredth values in this example remain representable at scale two. They therefore sit on opposite sides of zero as intended. They are not tests of every possible fractional source value.

I wouldn’t infer original direction after a lossy conversion. Preserve the source quantity when that distinction matters. Decide the accepted scale before using a zero classification to drive business behavior.

SIGN returns the same numeric category as its input. This article normalizes the displayed direction to int explicitly. It does not claim that every SIGN call naturally returns that exact output type.

Keep the classification within its job

A direction code can be useful for grouping positive and negative adjustments. It cannot reveal their total value by itself. Two positive adjustments can have very different effects on a balance.

Adding direction codes also answers a different question from adding amounts. A large positive amount and a small negative amount can have cancelling codes. Their numeric amounts need not cancel.

I keep magnitude calculations on the original typed amount. Direction labels can remain separate columns in the same output. That arrangement makes each reported number’s purpose visible.

These examples use exact decimal inputs to avoid a floating-point tolerance policy. An approximate measurement close to zero may require a business threshold. Such a threshold is a separate rule, not part of the sign function.

Choose labels that describe the application’s amount domain. Keep zero, positive, negative and missing amounts separate. Those four cases prevent a missing value from becoming an unintended sign classification.

Keep the amount next to the label, and nothing gets lost.

A direction code is not an amount, it is a classification that deliberately discards magnitude.

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