SQL to query by date dependencies

Question

Hi, all.

I have a table of patients which has the following columns: patient_id, obs_id, obs_date. Obs_id is the ID of a clinical observation (such as weight reading, blood pressure reading....etc), and obs_date is when that observation was taken. Each patient could have several readings on different dates...etc. Currently I have a query to get all patients that had obs_id = 1 and insert them into a temporary table (has two columns, patient_id, and flag which I set to 0 here):

insert into temp_table (select patient_id, 0 from patients_table 
where obs_id = 1 group by patient_id having count(*)   >= 1)

I also execute an update statement to set the flag to 1 for all patients that also had obs_id = 5:

UPDATE temp_table SET flag = 1 WHERE EXISTS ( 
  SELECT patient_id  FROM patients_table WHERE obs_id = 5 group by patient_id having   count(*)     >=1
 ) v  WHERE temp_table.patient_id = v.patient_id

Here's my question: How do I modify both queries (without combining them or removing the group by statement) such that I can answer the following question: "get all patients who had obs_id = 5 after obs_id = 1". If I add a min(obs_date) or max(obs_date) to the select of each query and then add "AND v.obs_date > temp_table.obs_date" to the second one, is that correct??

The reason why I need not remove the group by statement or combine is because these queries are generated by a code generator (from a web app), and i'd like to do that modification without messing up the code generator or re-writing it.

Many thanks in advance,

Answer 1

The advantage of SQL is that it works with sets. You don't need to create temporary tables or get all procedural.

As you describe the problem (find all patients who have obs_id 5 after obs_id 1), I'd start with something like this

select distinct p1.patient_id 
from patients_table p1, patients_table p2 
where 
p1.obs_id = 1 and
p2.obs_id = 5 and
p2.patient_id = p1.patient_id and
p2.obs_date > p1.obs_date

Of course, that doesn't help you deal with your code generator. Sometimes, tools that make things easier can also get in the way.