How to loop through rows to create new dates in Google Bigquery

Question

I'm trying to duplicates a row while changing the end date based on the difference between the current start and end date. The end date also has to be the last day of the month For example Data I have

Data I need to end up with

This is what i've tried so far: current code

I know I need a do loop probably a nested do loop and maybe an execute immediate but I can't figure it out. Can anyone help?

Answer 1

I briefly tell you the algorithm of achieving this:

1. create a column (possible_end_date) of all possible end dates (e.g. last day of all months from 2020 to 2022)
2. full join this column with your start_date column (e.g. if you have 3 start dates and 36 possible end dates the you have a 3 * 36 rows table after doing that)
3. left join the result with your data on start_date columns and after that you will have a table with three columns: start_date, end_date, and possible_end_date
4. filter rows where start_date < possible_end_date and also possible_end_date < end_date

Answer 2

You may use GENERATE_DATE_ARRAY() function to create an array of dates for every start-end dates, then flatten the array with UNNEST() function + CROSS JOIN to merge it back to the original table:

WITH sample_table AS (
SELECT DATE '2020-09-11' as file_date, DATE '2020-09-01' as start_date, DATE '2021-04-30' as end_date, 7 as total_increment UNION ALL
SELECT DATE '2019-12-10' as file_date, DATE '2019-12-01' as start_date, DATE '2020-03-31' as end_date, 3 as total_increment UNION ALL
SELECT DATE '2021-05-08' as file_date, DATE '2021-05-01' as start_date, DATE '2021-07-31' as end_date, 2 as total_increment
)
SELECT file_date, start_date,
  LAST_DAY(end_date) as end_date,
  date_diff(end_date, start_date, month) as increment
FROM sample_table
CROSS JOIN UNNEST(GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 month)) as end_date
WHERE date_diff(end_date, start_date, month)  > 0