Select records that are upto 2 years old from the max(effectiveDate) from a table in Oracle

Question

I have a query which returns ID, effectiveDate, and many other columns. Now I need to filter this result to get the records that are upto 2 years old from the most recent effectivedate for each ID.

Table has data similar to the following

ID |       EffectiveDate   | Col3 |     Col4 | ........
____________________________________________________________
1  |       2020-09-30      |
1  |       2019-09-30      |
1  |       2018-09-30      |
1  |       2018-03-31      |
1  |       2017-09-30      |
2  |       2019-03-31      |
2  |       2018-03-31      |
2  |       2017-03-31      |
3  |       2015-06-30      |
3  |       2015-03-31      |
3  |       2014-12-31      |
3  |       2012-06-30      |

I want the output to be

ID |       EffectiveDate   | Col3      Col4 ........
____________________________________________________________
1  |       2020-09-30      |
1  |       2019-09-30      |
1  |       2018-09-30      |
2  |       2019-03-31      |
2  |       2018-03-31      |
2  |       2017-03-31      |
3  |       2015-06-30      |
3  |       2015-03-31      |
3  |       2014-12-31      |

I have tried the following

select A.* from table A
inner join
(select ID, col3, col4, max(effectivedate) as MaxDate
from table A
group by ID, col3, Col4 ) B
on A.ID = B.ID
where (B.Maxdate - A.effectiveDate) < 740;

but this query returns all the records, not filtering anything. Also this is throwing a cross join result. Please help!

Answer 1

Use window functions. Your text specifies the maximum for each id:

select a.*
from (select a.*, max(efectivedate) over (partition by id) as max_ed
      from a
     ) a
where effectivedate >= max_ed - interval '740' day;

(Note: 730 would make more sense.)

If you want this by id/col3/col4 as the query suggests then use all three in the partition by.

Answer 2

If it is "2 years", then let it be 2 years, not 740 days.

SQL> with test (id, efdate) as
  2    (select 1, date '2020-09-30' from dual union all
  3     select 1, date '2019-09-30' from dual union all
  4     select 1, date '2018-09-30' from dual union all
  5     select 1, date '2017-09-30' from dual union all
  6     select 2, date '2019-03-31' from dual union all
  7     select 2, date '2018-03-31' from dual union all
  8     select 2, date '2017-03-31' from dual
  9    ),
 10  tmp as
 11    (select id, efdate, max(efdate) over (partition by id) maxdate
 12     from test
 13    )
 14  select t.id, t.efdate
 15  from test t join tmp m on t.id = m.id and t.efdate = m.efdate
 16  where t.efdate >= add_months(m.maxdate, -12 * 2)
 17  order by t.id, t.efdate desc;

        ID EFDATE
---------- ----------
         1 2020-09-30
         1 2019-09-30
         1 2018-09-30
         2 2019-03-31
         2 2018-03-31
         2 2017-03-31

6 rows selected.

SQL>

Answer 3

This should work as well. All condition is in the WHERE clause. It takes the MAX of dates for each group ID and does the subtraction for each record then gets the result as you required.

select * from tbl A
where (select max(B.effectivedate) from tbl B where A.id = B.id) - A.effectivedate < 740
order by A.id asc, A.effectivedate desc;