Big Query SQL to return dates and split into new rows

Question

Wondering if this is possible in big query.

I have a table that returns rules created by employees in a single row like the example below. It's not a very well thought out table and it is what it is.

RuleFrom	RuleTo	Rules
2023-05-31	2023-06-06	10%

This is what I'm expecting the data to look like.

Are there any way to use the RuleFrom and RuleTo dates and then create a new data set with 7 days of daily data like below?

RuleFrom        RuleTo        Rules
2023-05-31      2023-05-31      10%
2023-06-01      2023-06-01      10%
2023-06-02      2023-06-02      10%
2023-06-03      2023-06-03      10%
2023-06-04      2023-06-04      10%
2023-06-05      2023-06-05      10%
2023-06-06      2023-06-06      10%

Answer 1

We can handle this via a calendar table approach:

WITH dates AS (
    SELECT date
    FROM unnest(generate_date_array(
             (SELECT RuleFrom FROM yourTable),
             (SELECT RuleTo FROM yourTable),
             interval 1 day)
         ) AS date
)

SELECT d.date AS RuleFrom, d.date AS RuleTo, t.Rules
FROM dates d
CROSS JOIN yourTable t
ORDER BY d.date;

Answer 2

You can consider below.

-- sample data
WITH sample_table AS (
  SELECT DATE '2023-05-31' RuleFrom, DATE '2023-06-06' RuleTo, '10%' Rules
)
-- query starts here
SELECT dt AS RuleFrom, dt AS RuleTo, Rules 
  FROM sample_table, UNNEST(GENERATE_DATE_ARRAY(RuleFrom, RuleTo)) dt;

-- query result
+------------+------------+-------+
|  RuleFrom  |   RuleTo   | Rules |
+------------+------------+-------+
| 2023-05-31 | 2023-05-31 | 10%   |
| 2023-06-01 | 2023-06-01 | 10%   |
| 2023-06-02 | 2023-06-02 | 10%   |
| 2023-06-03 | 2023-06-03 | 10%   |
| 2023-06-04 | 2023-06-04 | 10%   |
| 2023-06-05 | 2023-06-05 | 10%   |
| 2023-06-06 | 2023-06-06 | 10%   |
+------------+------------+-------+