2

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
Simeon Pilgrim
  • 22,906
  • 3
  • 32
  • 45
YoungboyVBA
  • 197
  • 7
  • 1
    Duplication is against the principle of a relational database. Could you use a second table to define the periods, or even define them dynamically? –  Mar 14 '22 at 20:28
  • Good point. I guess I could figure out how to define them dynamically but I can't see a away to do so referencing 'Number of periods' in this table... – YoungboyVBA Mar 14 '22 at 20:32

1 Answers1

3

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:

ID LOCATIONTYPE TRIPEND NUMPERIODS RANGE_TIME
1244 Commercial 2021-12-31 09:00:00.000 2 2021-12-31 09:00:00.000
1244 Commercial 2021-12-31 09:00:00.000 2 2021-12-31 09:30:00.000
1244 Commercial 2021-12-31 09:00:00.000 2 2021-12-31 10:00:00.000
1244 Residential 2021-12-31 10:30:00.000 1 2021-12-31 10:30:00.000
1244 Residential 2021-12-31 10:30:00.000 1 2021-12-31 11:00:00.000
1298 Residential 2022-01-02 05:30:00.000 8 2022-01-02 05:30:00.000
1298 Residential 2022-01-02 05:30:00.000 8 2022-01-02 06:00:00.000
1298 Residential 2022-01-02 05:30:00.000 8 2022-01-02 06:30:00.000
1298 Residential 2022-01-02 05:30:00.000 8 2022-01-02 07:00:00.000
1298 Residential 2022-01-02 05:30:00.000 8 2022-01-02 07:30:00.000
1298 Residential 2022-01-02 05:30:00.000 8 2022-01-02 08:00:00.000
1298 Residential 2022-01-02 05:30:00.000 8 2022-01-02 08:30:00.000
1298 Residential 2022-01-02 05:30:00.000 8 2022-01-02 09:00:00.000
1298 Residential 2022-01-02 05:30:00.000 8 2022-01-02 09:30:00.000
1298 Residential 2022-01-03 05:30:00.000 6 2022-01-03 05:30:00.000
1298 Residential 2022-01-03 05:30:00.000 6 2022-01-03 06:00:00.000
1298 Residential 2022-01-03 05:30:00.000 6 2022-01-03 06:30:00.000
1298 Residential 2022-01-03 05:30:00.000 6 2022-01-03 07:00:00.000
1298 Residential 2022-01-03 05:30:00.000 6 2022-01-03 07:30:00.000
1298 Residential 2022-01-03 05:30:00.000 6 2022-01-03 08:00:00.000
1298 Residential 2022-01-03 05:30:00.000 6 2022-01-03 08:30:00.000
Simeon Pilgrim
  • 22,906
  • 3
  • 32
  • 45
  • Thank you for this. What if I had a much larger dataset though? Would I still need to pass it all through like you've done on lines 3-6? – YoungboyVBA Mar 14 '22 at 23:49
  • ah, the `data` CTE was purely for my simplicity of avoid creating a table, and inserting rows. You should avoid writing production SQL with `SELECT FROM VALUES` for real data.. (except for setting up small tables) – Simeon Pilgrim Mar 15 '22 at 00:23