oracle sql to retrieve followup dates from a preceding date

Question

CREATE TABLE tst_tbl
(
   id                 NUMBER,
   last_name          VARCHAR2 (50),
   first_name         VARCHAR2 (50),
   dob                DATE,
   register_dt        DATE,
   register_loc       VARCHAR2 (50),
   visit_dt           DATE,
   visit_loc          VARCHAR2 (50),
   visit_comments   VARCHAR2 (30)
);


INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('09/05/2017' ,'MM/DD/YYYY')   ,'NEW YORK',  to_date('02/26/2018','MM/DD/YYYY'), 'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('09/05/2017' ,'MM/DD/YYYY')   ,'NEW YORK',  to_date('2/12/2018', 'MM/DD/YYYY'),'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('09/05/2017' ,'MM/DD/YYYY')   ,'NEW YORK',  to_date('11/6/2017', 'MM/DD/YYYY'),'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('09/05/2017' ,'MM/DD/YYYY')   ,'NEW YORK',  to_date('10/23/2017','MM/DD/YYYY'), 'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('09/05/2017' ,'MM/DD/YYYY')   ,'NEW YORK',  to_date('3/27/2018', 'MM/DD/YYYY'),'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('09/05/2017' ,'MM/DD/YYYY')   ,'NEW YORK',  to_date('3/19/2018', 'MM/DD/YYYY'),'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('09/05/2017' ,'MM/DD/YYYY')   ,'NEW YORK',  to_date('9/11/2017', 'MM/DD/YYYY'),'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('2/7/2018'   ,'MM/DD/YYYY')   ,  'NEW YORK',to_date('11/6/2017 ','MM/DD/YYYY'), 'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('2/7/2018'   ,'MM/DD/YYYY')   ,  'NEW YORK',to_date('3/19/2018', 'MM/DD/YYYY'),'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('2/7/2018'   ,'MM/DD/YYYY')   ,  'NEW YORK',to_date('9/11/2017', 'MM/DD/YYYY'),'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('2/7/2018'   ,'MM/DD/YYYY')   ,  'NEW YORK',to_date('3/27/2018', 'MM/DD/YYYY'),'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('2/7/2018'   ,'MM/DD/YYYY')   ,  'NEW YORK',to_date('2/26/2018', 'MM/DD/YYYY'),'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('2/7/2018'   ,'MM/DD/YYYY')   ,  'NEW YORK',to_date('10/23/2017','MM/DD/YYYY'), 'NEW JERSEY', '');
INSERT INTO tst_tbl VALUES(1234, 'John', 'Smith', to_date('12/01/1980','MM/DD/YYYY') , to_date('2/7/2018'   ,'MM/DD/YYYY')   ,  'NEW YORK',to_date('2/12/2018', 'MM/DD/YYYY'),'NEW JERSEY', '');
COMMIT;

I want immediate visit information(visit_dt, visit_loc, ..) following a register date.

eg:

1234, John, Smith, 12/01/1980, 09/05/2017,  NEW YORK, 9/11/2017, NEW JERSEY
1234, John, Smith, 12/01/1980, 2/7/2018,  NEW YORK, 2/12/2018, NEW JERSEY

I tried with the below logic to sort the register dates and visit dates and then use lead to retrieve the following date and filter only for register dates. But, i am unable to add other fields as shown above..

 SELECT 
      dt, vst_type, register_dt, vst_dt
    FROM 
    (
      SELECT 
        id, dt, vst_type, 
        dt AS register_dt,
        ROW_NUMBER () OVER 
            ( 
              PARTITION BY id, dt ORDER BY
                CASE 
                  WHEN vst_type = 'REGISTER_DT' THEN 1 ELSE 2 END
            )
        AS vst_dt_rnum,
        LEAD(dt) OVER (PARTITION BY id ORDER BY dt) AS vst_dt
      FROM 
      (
        SELECT id, register_dt AS dt, 'REGISTER_DT' vst_type FROM tst_tbl
        UNION
        SELECT id, visit_dt AS dt, 'VISIT_DT' vst_type FROM tst_tbl
      )
    )
    WHERE vst_dt_rnum = 1 AND vst_type = 'REGISTER_DT'

kfinity · Answer 1 · 2018-06-29T20:16:01.597

I feel like you're overcomplicating things. Here's a way you can add an indicator if the visit date follows the register date by <30 days. If there's some reason this doesn't work for you, please edit your question and add more data (e.g. what should the output look like?)

select r.*, 
    case when (floor(visit_dt - register_dt) between 0 and 29) 
         then 'Y' else 'N' 
         end as less_than_30_days
from tst_tbl r;

But that's based on your sample output. Based on your query, it looks like you're trying to look for all visits which have any register date within the past 30 days (for that ID). If you'd prefer that instead, here's a simpler way to do it.

select v.*,
    (select nvl(max('Y'),'N')
      from tst_tbl r
      where r.id = v.id
        and (floor(v.visit_dt - r.register_dt) between 0 and 29))
      as reg_within_30_days
from tst_tbl v;

thanks for your response kfinity, i have edited my question. — G.Kol, Jun 29 '18 at 21:08

score 0 · Accepted Answer · answered Jun 30 '18 at 00:27

If I understand correctly, and assuming that the ID is a unique identifier, you want to group the rows by (ID, REGISTER_DT) and from each group to keep just the rows with VISIT_DT >= REGISTER_DT, and then from each group select the earliest (oldest) row by VISIT_DT. If so, something like this should work:

select (columns you want)
from   (
         select t.* 
              , row_number() over (partition by id, register_dt
                                   order by visit_dt) as rn
         from   tst_tbl t
         where  visit_dt >= register_dt
       )
where  rn = 1
;

thanks a million mathguy, this is the one i am looking for. thanks again — G.Kol, Jul 02 '18 at 18:49

score 0 · Answer 3 · answered Jun 30 '18 at 06:04

Try this

Select id, last_name, first_name, dob, register_dt, register_loc,
visit_dt
From (
  Select r.id, r.last_name, r.first_name, r.dob, r.register_dt, r.register_loc,
r.visit_dt, r.visit_loc, row_number() over (partition by r.register_dt order by r.visit_dt) r
From tst_tbl r
where register_dt < visit_dt
) x
Where x.r = 1

oracle sql to retrieve followup dates from a preceding date

3 Answers3