I have range of dates:

| date |
| -------- | 
| 1/1/2022 |
| 2/1/2022 |
| 3/1/2022 |
| 5/1/2022 |
| 6/1/2022 |
| 7/1/2022 |
| 8/1/2022 |
| 10/1/2022 |

I want to get the dates that are not included between these dates, in this case 4/1 and 9/1, I want the count of these dates, in this case 2, so I want the count of dates that do not exist between a specific range of dates, how can I achieve that?

CodePudding user response：

select (max(date) - min(date)   1) - count(distinct date)
from table_name

https://dbfiddle.uk/cSKZloYA

(max(date) - min(date) 1) will give the total number of days in the range.

count(distinct date) will be the number of existing (different) days in the table.

The difference between these is the number of non-existing days.

Note: date is a reserved word, so if it's the actual column name, it has to be delimited as "date". (https://en.wikipedia.org/wiki/List_of_SQL_reserved_words)

CodePudding user response：

You can use the LAG analytic function to find the previous date and then work out the number of days difference and if it is more than 1 then you have that many missing days:

SELECT SUM(missing_dates) AS num_missing
FROM   (
  SELECT GREATEST("DATE" - LAG("DATE") OVER (ORDER BY "DATE") - 1, 0)
           AS missing_dates
  FROM   table_name
);

Which, for the sample data:

CREATE TABLE table_name ("DATE") AS
SELECT DATE '2020-01-01' FROM DUAL UNION ALL
SELECT DATE '2020-01-02' FROM DUAL UNION ALL
SELECT DATE '2020-01-03' FROM DUAL UNION ALL
SELECT DATE '2020-01-05' FROM DUAL UNION ALL
SELECT DATE '2020-01-06' FROM DUAL UNION ALL
SELECT DATE '2020-01-07' FROM DUAL UNION ALL
SELECT DATE '2020-01-08' FROM DUAL UNION ALL
SELECT DATE '2020-01-10' FROM DUAL;

Outputs:

fiddle