Generating multiple rows from one row in SQL based on a column

Question

I have a table with these five columns:

The ID is the PI here. BEGIN_WINDOW and END_WINDOW are TIMESTAMP columns. The DURATION_DAYS_RUP is calculated by dividing DURATION_HRS by 24 and rounding up.

What I'm trying to do is based on the DURATION_DAYS_RUP, I need to create multiple rows.

• If that value is 1, then the row created is just the same row with same ID, BEGIN_WINDOW and END_WINDOW.
• If it's greater than 1, for ex. 2, two rows would be created - one row where the ID is the same, BEGIN_WINDOW is the value from the original row, and END_WINDOW is 24 hrs + BEGIN_WINDOW and the second row would be the same ID, BEGIN_WINDOW is the END_WINDOW of that first row, and END_WINOW is this row's BEGIN_WINDOW + 24 hours.

See the example below:

I've researched a lot but can't seem to find the trick to doing this. If anyone has an idea, would be greatly appreciated!

Answer 1

You could use Teradata's EXPAND ON syntax:

SELECT x.ID, BEGIN(pd) as BEGIN_WINDOW, BEGIN(pd) + INTERVAL '24' HOUR as END_WINDOW
FROM mytable x
EXPAND ON PERIOD(x.BEGIN_WINDOW, x.END_WINDOW) AS pd
BY INTERVAL '24' HOUR;

Answer 2

You can use a recursive query:

with recursive cte (id, begin_window, end_window, duration_days_rup) as (
    select 
        id, 
        begin_window, 
        case when duration_days_rup = 1 then end_window else begin_window + interval '1' day end,
        duration_days_rup - 1
    from mytable 
    union all
    select 
        id, 
        begin_window + interval '1' day,
        case when duration_days_rup = 1 then end_window else end_window + interval '1' day end, 
        duration_days_rup - 1
    from cte
    where duration_days_rup > 0
)
select id, begin_window, end from cte

Looking at your query, I doubt that you really need the duray_days_rup column, which is derived information. We could use straight date comparisons. I think the logic you want is:

with recursive cte (id, begin_window, end_window, real_end_window) as (
    select 
        id, 
        begin_window, 
        least(end_window, begin_window + interval '1' day),
        end_window
    from mytable 
    union all
    select 
        id, 
        begin_window + interval '1' day,
        least(real_end_window, end_window + interval '1' day),
        real_end_window
    from cte
    where begin_window + interval '1' day > real_end_window
)
select id, begin_window, end from cte