WHILE loop to generate columns using Google BigQuery SQL

Question

I'm trying to query a table and generate in my output a number of N different columns. The 'N' depends on the number of months between a certain date and today.

This is what I'm trying:

DECLARE mes DATE DEFAULT '2021-11-01';
DECLARE mes_1 DATE;

SELECT 
    issue_date,
    cancell_date,
    WHILE mes <= CURRENT_DATE DO
      SET mes_1 = DATE_ADD(mes, INTERVAL 1 MONTH)
      CASE WHEN issue_date >= mes_1 THEN 0
      WHEN issue_date < mes AND cancell_date < mes THEN 0
      WHEN issue_date < mes AND cancell_date IS NULL THEN 1
      WHEN issue_date < mes AND cancell_date >= mes_1 THEN 1
      WHEN issue_date < mes AND cancell_date >= mes AND cancell_date < mes_1 THEN DATE_DIFF(cancell_date, mes, DAY)/30
      WHEN issue_date< mes_1 AND issue_date >= mes AND cancell_date IS NULL THEN DATE_DIFF(mes_1,issue_date, DAY)/30
      WHEN issue_date< mes_1 AND issue_date >= mes AND cancell_date >= mes_1 THEN DATE_DIFF(mes_1,issue_date, DAY)/30
      WHEN issue_date < mes_1 AND issue_date >= mes AND cancell_date < mes_1 THEN DATE_DIFF(cancell_date, issue_date, DAY)/30
      ELSE 0 END AS CONCAT("exposicion_",EXTRACT(YEAR FROM mes),"_",EXTRACT(MONTH FROM mes),"_",EXTRACT(DAY FROM mes))
      SET mes = DATE_ADD(mes, INTERVAL 1 MONTH)
    END WHILE
FROM my_table;

The input looks like this:

issue_date | cancell_date|
10/15/2021 |             |
10/15/2021 |  11/10/2021 |
11/11/2021 |  12/29/2021 |
12/01/2021 |             |

And setting mes = 11/1/2021 and current_date = 12/31/2021 the output should be this:

issue_date | cancell_date|exposicion_2021_11_01|exposicion_2021_12_01|
10/15/2021 |             |                    1|                    1|
10/15/2021 |  11/10/2021 |               0.3333|                    0|
11/11/2021 |  12/29/2021 |              0.63333|              0.96667|
12/01/2021 |             |                    0|                    1|

That's what I'm trying to do but I'm failing on it. I asssume that this isn't the way to approach this problem, I've been doing some research but can't find the solution.

I appreciate someone can give me at least a hint of how to approach this.

Thanks!