SQL Teradata – Comparing row values but skipping some rows

Lets say I have these date values in my table:

#1 2019-01-01
#2 2019-02-01
#3 2019-03-01
#4 2019-05-01
#5 2019-06-01
#6 2019-06-15
#7 2019-07-01

I need to keep only the dates that are 2 months appart from the previous “good” one.

So, I start with:

#1 is the first one, I keep it as a good one.

#2 is only one month appart, so not good.

#3 is two months appart from #1 (I ignore #2 because it was not good).

#4 is two months appart from #3, so I keep it

#5 is not good because it is only one month from #4

#6 is not good because it is only one and a half month from #4 (#5 is ignored because it was not good).

#7 is good because it is two months appart from #4 which was the last good one.

Is there an easy, clean way to do that?

I started with a dense_rank() over and compare them with the previous rank but I fail to figure out how to ignore the bad dates.

Answer

This is an iterative process. You can solve it using a recursive CTE. Given that you are dealing with dates and months, your data is not too large, so this might be a reasonable solution.

Date arithmetic varies significantly among databases. The following is the idea:

with recursive t as (
      select t.*, row_number() over (order by datecol) as seqnum
      from mytable t
     ) t,
     cte as (
      select datecol, seqnum, datecol as refdate
      from t
      where seqnum = 1
      union all
      select t.datecol, t.segnum,
             (case when t.datecol >= add_months(cte.refdate, 2)
                   then t.datecol else cte.refdate
              end)
      from cte join
           t
           on t.seqnum = cte.seqnum + 1
     )
select distinct refdate
from cte;