确定将来患者是否有另一个记录

Question

我有一个代表约会的表：

Appointments
- id
- doctor_id
- patient_id
- start_date
- attended (bool)

所以我有一个查询，可以让我所有预约但实际上没有出现的患者：

select 
        patient_id
from appointments a
where attended=false

，但我要添加的是，如果患者没有参加，但将来有另一个约会：

select 
        patient_id,
        has_future_appointment
from appointments a
where attended=false

Answer 1

select 
    a.patient_id
    , cast(case when a_future.patient_id is not null then 1 else 0 end as bit) has_future_appointment
from
    appointments a
    outer apply
        (
            select top 1 a_future.patient_id
            from appointments a_future
            where
                a_future.patient_id = a.patient_id
                and a_future.[start_date] > GETDATE()
        ) a_future
where attended=0

Answer 2

过滤

的一种方法

WITH PatienLits as(
select 
    patient_id
from appointments a
where attended=0
and start_date <= GETDATE()
)
SELECT a.patient_id,a.start_date, a.attended from
appointments a
inner join PatienLits P
on a.patient_id = p.patient_id
WHERE
a.start_date > GETDATE()

Answer 3

您可以检查患者日期大于当前的日期

select patient_id
from appointments a
where attended = falase and start_date < systdate();