I am trying to ultimately get a list of the last time a statin has been prescribed for a patient
I have a patient medication table that I have been able to write a query that then shows all the statins with person_id. that are active
after that I have limited the fields only to patient_ID, modify_timestamp and Medication-name
but 3 patients keep showing up 2 times.
I thought maybe it was the create timestamp field that caused the problem, but that was not it, removed all number fields except id and modify timestamp and still these 3 patients show up in the final query.
so if someone could please look at these three and see what I have done that could be causing this problem
1st query is the main one to identify statin medications currently active
(unfortunately the pharmaceauticals cant pick one number)
SELECT dbo_patient_medication.person_id, dbo_patient_medication.modify_timestamp, dbo_patient_medication.medication_name
FROM dbo_patient_medication
WHERE (((dbo_patient_medication.date_stopped)="" Or (dbo_patient_medication.date_stopped)>"20150101") AND ((dbo_patient_medication.gcn_seqno)=2379)) OR (((dbo_patient_medication.gcn_seqno)=2380 Or (dbo_patient_medication.gcn_seqno)=2381 Or (dbo_patient_medication.gcn_seqno)=2389 Or (dbo_patient_medication.gcn_seqno)=2390 Or (dbo_patient_medication.gcn_seqno)=2391 Or (dbo_patient_medication.gcn_seqno)=2394 Or (dbo_patient_medication.gcn_seqno)=2395 Or (dbo_patient_medication.gcn_seqno)=2396 Or (dbo_patient_medication.gcn_seqno)=3100 Or (dbo_patient_medication.gcn_seqno)=3101 Or (dbo_patient_medication.gcn_seqno)=3102 Or (dbo_patient_medication.gcn_seqno)=6416 Or (dbo_patient_medication.gcn_seqno)=6460 Or (dbo_patient_medication.gcn_seqno)=6461 Or (dbo_patient_medication.gcn_seqno)=11743 Or (dbo_patient_medication.gcn_seqno)=13675 Or (dbo_patient_medication.gcn_seqno)=16310 Or (dbo_patient_medication.gcn_seqno)=16366 Or (dbo_patient_medication.gcn_seqno)=16367 Or (dbo_patient_medication.gcn_seqno)=16576 Or (dbo_patient_medication.gcn_seqno)=16577 Or (dbo_patient_medication.gcn_seqno)=16578 Or (dbo_patient_medication.gcn_seqno)=16579 Or (dbo_patient_medication.gcn_seqno)=20741 Or (dbo_patient_medication.gcn_seqno)=21694 Or (dbo_patient_medication.gcn_seqno)=22344 Or (dbo_patient_medication.gcn_seqno)=27864 Or (dbo_patient_medication.gcn_seqno)=29967 Or (dbo_patient_medication.gcn_seqno)=29968 Or (dbo_patient_medication.gcn_seqno)=29969 Or (dbo_patient_medication.gcn_seqno)=33364 Or (dbo_patient_medication.gcn_seqno)=33366 Or (dbo_patient_medication.gcn_seqno)=40238 Or (dbo_patient_medication.gcn_seqno)=45772 Or (dbo_patient_medication.gcn_seqno)=46172 Or (dbo_patient_medication.gcn_seqno)=46757 Or (dbo_patient_medication.gcn_seqno)=48571 Or (dbo_patient_medication.gcn_seqno)=49758 Or (dbo_patient_medication.gcn_seqno)=50556 Or (dbo_patient_medication.gcn_seqno)=50557 Or (dbo_patient_medication.gcn_seqno)=50558 Or (dbo_patient_medication.gcn_seqno)=51214 Or (dbo_patient_medication.gcn_seqno)=51784 Or (dbo_patient_medication.gcn_seqno)=51785 Or (dbo_patient_medication.gcn_seqno)=51786 Or (dbo_patient_medication.gcn_seqno)=52944 Or (dbo_patient_medication.gcn_seqno)=53689 Or (dbo_patient_medication.gcn_seqno)=53690 Or (dbo_patient_medication.gcn_seqno)=53691 Or (dbo_patient_medication.gcn_seqno)=53693 Or (dbo_patient_medication.gcn_seqno)=53694 Or (dbo_patient_medication.gcn_seqno)=53695 Or (dbo_patient_medication.gcn_seqno)=57859 Or (dbo_patient_medication.gcn_seqno)=57863 Or (dbo_patient_medication.gcn_seqno)=57865 Or (dbo_patient_medication.gcn_seqno)=58432 Or (dbo_patient_medication.gcn_seqno)=58486 Or (dbo_patient_medication.gcn_seqno)=61199 Or (dbo_patient_medication.gcn_seqno)=61230 Or (dbo_patient_medication.gcn_seqno)=62885 Or (dbo_patient_medication.gcn_seqno)=63025 Or (dbo_patient_medication.gcn_seqno)=63683 Or (dbo_patient_medication.gcn_seqno)=64063 Or (dbo_patient_medication.gcn_seqno)=64310 Or (dbo_patient_medication.gcn_seqno)=64676 Or (dbo_patient_medication.gcn_seqno)=64677 Or (dbo_patient_medication.gcn_seqno)=65929 Or (dbo_patient_medication.gcn_seqno)=66297 Or (dbo_patient_medication.gcn_seqno)=66349 Or (dbo_patient_medication.gcn_seqno)=66350));
second query limits the first query to show the last time a patient got any statin but I am getting some patients showing up more than 1 time
SELECT statin_query_all.*
FROM statin_query_all INNER JOIN (SELECT person_id, MAX(modify_timestamp) AS TimeMatch FROM statin_query GROUP BY person_id) AS MaxRecord ON (statin_query_all.person_id = MaxRecord.person_id) AND (statin_query_all.modify_timestamp = MaxRecord.TimeMatch); (5 patients showing up two times)
I have used the second query with other queries and have not had this problem so I am just not sure what I am doing wrong.
thank you
Patty
Posted by: pattykf@cox.net
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar