CODEWITHSUNDEEP
plsqloracle11g
Abhishek
Abhishek

Reputation: 680

Generate the dates in between Start and End Dates

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

Upvotes: 4

Views: 220

Answers (4)

BartmanDilaw
BartmanDilaw

Reputation: 415

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

Upvotes: 0

Alessandro Rossi
Alessandro Rossi

Reputation: 2450

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>

Upvotes: 0

Praveen
Praveen

Reputation: 9335

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

Upvotes: 1

Noel
Noel

Reputation: 10525

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

Upvotes: 0

Related Questions