Using SQL Alias In Expression Without SELECTING It

Question

I have a simple issue in SQL (using SQL Server) and it drives me nuts... I'm sure there must be an easy solution but have yet to identify it. I must add that I have been programming in SQL for some time now and am not a beginner.

Basically I run a query on 2 Tables (1 and 2) using an INNER JOIN and in the SELECT part, I ask for several columns to be returned... so far everything plain and basic.

But I also want to refer to a column in Table A without selecting it as such. The column itself contains the last entry date of an item in our inventory system and is called [Last Entry Dt].

Now, I do not want this column to be in the query output but rather the number of days since that Last Entry Dt, which is derived from [Last Entry Dt]. Next, using this information I qualify in the output dataset each item with strings such as "old item", "relatively new item", "new item" etc.

In short, from the query I don't need the [Last Entry Dt], but rather this qualifier.

I tried the following:

SELECT 
    Acol, Bcol, Ccol, 
    [Last Entry Dt] AS LED, 
    DATEDIFF('d', LED, TodayDate) AS ItemAge 
FROM 
    Table1 
INNER JOIN 
    Table1.somefield = Table2.somefied etc.

And of course it works, but I get the last entry date from the query as well, which I don't need in the output dataset. So the question is, how do I define the alias LED in the query without actually selecting it but rather merely using it in another expression?

I tried using a double SELECT statement, where the second one is appended after FROM, i.e.

SELECT 
    Acol, Bcol, Ccol, 
    DATEDIFF('d', LED, TodayDate) AS ItemAge
FROM 
    Table1 
INNER JOIN 
    Table1.somefield = Table2.somefied, 
(SELECT [Last Entry Dt] AS LED)

but I got a

Syntax error in FROM clause

Thank you so much,

Stefan

i'm guessing you want CROSS APPLY. It allows you to define a recurring value which can be used everywhere — siggemannen, Mar 29 '23 at 14:22

score 2 · Answer 1 · answered Mar 29 '23 at 14:06

If you don't really want the column [Last Entry Dt], but a "classification" based on it - try something like this (also - you cannot refer to an alias that you've introduced in the SELECT, inside the same SELECT- you need to use the actual column name):

SELECT 
    Acol, Bcol, Ccol, 
    [Last Entry Dt] AS LED, 
    CASE 
        WHEN DATEDIFF(day, [Last Entry Dt], TodayDate) <= 30 THEN 'Fairly recent'
        WHEN DATEDIFF(day, [Last Entry Dt], TodayDate) <= 90 THEN 'A bit older'
        WHEN DATEDIFF(day, [Last Entry Dt], TodayDate) > 90 THEN 'Quite old really'
        ELSE 'undetermined'
    END AS Classification
FROM 
    Table1 
INNER JOIN 
    Table2 ON Table1.somefield = Table2.somefield

Furthermore:

I'd recommend using the proper day, month etc. in DATEDIFF - instead of their abbreviations - just makes the code more readable and understandable
I correct the INNER JOIN - you need to INNER JOIN with a second table and an ON join condition

I'd edit this and substitute "DATEDIFF('d', Last Entry Dt, TodayDate) AS ItemAge" for "[Last Entry Dt] AS LED", since the OP doesn't actually want the date, but rather the age. — Johnny Bones, Mar 29 '23 at 14:16
@JohnnyBones: OK - `LED` is a bit of a misleading and not very clear name - but that's really the OP's choice - really has no bearing on the functionality :D — marc_s, Mar 29 '23 at 14:17

John Cappelletti · Answer 2 · 2023-03-29T15:03:35.603

0

Another option is a CROSS APPLY with the VALUES construct. This allows you to stack and reference calculations

Depending on your tables, the CROSS APPLY can come after the JOIN

Example

SELECT Acol
     , Bcol
     , Ccol
     ,DATEDIFF('d', LED, TodayDate) AS ItemAge 
FROM Table1 
Cross Apply ( values ( [Last Entry Dt] )) C(LED)
INNER JOIN  Table1.somefield = Table2.somefied

edited Mar 29 '23 at 15:03

answered Mar 29 '23 at 14:11

John Cappelletti

79,615
7
44
66

Using SQL Alias In Expression Without SELECTING It

2 Answers2