generating range of dates table with rownum

Question

I am trying to create a report on a client level which will take information like start_date and end_date from clients table where client_id is specified. After that the idea is to generate table of dates that can warry in range, depending on the clients start_date and end_date.

My question: Is it possible to create table with range of dates in the moment of query execution (dynamically)?

Or: Can you make sequence of numbers like you would do with for loop without previously defining temp table or assistant procedure?

For example (oracle SQL):

SELECT  *
FROM    (   SELECT  1   A
            FROM    DUAL    )   A

LEFT JOIN    (  SELECT  10  B
                FROM    DUAL    )   B ON B.B = A.A + ROWNUM

Marmite Bomber · Accepted Answer · 2022-01-24T20:51:35.300

You may use recursive CTE for this purpose.

You only need to pass the start date and the stop date in the anchor select of the recursive CTE

Example

with cal (dt, stop) as (
select date'2022-01-01', date'2022-01-05' from dual
union all
select dt+1, stop from cal
where dt< stop)
select dt from cal;

DT                 
-------------------
01.01.2022 00:00:00
02.01.2022 00:00:00
03.01.2022 00:00:00
04.01.2022 00:00:00
05.01.2022 00:00:00

score 1 · Answer 2 · answered Jan 23 '22 at 10:33

1

You could use the connect by level syntax:

SELECT     TO_DATE('01/02/03', 'DD/MM/RR') + LEVEL - 1 -- Some arbitrary start date
FROM       dual
CONNECT BY LEVEL <= 10 -- Arbitrarily produce 10 rows

answered Jan 23 '22 at 10:33

Mureinik

297,002
52
306
350

score 0 · Answer 3 · answered Jan 23 '22 at 12:37

Looks like an ordinary hierarchical query to me.

SQL> with cal (dt, stop) as
  2    (select date'2022-01-01', date'2022-01-05' from dual)
  3  select dt + level - 1 datum
  4  from cal
  5  connect by level <= stop - dt + 1;

DATUM
----------
01.01.2022
02.01.2022
03.01.2022
04.01.2022
05.01.2022

SQL>

generating range of dates table with rownum

3 Answers3

Linked