| Author |
Topic |
|
lamenramen
Starting Member
6 Posts |
 Posted - 2011-04-04 : 00:19:48
|
Hi everyone,I'd really appreciate some help on my problem. I am not sure how to write the query, first of all. Second of all, I'm not sure if I need to use looping,cursors, and stored procedures, or just use set-based logic.I have a list of people who were diagnosed with high blood pressure (hypertension) on a certain visit date. All I want to know is whether, on that same visit date, they received a high blood pressure medication called Toprol or not.So the final table should be:patient visit_date received_Toprol1 1/1/11 y2 1/2/11 n3 1/3/11 n4 1/4/11 y Here are the tables that I need to combine in order to produce the final table listed above.Visit History Table (I only care about the bolded visit dates for each patient)patient visit_date diagnosis1 1/1/11 high blood pressure1 1/3/11 flu1 2/2/09 diabetes2 1/2/11 high blood pressure2 1/5/10 heart attack3 1/3/11 high blood pressure3 1/4/08 asthma4 1/4/11 high blood pressure4 3/1/05 sprained ankleMedication History Tablepatient medication_name date1 Toprol 1/1/111 Aspirin 1/1/112 Toprol 1/3/11 (note, Toprol was received, but on wrong date for pt. #2.) 2 Tylenol 3/3/10*3* patient #3 is not in this table b/c he has never received any meds, ever4 Toprol 1/4/114 Aspirin 1/4/11 The patient visit history is in one table, and the medication history of all meds (not just Toprol), is in another one. You can join them by patient and date. You would need both patient and date in my case since a patient might be seen on 1/2/11, and receive a med, but not be a hypertension med. Or a patient might have received a hypertension med, but not on the visit date that I'm interested in.I'm able to narrow down the Visit History table to just those 4 visit dates that I'm interested in, thus constructing part of the final table:patient visit_date 1 1/1/11 2 1/2/11 3 1/3/11 4 1/4/11 Now I need to decide whether each patient got Toprol on that visit date or not by referencing the Medication History Table, and add it as another column to my final table. I've spent the better part of the weekend going in circles. I think that I need a series of left outer joins or something, or maybe I am going to need loops? At this point I'm just hoping for a solution.Thank you so much! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-04-04 : 00:39:45
|
why patient 1 with visit date of 1/3/11 is not in your result ? On what basis you determine to include the patent & visit_date in your result ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
lamenramen
Starting Member
6 Posts |
Posted - 2011-04-04 : 00:48:37
|
| Hi, patient #1 with a visit date of 1/3/11 was seen. However, he was not given any medications on 1/3/11, so there is no entry for him in the medications history table.Patient and visit dates that I am interested in are only the ones in which patients were diagnosed with high blood pressure. For clarity, I have updated the original post to add a new column to make this clearer. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-04-04 : 00:52:12
|
"patient #1 with a visit date of 1/3/11 was seen. However, he was not given any medications on 1/3/11, so there is no entry for him in the medications history table."If the condition is based on existence any record in the medication history, then why patient 3 that is with any history is also included in your result ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
lamenramen
Starting Member
6 Posts |
Posted - 2011-04-04 : 00:58:13
|
| patient # 3 was seen on 1/3/11 and diagnosed with high blood pressure. However, he was not given Toprol (he was given no medications on 1/3/11). So in the final result table, I record patient #3 as "received Toprol = n". I need to capture the lack of being given a medication. In this case, I guess that the doctor felt that though patient #3 has high blood pressure, patient #3 could try dieting and exercise first before being put on Toprol. |
|
|
|
lamenramen
Starting Member
6 Posts |
Posted - 2011-04-04 : 01:00:29
|
quote:
Originally posted by lamenramen
patient # 3 was seen on 1/3/11 and diagnosed with high blood pressure. However, he was not given Toprol (he was given no medications on 1/3/11, nor ever actually). So in the final result table, I record patient #3 as "received Toprol = n". I need to capture the lack of being given a medication. In this case, I guess that the doctor felt that though patient #3 has high blood pressure, patient #3 could try dieting and exercise first before being put on Toprol.
|
|
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2011-04-04 : 01:22:57
|
| LamenRamen,it is always good to have all the values set; like in case of patient #1. if he received only medications there should be also a value for that. So you do not have to relay on your knowledge of what null value for this column means and you certainly avoid null problems when joining the tables. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-04-04 : 04:07:55
|
[code]select v.patient, v.visit_date, receive_Toprol = case when m.patient is null then 'n' else 'y' endfrom visit v left join ( select patient, date from medication where medication_name = 'Toprol' ) m on v.patient = m.patient and v.visit_date = m.datewhere v.diagnosis = 'high blood pressure'[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
lamenramen
Starting Member
6 Posts |
Posted - 2011-04-06 : 11:30:50
|
| Thank you so very much! Your query works, and I learned something valuable.Great board. |
|
|
|
|
Stuck on writing a probably simply query