How to create more records from 1 record based on condition (SQL)

Question

I am trying to create more records from 1 record based on some conditions. For example: If the user purchased a monthly package, the table will create records for each week until the Date Ended.

From this table:

|Date Purchased| Date Ended| Package|
|2019-1-1      | 2019-2-1  | 1Month |

To this:

|Date Purchased| Date Ended| Package|
|2019-1-1      | 2019-2-1  | 1Month |
|2019-1-8      | 2019-2-1  | 1Month |
|2019-1-15     | 2019-2-1  | 1Month |
|2019-1-23     | 2019-2-1  | 1Month |

Any ideas on how to do this in SQL? Thanks

score 0 · Accepted Answer · answered Dec 01 '19 at 16:45

You can use GENERATE_DATE_ARRAY() and UNNEST():

SELECT dte, t.ended, t.package
FROM (select t,
             GENERATE_DATE_ARRAY(t.purchased, t.ended, interval 1 week) as dtes
      from (select date('2019-01-01') as purchased, date('2019-02-01') as ended,  '1MOnth' as package
           ) t
     ) t CROSS JOIN
     UNNEST(t.dtes) dte

score 0 · Answer 2 · answered Dec 01 '19 at 19:37

Below is for BigQuery Standard SQL

#standardSQL
SELECT * EXCEPT(day) REPLACE(day AS date_purchased)
FROM `project.dataset.table`, UNNEST((
  SELECT CASE package 
    WHEN 'Week' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 DAY)
    WHEN 'Month' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 WEEK)
    WHEN 'Quarter' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 MONTH)
    WHEN 'Year' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 QUARTER)
    ELSE GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 MONTH)
  END  
)) day

You can test, play above using dummy data as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT DATE '2019-01-01' date_purchased, DATE '2019-02-01' date_ended, 'Month' package UNION ALL
  SELECT '2019-01-01', '2019-03-31', 'Quarter' UNION ALL
  SELECT '2019-01-01', '2020-12-31', 'Year' 
)
SELECT * EXCEPT(day) REPLACE(day AS date_purchased)
FROM `project.dataset.table`, UNNEST((
  SELECT CASE package 
    WHEN 'Week' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 DAY)
    WHEN 'Month' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 WEEK)
    WHEN 'Quarter' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 MONTH)
    WHEN 'Year' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 QUARTER)
    ELSE GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 MONTH)
  END  
)) day

with result

Row date_purchased  date_ended  package  
1   2019-01-01      2019-02-01  Month    
2   2019-01-08      2019-02-01  Month    
3   2019-01-15      2019-02-01  Month    
4   2019-01-22      2019-02-01  Month    
5   2019-01-29      2019-02-01  Month    
6   2019-01-01      2019-03-31  Quarter  
7   2019-02-01      2019-03-31  Quarter  
8   2019-03-01      2019-03-31  Quarter  
9   2019-01-01      2020-12-31  Year     
10  2019-04-01      2020-12-31  Year     
11  2019-07-01      2020-12-31  Year     
12  2019-10-01      2020-12-31  Year     
13  2020-01-01      2020-12-31  Year     
14  2020-04-01      2020-12-31  Year     
15  2020-07-01      2020-12-31  Year     
16  2020-10-01      2020-12-31  Year

How to create more records from 1 record based on condition (SQL)

2 Answers2