Create months between two dates Snowflake SQL-CodePudding

I just want to generate the months between data range using SQL Query.

CodePudding user response：

You can use a table generator:

select '2022-07-04'::date   
row_number() over(partition by 1 order by null) - 1 GENERATED_DATE
from table(generator(rowcount => 365))
;

Just change the start date and the number of days into the series. You can use the datediff function to calculate the number of days between the start end end dates.

Edit: I just realized the generator table function requires a constant for the number of rows. That's easily solvable. Just set a higher number of rows than you'll need and specify the end of the series in a qualify clause:

set startdate = (select '2022-04-15'::date);
set enddate = (select '2022-07-04'::date);

select $startdate::date   
row_number() over(partition by 1 order by null) - 1 GENERATED_DATE
from table(generator(rowcount => 100000))
qualify GENERATED_DATE <= $enddate
;

CodePudding user response：

You can use a table generator in the CTE, and then select from the CTE and cartesian join to your table with data and use a case statement to see if the date in the generator is between your start and to dates.

Then select from it:

select user_id, x_date
from (
  with dates as (
  select '2019-01-01'::date   row_number() over(order by 0) x_date
  from table(generator(rowcount => 1500))
  )
  select d.x_date, t.*,
  case 
    when d.x_date between t.from_date and t.to_date then 'Y' else 'N' end target_date
  from dates d, my_table t --deliberate cartesian join
  )
where target_date = 'Y'
order by 1,2

Output:

USER_ID X_DATE
1       2/20/2019
1       2/21/2019
1       2/22/2019
1       2/23/2019
2       2/22/2019
2       2/23/2019
2       2/24/2019
2       2/25/2019
2       2/26/2019
2       2/27/2019
2       2/28/2019
3       3/1/2019
3       3/2/2019
3       3/3/2019
3       3/4/2019
3       3/5/2019

=======EDIT========

Based on your comments below, you are actually looking for something different than your original screenshots. Ok, so here we are still using the table generator, and then we're truncating the month to the first day of the month where the x-date is YES.

    select distinct t.user_id, t.from_date, t.to_date, date_trunc('MONTH', z.x_date) as trunc_month
    from (
        with dates as (
        select '2019-01-01'::date   row_number() over(order by 0) x_date
        from table(generator(rowcount => 1500))
        )
        select d.x_date, t.*,
        case 
         when d.x_date between t.from_date and t.to_date then 'Y' else 'N' end target_date
        from dates d, my_table t
          )z
    join my_table t
      on z.user_id = t.user_id
    where z.target_date = 'Y'
    order by 1,2

Output (modified User ID 3 to span 2 months):

USER_ID FROM_DATE   TO_DATE     TRUNC_MONTH
1       2/20/2019   2/23/2019   2/1/2019
2       2/22/2019   2/28/2019   2/1/2019
3       2/25/2019   3/5/2019    2/1/2019
3       2/25/2019   3/5/2019    3/1/2019