CODEWITHSUNDEEP
sqlsql-server
cool breeze
cool breeze

Reputation: 4811

Determining if the patient has a another record in the future

I have a table that represents Appointments:

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

So I have a query that gets me all the patients that made an appointment but did not actually show up for it:

select 
        patient_id
from appointments a
where attended=false

But what I want to add is, if a patient didn't attend but has another appointment in the future:

select 
        patient_id,
        has_future_appointment
from appointments a
where attended=false

Upvotes: 0

Views: 44

Answers (3)

Guru0008
Guru0008

Reputation: 54

Here you can check patient date greater than current

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

Upvotes: 0

Chris Mack
Chris Mack

Reputation: 5208

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

Upvotes: 1

Rex
Rex

Reputation: 521

One way of filtering 

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()

Upvotes: 0

Related Questions