Hey there!

While working with financial data in SQL, I ran into a somewhat forgotten function called SIGN() that turned out to be surprisingly useful.

The SIGN() function returns:

• 1 when the expression is positive
• 0 when the expression is zero
• -1 when the expression is negative

This makes it particularly useful for anyone working with financial data.

Let’s walk through a practical example.

The Setup

First, let’s create a table that simulates the daily profit or loss of a company over the last 180 days.

CREATE TABLE dbo.daily_profit
(
    date        DATE,
    profit      DECIMAL(10,2),
    revenue     DECIMAL(10,2),
    expenses    DECIMAL(10,2)
);

A simple table with just four columns.

Now let’s generate 180 days of sample data and calculate profit using the formula:

profit = revenue - expenses

WITH dates AS
(
    SELECT 
        DATEADD(DAY, -value, CAST(GETDATE() AS DATE)) AS date,
        CAST(ROUND(1000 + (ABS(CHECKSUM(NEWID())) % 1000000)/100.0, 2) AS DECIMAL(10, 2)) AS revenue,
        CAST(ROUND(500 + (ABS(CHECKSUM(NEWID())) % 1000000)/100.0, 2) AS DECIMAL(10, 2)) AS expenses
    FROM 
        GENERATE_SERIES(1,180)
)
INSERT INTO dbo.daily_profit(date, revenue, expenses, profit)
SELECT
    date,
    revenue,
    expenses,
    revenue - expenses AS profit
FROM
    dates
ORDER BY 
    date ASC;

Let’s query the table to see what the data looks like.

SELECT date, profit, revenue, expenses 
FROM dbo.daily_profit;

date        profit      revenue     expenses
----------  ----------  ----------  ----------
2024-02-21  1439.64     10461.24    9021.60
2024-02-20  -2334.20    2344.88     4679.08
2024-02-19  4244.82     10173.05    5928.23
2024-02-18  4418.97     5297.38     878.41
2024-02-17  1680.12     3247.45     1567.33
2024-02-16  -4091.95    1339.46     5431.41

Using the SIGN() Function in Financial Data Analysis

With the SIGN() function, we can easily classify each record as:

• Profitable (profit > 0)
• Breakeven (profit = 0)
• Loss-making (profit < 0)

This categorization helps analysts quickly identify performance trends and spot anomalies.

SELECT
    date,
    profit,
    CASE SIGN(profit)
        WHEN  1 THEN 'Profitable'
        WHEN  0 THEN 'Breakeven'
        WHEN -1 THEN 'Loss-making'
    END AS performance
FROM
    dbo.daily_profit
ORDER BY
    date ASC;

date        profit      performance
----------  ----------  ------------
2023-08-26  2006.57     Profitable
2023-08-27  -1078.00    Loss-making
2023-08-28  1798.61     Profitable
2023-08-29  3335.40     Profitable
2023-08-30  5861.58     Profitable
2023-08-31  -5517.12    Loss-making

Tracking Profit Changes with SIGN() and LAG()

The SIGN() function becomes even more useful when combined with window functions like LAG().

For example, you may want to know whether profit has increased, decreased, or stayed the same compared to the previous day.

SELECT
    date,
    CASE SIGN(profit - LAG(profit) OVER (ORDER BY date))
        WHEN  1 THEN 'Increased'
        WHEN  0 THEN 'No change'
        WHEN -1 THEN 'Decreased'
    END AS profit_change_direction
FROM
    dbo.daily_profit;

date        profit_change_direction
----------  -----------------------
2023-08-26  NULL
2023-08-27  Decreased
2023-08-28  Increased
2023-08-29  Increased
2023-08-30  Increased
2023-08-31  Decreased
2023-09-01  Increased
2023-09-02  Decreased
2023-09-03  Decreased
2023-09-04  Increased

Conclusion

The SIGN() function may not be the first tool that comes to mind for financial analysis, but its simplicity and flexibility make it a valuable addition to your SQL toolkit.

Combined with window functions, it allows you to quickly classify financial performance and track changes over time with minimal effort.

Thanks for reading!
You can find the code for this post and others on my GitHub.

Cheers! 🍻