1
SELECT   DISTINCT OH.MRN,OH.VISIT_NUMBER,OH.EVENT_TIME
FROM ONCOLOGY_HISTROY_MV OH
WHERE   OH.PROC='BC - Heamatology-Oncology Appt'
ORDER BY EVENT_TIME ASC

I'm trying to get the first visit for each MRN (Patient) in a particular PROC (which is Procedure)

Please help :)

FatherofFaris
  • 41
  • 1
  • 5

3 Answers3

1

It could be as simple as this:

select mrn, min(visit_date) mindate
from oncology_history_mv
where proc = 'BC Heamatology-Oncology Appt'
group by mrn

If you need more details, make the above a derived table and join to it.

 select fields you need
 from oncology_history_mv onc join 
 (select mrn, min(visit_date) mindate
from oncology_history_mv
where proc = 'BC Heamatology-Oncology Appt'
group by mrn ) temp on onc.mrn = temp.mrn and vist_date = mindate
where proc = 'BC Heamatology-Oncology Appt'

This assumes that event_time does not include the date of the procedure, just the time of day.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
1

This is a good opportunity to use analytic functions:

SELECT MRN, VISIT_NUMBER, EVENT_TIME
FROM (SELECT OH.MRN, OH.VISIT_NUMBER, OH.EVENT_TIME,
             ROW_NUMBER() OVER (PARTITION BY OH.MRN ORDER BY OH.EVENT_TIME) AS SEQNUM
      FROM ONCOLOGY_HISTROY_MV OH
      WHERE OH.PROC = 'BC - Heamatology-Oncology Appt'
     ) OH
WHERE SEQNUM = 1
ORDER BY EVENT_TIME ASC;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-2

it can be done as follow

SELECT  OH.MRN,,OH.EVENT_TIME
FROM ONCOLOGY_HISTROY_MV OH
WHERE   OH.PROC='BC - Heamatology-Oncology Appt'
ORDER BY EVENT_TIME AS
GROUP BY OH.MRN
HAVING min(OH.EVENT_TIME)

this will give you the first date when the MRN record was added which implies that it will be the first visit.

Aunn Raza
  • 468
  • 2
  • 12