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%

score 0 · Answer 1 · answered Jun 13 '23 at 07:18

0

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;

answered Jun 13 '23 at 07:18

Tim Biegeleisen

502,043
27
286
360

Hi Tim, i've tried this one but it returns an unexpected keyword interval error – Jonathon Chau Jun 16 '23 at 05:55

score 0 · Answer 2 · answered Jun 13 '23 at 14:54

0

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%   |
+------------+------------+-------+

answered Jun 13 '23 at 14:54

Jaytiger

11,626
2
5
15

Hi @Jaytiger. I got this one working, but whatever if i have multiple dates? How do i change the query instead of it looking at 2023-05-31 as the From date? – Jonathon Chau Jun 16 '23 at 05:47
@JonathonChau, sorry I didn't get your question clearly. Would you be more specific on `having multiple dates` ? not sure if it means you have more rows in your input table rather than a single row. – Jaytiger Jun 16 '23 at 06:00

Big Query SQL to return dates and split into new rows

2 Answers2