如果 time col 为空，则替换为 00:00

Question

>我正在研究医院数据库，表格详细信息是

Patient number     Dischargeto             Date           Time
212              Hospital1            16/10/2018      14:00:00
212              Hospital2            18/10/2018      10:00:00
212              Hospital3            20/10/2018      18:00:00
212              Home                 22/10/2018      10:00:00
213              Hostpital1           11/11/2018      11:00:00
213              Death                14/11/2018      18:00:00
214              Hospital 1           28/12/2011      14:00:00
214              Home                 05/01/2012      NULL
Info:
Final destination of the patient 
212 is Home
213 is Death
214 is home

我想要最终目的地不是死亡的病人 所以我写了这个查询

select * 
from 
(select
Patient number, DischargeTo, Date, Time,
ROW_NUMBER() OVER(PARTITION BY Patientnumber order by Date desc, Time desc) as testcount
from tablename) abc
where   testcount = 1
and 
DischargeTo not like '%Death%'

当时间为空时，结果不正确。我想转换 如果时间为空，则转换为 00：00：00 因此可以更正排序。

谢谢贝霍雷手

Answer 1

在排序之前将日期时间设置为有效的 SQL 日期时间。

select * 
from 
(select
Patient number, DischargeTo, Date, Time,
ROW_NUMBER() OVER(PARTITION BY Patientnumber order by cast(concat(RIGHT([Date],4) + '' + SUBSTRING([Date],4,2) + '' + LEFT([Date],2), ' ', [Time]) as datetime)) as testcount
from tablename) abc
where   testcount = 1
and 
DischargeTo not like '%Death%'

Answer 2

我切换了日期字符串中的日期和月份，因为日期时间转换给出了越界错误。请尝试以下操作：

with hospitaldt(PatientId, DischargeTo, dt) as
(
select PatientId, DischargeTo, convert(datetime, concat(Date, " ", isnull(Time, "00:00:00")))
from hospital
)
select t1.PatientId, t1.DischargeTo, t1.dt
from hospitaldt t1
where t1.dt =
(
select max(t2.dt)
from hospitaldt t2
where t2.DischargeTo not like "%Death%"
and t2.PatientId = t1.PatientId

Answer 3

我认为"死亡"只发生一次，所以你不需要寻找最终状态。 只要检查死亡是否曾经存在：

select patient_number
from tablename
group by patient_number
having sum(case when dischargeto = 'Death' then 1 else 0 end) = 0;