Hi,
I have a following table:
create table t ( user_id varchar2(10), clinic varchar2(50), visit_dt date );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '08-APR-2008') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '21-APR-2008') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '08-JAN-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '26-JAN-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '29-JAN-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '24-FEB-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '17-MAR-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '30-MAR-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '04-MAY-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '19-OCT-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '18-NOV-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '25-NOV-2009') );
We are looking to select users who visited a clinic two or more times with visits at least 3 months apart. Is there a way to only select those visits that are >=3 months apart? The first qualifying visit is the first visit to the clinic and next qualifying visit must be >=3 months later. The might be multiple visits under 3 months, but we only interested in those that are 3+. The clock re-starts after each qualifying visit. User ID can be linked to more than one clinic, but qualifying visits need to be to the same clinic.
From the table above I need the following records:
user_id clinic visit_dt
---------------------------------
12 oncology 08-APR-2008
12 oncology 08-JAN-2009
12 oncology 04-MAY-2009
12 oncology 19-OCT-2009
Any help is much appreciated! Thank you.
It's a shame you're not on 12c. Pattern matching makes this easy :)
Just define an always-true initial variable. Then another which matches all those within 3 months of this:
alter session set nls_date_format = ' DD Mon YY ';
select * from t
match_recognize (
order by visit_dt
measures
match_number() visit,
first(st.visit_dt) stdt,
coalesce(last(visit.visit_dt), st.visit_dt) endt,
user_id usr,
clinic clin
one row per match
pattern ( st visit* )
define
visit as months_between ( visit_dt, st.visit_dt ) < 3
);
VISIT STDT ENDT USR CLIN
1 08 Apr 08 21 Apr 08 12 oncology
2 08 Jan 09 30 Mar 09 12 oncology
3 04 May 09 04 May 09 12 oncology
4 19 Oct 09 25 Nov 09 12 oncology
On 11.2 you can get what you want using recursive with. Which is a little clunky!
In the base condition, select the first row for the patient. Then, in the recursive part, check if the date of the next visit is within three months of the initial. If it is, return the previous date. If not, return the current date.
Then take the distinct of this to get the visits:
with vals as (
select t.*,
row_number() over (order by visit_dt) rn,
min(visit_dt) over () mn
from t
), visits (
user_id, clinic, visit_strt, rn
) as (
select user_id, clinic, visit_dt, rn
from vals
where visit_dt = mn
union all
select s.user_id, s.clinic,
case
when months_between(s.visit_dt, v.visit_strt) < 3 then
v.visit_strt
else
s.visit_dt
end, s.rn
from visits v
join vals s
on v.rn + 1 = s.rn
)
select distinct user_id, clinic, visit_strt
from visits
order by visit_strt;
USER_ID CLINIC VISIT_STRT
12 oncology 08 Apr 08
12 oncology 08 Jan 09
12 oncology 04 May 09
12 oncology 19 Oct 09