0

I have the following example records in a table that contains records with time periods (Originally import data):

ID DateFrom DateTo Value
1 01.01.2021 03.01.2021 A
2 02.03.2021 06.03.2021 B
...

The data is imported as individual records into a separate table.

I would like to put the data records into the following form with a SELECT query in order to be able to check in the 2nd step whether all data were imported as a single data record:

ID DateFrom DateTo Value
1 01.01.2021 01.01.2021 A
1 02.01.2021 02.01.2021 A
1 03.01.2021 03.01.2021 A
2 02.03.2021 02.03.2021 B
2 03.03.2021 03.03.2021 B
2 04.03.2021 04.03.2021 B
2 05.03.2021 05.03.2021 B
2 06.03.2021 06.03.2021 B
..

Unfortunately, I have a knot in my head and cannot find a query approach.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
mirrorbook
  • 1

1 Answers1

0

I am sure the hierarchical query suits here. The problem though I still can't fit it in here without using distinct.

This query will work with assummption that "datefrom" and "dateto" columns are of DATE format. Replace "test_data" with table name you store dates in.

select td.id, 
       qq.day_date,
       value
  from test_data td
  join (select distinct id, 
                        datefrom + level - 1 day_date 
                   from test_data 
                connect by level <= (dateto - datefrom + 1)) qq
    on qq.id = td.id
 order by td.id, qq.day_date;

If datato and datafrom are just varchars, you may convert them to dates using to_date function.

ekochergin
  • 4,109
  • 2
  • 12
  • 19