Ad

Creating A Stored Procedure Or View In SQL That Will Run In Big Query That Fills Payment History Based On First Payment And Last Payment

- 1 answer

I have a file that have this structure

Account_ID  first_payment   Last_payment   Status
----------------------------------------------------
     1       10/15/2021          NA        Active
     2       09/22/2021       11/22/2021   Canceled
     .            .              .           .

I am trying to create a payment record file based on this information, so if a customer is active it will create 1 payment monthly till the next valid current date, and if he is canceled then it will create 1 monthly payment until the cancel date.

So the final result for this file will be

Account_id  payment
----------------------
1           10/15/2021 
1           11/15/2021
1           12/15/2021
1           01/15/2022
2           09/15/2021
2           10/15/2021
2           11/15/2021

Thanks for the help

Ad

Answer

Consider below approach

select Account_ID, payment
from your_table, 
unnest(generate_date_array(first_payment, ifnull(last_payment, current_date), interval 1 month)) payment            

if applied to sample data in your question

with your_table as (
  select 1 Account_ID, date '2021-10-15' first_payment, cast(null as date) last_payment, 'Active' Status union all
  select 2, '2021-09-22', '2021-11-22', 'Canceled'
)            

output is

enter image description here

Ad
source: stackoverflow.com
Ad