如何在同一查询中将一个数据字段用于另一个 case 表达式
我想在 CASE
表达式的第二部分使用 SNumber
但不知道如何使用它?
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
我想在下面的同一查询中使用 SNumber
,但我无法使用它,因为这都属于一个查询.有没有办法在下面的 CASE
中使用上述内容?我想使用如下所示的内容.
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,
推荐答案
使用交叉应用来执行您可以重复使用的计算.
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)
注意:contains
在该上下文中不起作用 - 它是全文搜索的 where
子句谓词,因此我已替换为 喜欢
.
Note: contains
doesn't work in that context - its a where
clause predicate for full-text search, so I've replaced with like
.
相关文章