SELECT id ,MAX(date2) last_date FROM (SELECT id,date2,last_day,DATEDIFF(DAY,last_day,date2) gap FROM (SELECT id,date2, MAX(date2) OVER(PARTITION BY id order by date2 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) last_day FROM DS) TMD )TMD1 GROUP BY id HAVING MAX(gap)>=5;