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
CodePudding user response:
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