Calculating time gaps between appointment dates for a patient in clinic in mySQL

Question

I'd like to work out if a patient has been late for their appointment from a database, and how much by.

I have a data table from a clinic that has columns:

1. Pt - Patient number
2. Appt - The date patient attended clinic
3. Next_appt - The assigned next appointment date

It looks something like this:

Pt  Appt        Next_appt
12  2013-04-22  2013-05-21
12  2013-05-20  2013-07-15
57  2010-06-08  2010-07-05
57  2010-08-03  2010-10-19
127 2009-02-24  2009-06-23
127 2009-04-20  2009-05-11

Hence for patient 12 - he was assigned the next_appt date on 2013-05-21 and he attended on 2013-05-20 (1 day early)

It would be easy to shift the Next_appt columns down by one and then subtract the two, but then I would run into trouble as the dates will correspond to different patients then.

There are about 20,000 patients(!) in the database.

I wonder if anyone could give me some suggestions?

Thank you,

Answer 1

Try this:

select
    t1.*,
    case sign(t2.appt - t1.next_appt)
         when 0 then 'on time'
         when 1 then 'late'
         when -1 then 'early' end 
from 
    your_table t1
left join
    your_table t2
on 
    t1.pt = t2.pt -- shift down with pt as join condition
and t1.appt < t2.appt