CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
KiMaN
KiMaN

Reputation: 319

Generate date range from two date columns

The source table it's following:

Id   start_date   end_date    field1
1    01/03/2019   07/03/2019  text1
2    10/04/2019   15/04/2019  text2

I would like to get this output:

Id date        field1
1  01/03/2019  text1
1  02/03/2019  text1
1  03/03/2019  text1
1  04/03/2019  text1
1  05/03/2019  text1
1  06/03/2019  text1
1  07/04/2019  text1
2  10/04/2019  text2
2  11/04/2019  text2
2  12/04/2019  text2
2  13/04/2019  text2
2  14/04/2019  text2
2  15/04/2019  text2

I must use a loop to populate this table ?

Thank you

Upvotes: 2

Views: 471

Answers (2)

Tomáš Záluský
Tomáš Záluský

Reputation: 12010

You can use recursive CTE, which is SQL standard. For example, the CTE expanded in following query works in both Oracle and Postgres (whole query works only in Oracle, will work in Postgres after removing from dual and adding with recursive):

with original as (
  select 1 as id, date '2019-03-01' as start_date, date '2019-03-07' as end_date, 'text1' as field from dual
  union
  select 2 as id, date '2019-04-10' as start_date, date '2019-04-15' as end_date, 'text2' as field from dual
), expanded (id, the_date, end_date, field) as (
  select id, start_date, end_date, field
  from original
  union all
  select original.id, expanded.the_date + 1, original.end_date, original.field
  from expanded
  join original on expanded.id = original.id and expanded.the_date + 1 <= original.end_date
)
select id, the_date, field
from expanded
order by id, the_date

See https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c0e49c2fff9e24fee8401e8ac9a16ceb

Note: due to Oracle CTE dating arithmetic bug, the query won't work in Oracle up to 11.2 (you obtain ORA-01841: (full) year must be between -4713 and +9999, and not be 0 error as dates go mistakenly downwards). If you have older Oracle version, it's probably better for you to use connect by, since using CTE requires some workaround of the bug (reverse direction or precomputed sequences), which is tricky as well. For newer Oracle version (or other vendors - if somebody finds this question) I recommend to prefer SQL-standard way.

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You may use a connect by query using the prior + sys_guid() trick

select id,start_date + level - 1 as "date", field1 from t 
connect by level <= end_date - start_date  + 1
    and prior id = id
and prior sys_guid() is not null;

DEMO

Upvotes: 6

Related Questions