How to create dynamic column based on distance from specific date?-CodePudding

I would like to create dynamic column in Redshift, which will add new value incremented by 1 dynamically. Basically it will calculate month distance from specific date, let's say 1 Jan 2020. So for current month it should be 23, in next month it should be 24 etc. Is it possible to somehow replace something which I have now static in WITH statement? Counter stops on 12 and I would have to increment it every month manually.

with months as (
    select 1 as mon union all select 2 union all select 3 union all select 4 union all
    select 5 as mon union all select 6 union all select 7 union all select 8 union all
    select 9 as mon union all select 10 union all select 11 union all select 12
   ),

CodePudding user response：

I think you should use DATEDIFF function as it gives you the difference in months between two dates. Simply put the dates you want: https://docs.aws.amazon.com/redshift/latest/dg/r_DATEDIFF_function.html

Example: select datediff(mon,'2020-01-01',current_date) as mon_diff

Depends on the size for your table, maybe save the code as a view so every time you run it you will get the correct difference.

CodePudding user response：

Try this

   Alter table tablename
   Add New_column number Default
   datediff(mon,date_col, current_date);

  With data as
  (Select row_number()  over (order by 1) rn from 
  table)
 Select datediff(month, max(rn), current_date) 
  from data;

Note: replace table to some table with entries count as more than your required like 9 and so on then can limit the results as required