CODEWITHSUNDEEP
sqloracle-database
Kacper
Kacper

Reputation: 4818

Hierarchical query for mulitiple groups

I'd like to generate every month end date between two dates but I need to do that for multiple groups. Simplified example is here:

select last_day(add_months(date '2015-01-01', level - 1)), 1 gr from dual
connect by level <= 12
union all
select last_day(add_months(date '2015-01-01', level - 1)), 2 gr from dual
connect by level <= 12;

Can it be done in single SQL query without union all as I have many groups.

I know I can do it with PL/SQL but just out of curiosity it is possible to do with single SQL statement?

I'd like query like this one:

with d as (
select date '2015-01-01' start_date, date '2015-12-01' end_date, 1 gr from dual
union all 
select date '2015-01-01' start_date, date '2015-12-01' end_date, 2 gr from dual
)
select last_day(add_months(start_date, level - 1)) from d
start with start_date = date '2015-01-01'
connect by level <= months_between(end_date, start_date);

but generating results as first query does not cross join

Upvotes: 1

Views: 43

Answers (2)

Kacper
Kacper

Reputation: 4818

I've found one more way but lateral is also not what I was aiming for. Kaushik's answer is what I was looking for.

with data as (
    select date '2015-01-01' start_date, date '2015-12-01' end_date, 1 as gr from dual
    union all
    select date '2015-01-01' start_date, date '2015-12-01' end_date, 2 as gr from dual
),
data_level as(
    select start_Date
        ,end_date
        ,gr
        ,months_between(end_date,start_date) + 1 as lvl
    from data)
select g from data_level,
lateral(select last_day(add_months(start_date,level - 1)) g
        from dual 
        connect by level <= lvl
);

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You can use the PRIOR and SYS_GUID() option

WITH d 
     AS (SELECT DATE '2015-01-01' start_date, 
                DATE '2015-12-01' end_date, 
                1                 gr 
         FROM   dual 
         UNION ALL 
         SELECT DATE '2015-01-01' start_date, 
                DATE '2015-12-01' end_date, 
                2                 gr 
         FROM   dual) 
SELECT gr, 
       last_day(add_months(start_date, LEVEL - 1)) AS dt 
FROM   d 
START WITH start_date = DATE '2015-01-01' 
CONNECT BY LEVEL <= months_between(end_date, start_date) 
           AND PRIOR gr = gr 
                     AND PRIOR sys_guid() IS NOT NULL 
ORDER  BY gr, 
          dt 

| GR |                   DT |
|----|----------------------|
|  1 | 2015-01-31T00:00:00Z |
|  1 | 2015-02-28T00:00:00Z |
|  1 | 2015-03-31T00:00:00Z |
|  1 | 2015-04-30T00:00:00Z |
|  1 | 2015-05-31T00:00:00Z |
|  1 | 2015-06-30T00:00:00Z |
|  1 | 2015-07-31T00:00:00Z |
|  1 | 2015-08-31T00:00:00Z |
|  1 | 2015-09-30T00:00:00Z |
|  1 | 2015-10-31T00:00:00Z |
|  1 | 2015-11-30T00:00:00Z |
|  2 | 2015-01-31T00:00:00Z |
|  2 | 2015-02-28T00:00:00Z |
|  2 | 2015-03-31T00:00:00Z |
|  2 | 2015-04-30T00:00:00Z |
|  2 | 2015-05-31T00:00:00Z |
|  2 | 2015-06-30T00:00:00Z |
|  2 | 2015-07-31T00:00:00Z |
|  2 | 2015-08-31T00:00:00Z |
|  2 | 2015-09-30T00:00:00Z |
|  2 | 2015-10-31T00:00:00Z |
|  2 | 2015-11-30T00:00:00Z |

Demo

Upvotes: 3

Related Questions