Sample Data:

ID      Location Type   Trip End            Number of periods
1298    Residential     02/01/2022 05:30    48
1298    Residential     03/01/2022 05:30    6
1244    Commercial      31/12/2021 09:00    2
1244    Residential     31/12/2021 10:30    1

Essentially, I want to duplicate the row according to the number in column 'number of periods.' Also, to add 30 mins to each new duplicate row. So the new second row would be:

1298    Residential     02/01/2022 06:00    48

CodePudding user response：

So this is solved in snowflake using a a table generator (which have a fixed input) combined with ROW_NUMBER and then using DATEADD

I changed 48 -> 8 to make the output less ugly.

WITH data(ID, LocationType, TripEnd, NumPeriods) as (
    select COLUMN1, COLUMN2, TO_TIMESTAMP_NTZ(COLUMN3, 'dd/mm/yyyy hh:mi'), COLUMN4 from values
        (1298, 'Residential','02/01/2022 05:30',8),
        (1298, 'Residential','03/01/2022 05:30',6),
        (1244, 'Commercial','31/12/2021 09:00',2),
        (1244, 'Residential','31/12/2021 10:30',1) 
), set_of_nums as (
    SELECT row_number() over (order by null)-1 as rn
    FROM table(generator(ROWCOUNT => 1000))
)
select d.*
    ,dateadd(minute, 30 * s.rn, TripEnd) as range_time
FROM DATA as d
JOIN set_of_nums as s ON d.NumPeriods >= s.rn
ORDER BY 1,3,5
;

which gives:

CodePudding user response：

You can create some stored procedures (I've used SQL Server): --------------- CREATING SAMPLE DATA ---------------

drop table if exists a1;
drop table if exists a2;
create table a1 (
    rowNum int
    ,ID int
    ,Location_Type varchar(25)
    , Trip_End datetime
    , Number_Of_Periods int
);
create table a2 (
    ID int
    ,Location_Type varchar(25)
    , Trip_End datetime
    , Number_Of_Periods int
);

insert into a1
values 
(1, 1298, 'Residential', '2022-01-02 05:30:00', 48),
(2, 1244, 'Commercial', '2021-12-31 09:00:00', 2)

--------------- CREATING STORED PROCEDURE 1 ---------------

CREATE OR ALTER PROCEDURE InsertA2 @id int, @locationType varchar(15) , @tripEnd datetime, @NumberPeriods int
as
INSERT INTO a2
VALUES(@id ,@locationType , @tripEnd , @NumberPeriods );

--------------- CREATING STORED PROCEDURE 2 ---------------

CREATE OR ALTER PROCEDURE completeTask
as
    DECLARE @rowNumber int = 1
    DECLARE @counterA int = 1
    DECLARE @counterB int = (select max(rowNum) from a1)
    DECLARE @ID int = (select ID from a1 where rowNum = @rowNumber)
    DECLARE @Location_Type varchar(25) = (select Location_Type from a1 where rowNum = @rowNumber)
    DECLARE @Trip_End datetime = (select Trip_End from a1 where rowNum = @rowNumber)
    DECLARE @Number_Of_Periods int = (select Number_Of_Periods from a1 where rowNum = @rowNumber)

    WHILE @rowNumber <= @counterB
    BEGIN
        WHILE  @counterA <= @Number_Of_Periods
        BEGIN
            EXEC InsertA2 @ID, @Location_Type, @Trip_End, @Number_Of_Periods
            SET @counterA = @counterA   1
            SET @Trip_End = DATEADD(mi, 30, @Trip_End)
        END
        SET @rowNumber = @rowNumber  1
        SET @counterA = 1
        SET @ID = (select ID from a1 where rowNum = @rowNumber)
        SET @Location_Type = (select Location_Type from a1 where rowNum = @rowNumber)
        SET @Trip_End = (select Trip_End from a1 where rowNum = @rowNumber)
        SET @Number_Of_Periods = (select Number_Of_Periods from a1 where rowNum = @rowNumber)
    END

--------------- EXECUTING ---------------

exec completeTask
select * from a2 order by id

--------------- OUTPUT---------------

1244    Commercial  2021-12-31 09:00:00.000 2
1244    Commercial  2021-12-31 09:30:00.000 2
1298    Residential 2022-01-02 05:30:00.000 48
1298    Residential 2022-01-02 06:00:00.000 48
1298    Residential 2022-01-02 06:30:00.000 48
1298    Residential 2022-01-02 07:00:00.000 48
1298    Residential 2022-01-02 07:30:00.000 48
1298    Residential 2022-01-02 08:00:00.000 48
1298    Residential 2022-01-02 08:30:00.000 48
1298    Residential 2022-01-02 09:00:00.000 48
1298    Residential 2022-01-02 09:30:00.000 48
1298    Residential 2022-01-02 10:00:00.000 48
1298    Residential 2022-01-02 10:30:00.000 48
1298    Residential 2022-01-02 11:00:00.000 48
1298    Residential 2022-01-02 11:30:00.000 48
1298    Residential 2022-01-02 12:00:00.000 48
1298    Residential 2022-01-02 12:30:00.000 48
1298    Residential 2022-01-02 13:00:00.000 48
1298    Residential 2022-01-02 13:30:00.000 48
1298    Residential 2022-01-02 14:00:00.000 48
1298    Residential 2022-01-02 14:30:00.000 48
1298    Residential 2022-01-02 15:00:00.000 48
1298    Residential 2022-01-02 15:30:00.000 48
1298    Residential 2022-01-02 16:00:00.000 48
1298    Residential 2022-01-02 16:30:00.000 48
1298    Residential 2022-01-02 17:00:00.000 48
1298    Residential 2022-01-02 17:30:00.000 48
1298    Residential 2022-01-02 18:00:00.000 48
1298    Residential 2022-01-02 18:30:00.000 48
1298    Residential 2022-01-02 19:00:00.000 48
1298    Residential 2022-01-02 19:30:00.000 48
1298    Residential 2022-01-02 20:00:00.000 48
1298    Residential 2022-01-02 20:30:00.000 48
1298    Residential 2022-01-02 21:00:00.000 48
1298    Residential 2022-01-02 21:30:00.000 48
1298    Residential 2022-01-02 22:00:00.000 48
1298    Residential 2022-01-02 22:30:00.000 48
1298    Residential 2022-01-02 23:00:00.000 48
1298    Residential 2022-01-02 23:30:00.000 48
1298    Residential 2022-01-03 00:00:00.000 48
1298    Residential 2022-01-03 00:30:00.000 48
1298    Residential 2022-01-03 01:00:00.000 48
1298    Residential 2022-01-03 01:30:00.000 48
1298    Residential 2022-01-03 02:00:00.000 48
1298    Residential 2022-01-03 02:30:00.000 48
1298    Residential 2022-01-03 03:00:00.000 48
1298    Residential 2022-01-03 03:30:00.000 48
1298    Residential 2022-01-03 04:00:00.000 48
1298    Residential 2022-01-03 04:30:00.000 48
1298    Residential 2022-01-03 05:00:00.000 48