QUERY TO GENERATE A SERIE BETWEEN TWO DATES AND INTEGRATE THEM IN A TABLE

Question

I have this problem: I have a table with columns that are an article and a date from and a date to (which is a time interval), for example:

ITEM   | DATE_FROM  | DATE_TO
IT_01  | 01/01/2021 | 07/01/2021

I would like to generate a query that generates a new column in which the interval from the date from and the date to is specified, generating the rows with the article of that interval, leaving the result like this:

ITEM    |  DATE_FROM    |    DATE_TO      |     DATE_PER_DAY
--------+---------------+-----------------+------------------    
IT_01   |  01/01/2021   |   07/01/2021    |   *01/01/2021*
IT_01   |  01/01/2021   |   07/01/2021    |   *02/01/2021*
IT_01   |  01/01/2021   |   07/01/2021    |   *03/01/2021*
IT_01   |  01/01/2021   |   07/01/2021    |   *04/01/2021*
IT_01   |  01/01/2021   |   07/01/2021    |   *05/01/2021*
IT_01   |  01/01/2021   |   07/01/2021    |   *06/01/2021*
IT_01   |  01/01/2021   |   07/01/2021    |   *07/01/2021*

Thanks

score 1 · Accepted Answer · answered Oct 27 '21 at 10:23

It is a row generator you need:

SQL> with test (item, date_from, date_to) as
  2    (select 'IT_01', date '2021-01-01', date '2021-01-07' from dual)
  3  select item, date_from, date_to, date_from + level - 1 as date_per_day
  4  from test
  5  connect by level <= date_to - date_from + 1
  6  order by date_per_day;

ITEM  DATE_FROM  DATE_TO    DATE_PER_D
----- ---------- ---------- ----------
IT_01 01.01.2021 07.01.2021 01.01.2021
IT_01 01.01.2021 07.01.2021 02.01.2021
IT_01 01.01.2021 07.01.2021 03.01.2021
IT_01 01.01.2021 07.01.2021 04.01.2021
IT_01 01.01.2021 07.01.2021 05.01.2021
IT_01 01.01.2021 07.01.2021 06.01.2021
IT_01 01.01.2021 07.01.2021 07.01.2021

7 rows selected.

SQL>

QUERY TO GENERATE A SERIE BETWEEN TWO DATES AND INTEGRATE THEM IN A TABLE

1 Answers1