I need to generate all dates in between two given dates. Here is the problem statement:
Input:
START_DATE END_DATE
---------- ----------
01-FEB-16 03-FEB-16
01-FEB-16 04-FEB-16
01-FEB-16 05-FEB-16
01-FEB-16 03-FEB-16
11-FEB-16 14-FEB-16
Output (All dates between start date and end dates):
BETWEEN_START_AND_END
----------------------
01-FEB-16
02-FEB-16
03-FEB-16
04-FEB-16
05-FEB-16
11-FEB-16
12-FEB-16
13-FEB-16
14-FEB-16
Try;
WITH date_tbl AS ( --get (max end_date diff) group by START_DATE
SELECT
Trunc(START_DATE) Min_date,
max(Trunc(END_DATE)) - Trunc(START_DATE) diff
FROM tbl
GROUP BY Trunc(START_DATE)
),
num_tbl AS ( --data to join the table [0 , 1, 2, 3 ..... max(diff) + 1]
SELECT LEVEL - 1 lev
FROM dual
CONNECT BY LEVEL <= (SELECT Max(diff) + 1 FROM date_tbl)
)
SELECT DISTINCT Min_date + lev date_col --adding level to get all date
FROM num_tbl JOIN date_tbl
ON lev <= diff
ORDER BY Min_date + lev
Demo
with tbl(start_date, end_date) as (
select Date '2016-02-01', Date '2016-02-03' from dual union all
select Date '2016-02-01', Date '2016-02-04' from dual union all
select Date '2016-02-01', Date '2016-02-05' from dual union all
select Date '2016-02-01', Date '2016-02-03' from dual union all
select Date '2016-02-11', Date '2016-02-14' from dual
),
date_tbl AS ( --get max end_date group by START_DATE
SELECT
Trunc(START_DATE) Min_date,
max(Trunc(END_DATE)) - Trunc(START_DATE) diff
FROM tbl
GROUP BY Trunc(START_DATE)
),
num_tbl AS ( --data to join the table [0 ,1 , 2, 3 ..... max(diff) + 1]
SELECT LEVEL - 1 lev
FROM dual
CONNECT BY LEVEL <= (SELECT Max(diff) + 1 FROM date_tbl)
)
SELECT DISTINCT Min_date + lev date_col
FROM num_tbl JOIN date_tbl
ON lev <= diff
ORDER BY Min_date + lev
OutPut
DATE_COL
01.02.2016
02.02.2016
03.02.2016
04.02.2016
05.02.2016
11.02.2016
12.02.2016
13.02.2016
14.02.2016
Query:
with y(start_date, end_date, m) as (
select start_date, end_date, case when start_date > lag(end_date) over (order by start_date, end_date) then 1 else 0 end
from your_table
)
,z(start_date, end_date, group_number) as (
select start_date, end_date, sum(m) over (order by start_date, end_date) from y
)
,a(start_date, end_date, group_number) as (
select min(start_date), max(end_date), group_number
from z
group by group_number
)
select start_date + level - 1
from a
connect by start_date + level - 1 <= end_date
and prior group_number = group_number
and prior SYS_GUID() is not null
order by 1;
Example:
with x(start_date, end_date) as (
select date'2016-02-01', date'2016-02-03' from dual union all
select date'2016-02-01', date'2016-02-05' from dual union all
select date'2016-02-01', date'2016-02-03' from dual union all
select date'2016-02-11', date'2016-02-14' from dual union all
select date'2016-02-02', date'2016-02-07' from dual union all
select date'2016-02-02', date'2016-02-05' from dual union all
select date'2016-02-12', date'2016-02-15' from dual union all
select date'2016-02-11', date'2016-02-17' from dual union all
select date'2016-02-19', date'2016-02-21' from dual
)
,y(start_date, end_date, m) as (
select start_date, end_date, case when start_date > lag(end_date) over (order by start_date, end_date) then 1 else 0 end
from x
)
,z(start_date, end_date, group_number) as (
select start_date, end_date, sum(m) over (order by start_date, end_date) from y
)
,a(start_date, end_date, group_number) as (
select min(start_date), max(end_date), group_number
from z
group by group_number
)
select start_date + level - 1
from a
connect by start_date + level - 1 <= end_date
and prior group_number = group_number
and prior SYS_GUID() is not null
order by 1;
Result:
start_date + level - 1
----------------------
01-FEB-16
02-FEB-16
03-FEB-16
04-FEB-16
05-FEB-16
06-FEB-16
07-FEB-16
11-FEB-16
12-FEB-16
13-FEB-16
14-FEB-16
15-FEB-16
16-FEB-16
17-FEB-16
19-FEB-16
20-FEB-16
21-FEB-16
To reach best performance it would be better to merge overlapping intervals and then generate one row for each day among start and end dates of these intervals. The following query shows you how to do it.
SQL> with intervals as (
2 select 1 as interval_id,
3 to_date('01-FEB-16','dd-mon-yy') as start_date,
4 to_date('03-FEB-16','dd-mon-yy') as end_date
5 from dual
6 union all
7 select 2 as interval_id,
8 to_date('02-FEB-16','dd-mon-yy') as start_date,
9 to_date('04-FEB-16','dd-mon-yy') as end_date
10 from dual
11 union all
12 select 3 as interval_id,
13 to_date('03-FEB-16','dd-mon-yy') as start_date,
14 to_date('05-FEB-16','dd-mon-yy') as end_date
15 from dual
16 union all
17 select 4 as interval_id,
18 to_date('01-FEB-16','dd-mon-yy') as start_date,
19 to_date('04-FEB-16','dd-mon-yy') as end_date
20 from dual
21 union all
22 select 5 as interval_id,
23 to_date('11-FEB-16','dd-mon-yy') as start_date,
24 to_date('14-FEB-16','dd-mon-yy') as end_date
25 from dual
26 ), overlapping_intervals as (
27 select case
28 when prev_end is null
29 then idx
30 when start_date <= prev_end
31 then idx - lag(idx) over (order by start_date, interval_id)
32 else -idx
33 end as overlap_interval_id,
34 t.*
35 from (
36 select row_number() over (order by start_date, interval_id) as idx,
37 lag(end_date) over (order by start_date, interval_id) as prev_end,
38 i.*
39 from intervals i
40 ) t
41 order by start_date, interval_id
42 ), non_overlapping_intervals as (
43 select overlap_interval_id,
44 min(start_date) as start_date,
45 max(end_date) as end_date
46 from overlapping_intervals
47 group by overlap_interval_id
48 )
49 select /*+rule*/start_date + level-1 as day, i.*
50 from non_overlapping_intervals i
51 connect by overlap_interval_id = prior overlap_interval_id
52 and level < end_date-start_date+1
53 and prior dbms_random.value is not null -- <-- workaround to bypass ORA-01436 thrown by the DB
54 order by day
55 /
DAY OVERLAP_INTERVAL_ID START_DATE END_DATE
----------- ------------------- ----------- -----------
01/02/2016 1 01/02/2016 05/02/2016
02/02/2016 1 01/02/2016 05/02/2016
03/02/2016 1 01/02/2016 05/02/2016
04/02/2016 1 01/02/2016 05/02/2016
11/02/2016 -5 11/02/2016 14/02/2016
12/02/2016 -5 11/02/2016 14/02/2016
13/02/2016 -5 11/02/2016 14/02/2016
7 rows selected
SQL>
Try this... I let you change date format to fit yours...
with tbl(start_date,
end_date) as
(select date '2016-02-01'
,date '2016-02-03'
from dual
union all
select date '2016-02-01'
,date '2016-02-04'
from dual
union all
select date '2016-02-01'
,date '2016-02-05'
from dual
union all
select date '2016-02-01'
,date '2016-02-03'
from dual
union all
select date '2016-02-11'
,date '2016-02-14'
from dual)
select distinct start_date + level - 1
from tbl
connect by start_date + level - 1 <= end_date
order by 1;
will output
01/02/2016
02/02/2016
03/02/2016
04/02/2016
05/02/2016
11/02/2016
12/02/2016
13/02/2016
14/02/2016
