How to select dates from dual, but with joined data?

If you have denormalised date it's quite simple:

with bas as (
    select 1 id_other_table, to_date('2020-01-05', 'YYYY-MM-DD') date_from, to_date('2020-01-06', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-06', 'YYYY-MM-DD') date_from, to_date('2020-01-07', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-07', 'YYYY-MM-DD') date_from, to_date('2020-01-08', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-10', 'YYYY-MM-DD') date_from, to_date('2020-01-11', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-11', 'YYYY-MM-DD') date_from, to_date('2020-01-12', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-12', 'YYYY-MM-DD') date_from, to_date('2020-01-13', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 2 id_other_table, to_date('2020-01-10', 'YYYY-MM-DD') date_from, to_date('2020-01-11', 'YYYY-MM-DD') date_to, 'my' some_info from dual
    union all select 2 id_other_table, to_date('2020-01-11', 'YYYY-MM-DD') date_from, to_date('2020-01-12', 'YYYY-MM-DD') date_to, 'my' some_info from dual
    union all select 2 id_other_table, to_date('2020-01-12', 'YYYY-MM-DD') date_from, to_date('2020-01-13', 'YYYY-MM-DD') date_to, 'my' some_info from dual
    union all select 3 id_other_table, to_date('2020-01-20', 'YYYY-MM-DD') date_from, to_date('2020-01-21', 'YYYY-MM-DD') date_to, 'friend' some_info from dual
    union all select 3 id_other_table, to_date('2020-01-21', 'YYYY-MM-DD') date_from, to_date('2020-01-22', 'YYYY-MM-DD') date_to, 'friend' some_info from dual
    union all select 3 id_other_table, to_date('2020-01-22', 'YYYY-MM-DD') date_from, to_date('2020-01-23', 'YYYY-MM-DD') date_to, 'friend' some_info from dual)
, ad as (select trunc(sysdate,'YYYY') -1 + level all_dates from dual connect by level <= 31)
select distinct some_info,all_dates from bas,ad where (some_info,all_dates) not in (select some_info,date_from from bas)

If you have longer date ranges or mind of the time the query needs another solution is helpful. But that is harder to debug. Because it's quite hard to get the orange time slot


Select whole range using connect by generator. Join your table partitioned by id.

select date_from, nvl(date_to, date_from +1) date_to, id_othertable, some_info
  from (
    select date '2020-01-01' + level - 1 as date_from 
      from dual 
      connect by level <= date '2020-01-31' - date '2020-01-01' ) gen 
  natural left join some_dates partition by (id_othertable) 

sqlfiddle


If you want the dates per id that are not in the database then you can use the LEAD analytic function:

WITH dates ( id, date_from, date_to ) AS (
  SELECT id_othertable,
         DATE '2020-01-01',
         MIN( date_from )
  FROM   some_dates
  WHERE  date_to > DATE '2020-01-01'
  AND    date_from < ADD_MONTHS( DATE '2020-01-01', 1 )
  GROUP BY id_othertable
UNION ALL
  SELECT id_othertable,
         date_to,
         LEAD( date_from, 1, ADD_MONTHS( DATE '2020-01-01', 1 ) )
           OVER ( PARTITION BY id_othertable ORDER BY date_from )
  FROM   some_dates
  WHERE  date_to > DATE '2020-01-01'
  AND    date_from < ADD_MONTHS( DATE '2020-01-01', 1 )
)
SELECT id,
       date_from,
       date_to
FROM   dates
WHERE  date_from < date_to
ORDER BY id, date_from;

so for the test data:

CREATE TABLE some_dates ( id_othertable, date_from, date_to, some_info ) AS
SELECT 1, DATE '2020-01-05', DATE '2020-01-06', 'hello1' FROM DUAL UNION ALL
SELECT 1, DATE '2020-01-06', DATE '2020-01-07', 'hello2' FROM DUAL UNION ALL
SELECT 1, DATE '2020-01-07', DATE '2020-01-08', 'hello3' FROM DUAL UNION ALL
SELECT 1, DATE '2020-01-10', DATE '2020-01-13', 'hello4' FROM DUAL UNION ALL
SELECT 2, DATE '2020-01-10', DATE '2020-01-13', 'my'     FROM DUAL UNION ALL
SELECT 3, DATE '2020-01-20', DATE '2020-01-23', 'friend' FROM DUAL UNION ALL
SELECT 4, DATE '2019-12-31', DATE '2020-01-05', 'before' FROM DUAL UNION ALL
SELECT 4, DATE '2020-01-30', DATE '2020-02-02', 'after'  FROM DUAL UNION ALL
SELECT 5, DATE '2019-12-31', DATE '2020-01-10', 'only_before' FROM DUAL UNION ALL
SELECT 6, DATE '2020-01-15', DATE '2020-02-01', 'only_after'  FROM DUAL UNION ALL
SELECT 7, DATE '2019-12-31', DATE '2020-02-01', 'exlude_all'  FROM DUAL;

this outputs:

ID | DATE_FROM  | DATE_TO   
-: | :--------- | :---------
 1 | 2020-01-01 | 2020-01-05
 1 | 2020-01-08 | 2020-01-10
 1 | 2020-01-13 | 2020-02-01
 2 | 2020-01-01 | 2020-01-10
 2 | 2020-01-13 | 2020-02-01
 3 | 2020-01-01 | 2020-01-20
 3 | 2020-01-23 | 2020-02-01
 4 | 2020-01-05 | 2020-01-30
 5 | 2020-01-10 | 2020-02-01
 6 | 2020-01-01 | 2020-01-15

db<>fiddle here

If you want the days before then filter on:

WHERE day_from = DATE '2020-01-01'

and, similarly, if you want the days after then filter on:

WHERE day_to = ADD_MONTHS( DATE '2020-01-01', 1 )

If you want to specify the start date and number of months duration then use named bind parameters:

WITH dates ( id, date_from, date_to ) AS (
  SELECT id_othertable,
         :start_date,
         MIN( date_from )
  FROM   some_dates
  WHERE  date_to > :start_date
  AND    date_from < ADD_MONTHS( :start_date, :number_months )
  GROUP BY id_othertable
UNION ALL
  SELECT id_othertable,
         date_to,
         LEAD( date_from, 1, ADD_MONTHS( :start_date, :number_months ) )
           OVER ( PARTITION BY id_othertable ORDER BY date_from )
  FROM   some_dates
  WHERE  date_to > :start_date
  AND    date_from < ADD_MONTHS( :start_date, :number_months )
)
SELECT id,
       date_from,
       date_to
FROM   dates
WHERE  date_from < date_to
ORDER BY id, date_from;

Tags:

Sql

Oracle