sql find gaps in date ranges 1

sql find gaps in date ranges

-- Range of missing dates d in table t1
SELECT * FROM (
    SELECT 
  		trunc(d) AS d,
        (SELECT min(trunc(d)) FROM t1 t2 WHERE trunc(t2.d) > trunc(t1.d)) next_d
    FROM t1
) WHERE d <> next_d - 1;

Here is what the above code is Doing:
1. The inner query is selecting the minimum date from t1 that is greater than the current date.
2. The outer query is selecting all dates from t1 that are not equal to the next date minus 1.

Similar Posts