SELECT MAX(DT) FROM ( SELECT '2018-08-10' DT, 'A' AS C2 UNION ALL SELECT '2018-08-11', 'A' UNION ALL SELECT '2018-08-12', 'B' UNION ALL SELECT '2018-08-13', 'B' UNION ALL SELECT '2018-08-14', 'A' ) WHERE C2 = 'A' AND DT < (SELECT MIN(DT) FROM ( SELECT '2018-08-10' DT, 'A' AS C2 UNION ALL SELECT '2018-08-11', 'A' UNION ALL SELECT '2018-08-12', 'B' UNION ALL SELECT '2018-08-13', 'B' UNION ALL SELECT '2018-08-14', 'A' ) WHERE C2 = 'B' )
WITH t AS ( SELECT '2018-08-10' dt, 'A' cd UNION ALL SELECT '2018-08-11', 'A' UNION ALL SELECT '2018-08-12', 'B' UNION ALL SELECT '2018-08-13', 'B' UNION ALL SELECT '2018-08-14', 'A' ) SELECT MIN(dt) sdt , MAX(dt) edt , cd FROM (SELECT dt, cd , ROW_NUMBER() OVER(ORDER BY dt) - ROW_NUMBER() OVER(PARTITION BY cd ORDER BY dt) grp FROM t ) a GROUP BY cd, grp ORDER BY sdt ;