sql - How to use one data field into another case expression in the same query

Question

I want to use SNumber in the second part of CASE expression but not sure how to use it?

    select    
        PE.EDateTime,
        (case when(PE.EDateTime is not NULL and cast(PE.EDateTime as time) < '12:30') then cast(format(PE.EDateTime,'yyyyMMddhhmm') as varchar(50))+'AM'
             when (PE.ADate is not NULL and cast(PE.ADate as time) < '12:30') then cast(format(PE.ADate,'yyyyMMddhhmm') as varchar(50))+'AM'
             when (PE.EDateTime is not NULL and cast(PE.EDateTime as time) >= '12:30') THEN  cast(format(PE.EDateTime,'yyyyMMddhhmm') as varchar(50))+'PM'
             when  (PE.ADate is not NULL and cast(PE.ADate as time) > '12:30') then cast(format(PE.ADate,'yyyyMMddhhmm') as varchar(50))+'PM'
             else null 
            end) as SNumber,
   
     case 
   when (SNumber like'%AM') then 'AM'
   when SNumber like '%PM') then 'PM'
   else null 
end as [Session],
        Comments   
    from (
        select       
            PE1.RId,
            PE1.ADate,
            PE1.EDateTime
        from (
            select
                RegEId, 
                ADate,
                EDateTime,
                Comments
            from PatEnr
            where PreNumber is not null               
            ) as PE1
        left join Pat PEA 
            on PE1.RegEId = PEA.RegEId   
        left join PBooking PB 
            on PB.RegEId = PE1.RegEId
        ) as PE

I want to use SNumber here below in the same query but I am not able to use this as this all belongs to one query. Is there any way to use the above in below CASE? I want to use something like below.

case 
   when (SNumber like'%AM') then 'AM'
   when (SNumber like '%%PM') then 'PM'
   else null 
end as Session,

Answer 1

Use cross apply to perform your calculation which you can then reuse.

select
    SNumber
    , case when SNumber like '%AM%' then 'AM'
    when SNumber like  '%PM%' then 'PM'
    else null end as [Session]
from MyTable PE
cross apply (
values (
    case when(PE.EDateTime is not NULL and cast(PE.EDateTime as time) < '12:30') then cast(format(PE.EDateTime,'yyyyMMddhhmm') as varchar(50))+'AM'
    when (PE.ADate is not NULL and cast(PE.ADate as time) < '12:30') then cast(format(PE.ADate,'yyyyMMddhhmm') as varchar(50))+'AM'
    when (PE.EDateTime is not NULL and cast(PE.EDateTime as time) >= '12:30') THEN  cast(format(PE.EDateTime,'yyyyMMddhhmm') as varchar(50))+'PM'
    when (PE.ADate is not NULL and cast(PE.ADate as time) > '12:30') then cast(format(PE.ADate,'yyyyMMddhhmm') as varchar(50))+'PM'
    else null 
    end
)) x (SNumber)

Note: contains doesn't work in that context - its a where clause predicate for full-text search, so I've replaced with like.