Results 1 to 2 of 2

Thread: Adding Next Appointment Date

  1. #1
    Join Date
    Jun 2011

    Adding Next Appointment Date

    Hi I'm dave and I'm new to the forum, SQL and SSRS. Thanks in advance for your assistance and patience.

    I have a recall report that produces letters for every patient who needs to return to the office on a certain date based on their recall plan (a plan that determines when they should return based on their condition). I have another table that stores all appointments for each patients (past and present). Appointments in the recall_plans table are auto-generated whereas appointments in the appointments table where created manually. The recall plan report is not checked if a person calls in to make an appointment so often the same appointment is represented in both tables resulting in duplicate reminder letters being sent out.

    I need to do two things:
    (I know my approach is not necessarily resolving the business problem but this is what I am tasked with)

    1. I need to produce a list showing the next appointment for each patient but only if it is in the future.
    2. I need to add a column to the first report showing each patient’s next appointment so someone can manually identify that duplicate letters would go out for specific patients and intervene accordingly.

    Recall Report Query:

    SELECT description as [Plan Name],
    per.first_name + ' ' + per.last_name as [Patient],
    substring (plan_start_date, 5,2) + '-' +
    substring (plan_start_date, 7,2) + '-' +
    substring (plan_start_date, 1,4) as [Plan Start Date],
    substring (nr.expected_return_date, 5,2) + '-' +
    substring (nr.expected_return_date, 7,2) + '-' +
    substring (nr.expected_return_date, 1,4) as [Expected Return Date]
    FROM recall_plan_mstr rp,
    patient_recall_plans nr,
    patient pt,
    person per
    WHERE rp.practice_id = nr.practice_id
    and rp.recall_plan_id = nr.recall_plan_id
    and nr.practice_id = pt.practice_id
    and nr.person_id = pt.person_id
    and per.person_id = pt.person_id
    and (active_plan_ind = 'Y')
    and rp.practice_id = '0025'

    Recall Report Results:

    OFFICE VISIT W/ DR Charles Span 04-18-2011 12-15-2011
    LIPID PANEL Ronald Chap 04-11-2011 06-28-2011
    OFFICE VISIT W/ DR Ronald Chap 04-11-2011 04-21-2011
    OFFICE VISIT W/ DR Will Thor 03-31-2011 02-01-2012
    PACEMAKER CHECK Sylvia Berkly 05-03-2011 08-03-2011
    OFFICE VISIT W/ DR Tim Cayle 04-13-2011 09-26-2011
    OFFICE VISIT W/ DR Caferana Mercade 04-11-2011 10-08-2011
    OFFICE VISIT W/ DR Susanna Calter 05-10-2011 05-07-2012
    ICD CHECK Jim Southern 04-14-2011 07-13-2011
    STRESS ECHO Don Cobey 04-28-2011 06-07-2010

    Appointments Query:

    select person_id, appt_date
    from appointments
    where person_id is not null
    group by person_id, appt_date
    order by person_id, appt_date desc

    Appointments Results:

    073C8F83-CE15-4192-8E12-00006CB5A433 20091228
    073C8F83-CE15-4192-8E12-00006CB5A433 20090510
    073C8F83-CE15-4192-8E12-00006CB5A433 20090301
    073C8F83-CE15-4192-8E12-00006CB5A433 20081006
    378A281C-FAE7-43DF-BC03-00006E386680 20110509
    378A281C-FAE7-43DF-BC03-00006E386680 20110217
    378A281C-FAE7-43DF-BC03-00006E386680 20110124
    378A281C-FAE7-43DF-BC03-00006E386680 20110111
    378A281C-FAE7-43DF-BC03-00006E386680 20101207
    816D4D31-3C99-4762-878D-000097883B73 20110316
    816D4D31-3C99-4762-878D-000097883B73 20101216


    1. How can I produce a list from the appointments table that results with one patient per row with only the latest appointment that is in the future? Do I need to write a cursor for that?
    2. How can I comingle this list into my recall report so it has a column to the right of return column that displays the patient’s next appointment date (future only)? Both tables have a person number GUID.

    I hope I've adequately explained and provided enough information. If any additional information is needed please don’t hesitate to ask.


  2. #2
    Join Date
    Jun 2012
    INSERT INTO #temp
    SELECT person_id, appt_dt
    FROM recall
    WHERE appt_dt > getdate()
    SELECT person_id, appt_dt
    FROM appointment
    WHERE appt_dt > getdate()

    SELECT person_id, min(appt_dt)
    FROM #temp
    GROUP BY person_id

    this would give you the next appointment for a person. Not super efficient, but it would work.

    PS I think you need to fix the logic to get it to work correctly.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts