Simplify SQL Query using variable

Question

I want to simplfy my T-SQL query. It is like this :

SELECT

    t.a AS [Column A], 
    t.b AS [Column B],
    t.c AS [Column C],
    (t.a - t.b - t.c) AS [Column D],
    CASE
      WHEN (t.a - t.b - t.c) = 0 THEN 'Equals'
      WHEN (t.a - t.b - t.c) > 0 THEN 'Greater'
      WHEN (t.a - t.b - t.c) < 0 THEN 'Less'
    END AS [Status]          

FROM
    Table1 AS t;

It would be nice to put (t.a - t.b - t.c) into a variable, so I can reuse it on all places it occurs, because the expression may change over time. I could not figure out how to do this without changing the existing query significantly.

Answer 1

Use Cross Apply

SELECT
    t.a AS [Column A], 
    t.b AS [Column B],
    t.c AS [Column C],
    [Column D],
    CASE
      WHEN [Column D] = 0 THEN 'Equals'
      WHEN [Column D] > 0 THEN 'Greater'
      WHEN [Column D] < 0 THEN 'Less'
    END AS [Status]          

FROM
    Table1 AS t
    CROSS APPLY (SELECT t.a - t.b - t.c AS [Column D]) AS t2

Answer 2

You can use a CTE (Common Table Expression) like this:

;WITH CTE AS
(
    SELECT
        t.a AS [Column A], 
        t.b AS [Column B],
        t.c AS [Column C],
        (t.a - t.b - t-c) AS [Column D]
    FROM
        Table1 AS t
)
SELECT
    [Column A], 
    [Column B],
    [Column C],
    [Column D],
    CASE
      WHEN [Column D] = 0 THEN 'Equals'
      WHEN [Column D] > 0 THEN 'Greater'
      WHEN [Column D] < 0 THEN 'Less'
    END AS [Status]      
FROM
    CTE

This defines a CTE - something like a "ad-hoc" view - that you can use to handle things like calculations, aggregations etc. and then select from it (or use other SQL statements against it). The CTE only exists for the one, next statement - it doesn't get "persisted" for multiple SQL statements. But it's quite handy to handle situations like this