Click here to Skip to main content
15,446,232 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Guys,

I have a query that I'm creating a column in select using case statement
select col1, (case when'x' then xDate else yDate) as newDate,(case when newDate< joiningDate then 1 else 0) as PDate
from table1 t1
left join table2 on

I'm unable to find the column newDate in IntelliSense in order to create column PDate. if I type it manually it throws an invalid column error.
I'm not sure how exactly does it work.

Can anyone help me to get the desired result?

Thank you.

What I have tried:

I'm not sure how does it work.
Updated 20-Sep-21 3:01am

Calculated columns aren't available in the same SELECT statement where they're defined. You either need to repeat the calculation:
    WHEN = 'x' And xDate < joiningDate THEN 1
    WHEN != 'x' And yDate < joiningDate THEN 1
    ELSE 0
END As PDate

Or you can use CROSS APPLY to define your calculated column:
    CASE WHEN D.newDate < joiningDate THEN 1 ELSE 0 END As PDate
    table1 As t1
    LEFT JOIN table2 As t2 ON =
    CROSS APPLY (SELECT CASE WHEN = 'x' THEN xDate ELSE yDate END As newDate) As D
The many uses of CROSS APPLY | SQL Studies[^]
Share this answer
Thank you Richard, Cross Apply just worked great :) and got to know a use case as well.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900