Add new variable in where clause with cross apply

Question

Created a new date variable in select statement, i need to add this new variable to the where clause as below;

         SELECT a.bim, a.cim, b.Block,
            (datename(dw, a.start_date)+ ', ' + convert  (varchar(20),a.start_date,107) ) as mmm
          FROM CK.SCHEDULE a
         LEFT JOIN ck.Lookup b on b.date1=mmm
                WHERE bim='2024'

This 'mmm' variable need to be equal 'date1' Tried cross apply but didnt work in this one.

date1 looks: Monday, July 03, 2023

  start_Date looks: 2018-07-02 00:00:00.0000000

Answer 1

mmm is out of the scope of the WHERE clause. However, if you define it in a subquery then it would be in scope of the WHERE clause.

For example:

create table t (a int, b int);

insert into t (a, b) values (1, 2), (3, 4), (5, 6)

select *
from (
  select t.*, a + b as c from t
) x
where c > 5 -- c is in scope in the external WHERE clause

See fiddle.