Notifications
Clear all
Topic starter
I'm trying to find a way to run a report for a list of patients by EDC. If possible to be sorted by month of their due date. I'm trying to track patients due by each month.
Posted : October 14, 2013 3:42 am
Here is a SQL Query I have used against the Oracle database in EMR 9.5. It uses the value of the EDC obs term but I have a commented line that uses the EDD BY LMP obs term also.
This line - and Obs.ObsDate >= '01-JAN-13' - is where you set how far back you want to go (this indicates EDC was entered in the chart on or after Jan 1 of this year.
You could instead use this line, which is commented out -
--and to_date(obs.obsvalue,'mm/dd/YYYY') > sysdate
To get all EDC values greater than today's date.
/* Due Dates */
select * from (
select Obs.pId, Obs.hdId, Obs.obsValue, Obs.obsDate
, ObsHead.name as obsName
, Max(obsDate) over (partition by obs.pid, obs.hdid) as maxDate
from ml.Obs
join ml.ObsHead on Obs.hdId = ObsHead.hdId
--where ObsHead.name = 'EDD BY LMP'
where ObsHead.name = 'EDC'
and Obs.ObsDate >= '01-JAN-13'
--and to_date(obs.obsvalue,'mm/dd/YYYY') > sysdate
and Obs.xId = 1.e+035
and Obs.change = 2
) where obsDate = maxDate ORDER BY obsvalue
Posted : October 15, 2013 6:20 am
Topic starter
Thank you, its very much appreciated. I will give it a try.
Posted : October 15, 2013 11:55 pm
