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

user_9926677 0
Viewed 32

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

google-bigquery stored-procedures sql
1 Answers

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

user_52219440
Related