Changes between Version 1 and Version 2 of UseCase11

Timestamp:

09/19/26 17:08:32 (3 days ago)

Author:

236021

Comment:

--

UseCase11

@@ -41 +41 @@
 {{{
 #!sql
-INSERT INTO referrals (record_id, from_doctor_id, to_doctor_id, reason, referral_date, appointment_date, appointment_time)
+INSERT INTO referrals (record_id, from_doctor_id, to_doctor_id, reason, referral_date)
 VALUES (
   (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')),
@@ -47 +47 @@
   (SELECT doctor_id FROM users WHERE username = 'nikola.trajkov'),
   'Cardiology consultation for hypertension management',
-  '2026-05-30',
-  '2026-06-15',
-  '14:00'
+  '2026-05-30'
 )
 RETURNING referral_id;
@@ -77 +75 @@
   r.reason,
   r.referral_date,
-  r.appointment_date,
-  r.appointment_time,
+  a.appointment_date,
+  a.appointment_time,
   u_from.first_name AS from_doctor_first_name,
   u_from.last_name AS from_doctor_last_name,
@@ -88 +86 @@
 JOIN doctors d_to ON r.to_doctor_id = d_to.doctor_id
 JOIN users u_to ON d_to.doctor_id = u_to.doctor_id
-WHERE r.referral_id = (SELECT MAX(referral_id) FROM referrals);
+JOIN appointments a ON a.patient_id = r.record_id AND a.doctor_id = r.to_doctor_id
+WHERE r.referral_id = (SELECT MAX(referral_id) FROM referrals)
+ORDER BY a.appointment_date DESC
+LIMIT 1;
 }}}
 
@@ -99 +100 @@
   r.reason,
   r.referral_date,
-  r.appointment_date,
   u_p.first_name AS patient_first_name,
   u_p.last_name AS patient_last_name