How to use variables inside functions in Redshift?

Question

Is it possible to use e.g. in line like this

SELECT  trunc(dateadd(month,mon,add_months(date_trunc('month', CURRENT_DATE), -9))) as dates
FROM    months

instead of -9, I would like to have there maximum value of column created like this (so in this case 12):

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

Is it possible somehow in Redshift? I tried just like that

SELECT  trunc(dateadd(month,mon,add_months(date_trunc('month', CURRENT_DATE), -MAX(mon))) as dates
FROM    months

but it is not working as expected

where is this mon field created? what if you create a column in the master table with the max value of the mon column and then use the resulting column in the function -- would that help? — samkart
– samkart, Commented Dec 15, 2021 at 12:31
What are you actually wanting to accomplish (as opposed to how you are trying to do it)? Are you wanting a date from 9 months ago? Can you describe the output you actually want to achieve? — John Rotenstein
– John Rotenstein, Commented Dec 16, 2021 at 3:13

Himanshu · Accepted Answer · 2021-12-15 23:11:32Z

1

Yes, an alternate way is

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

You can take some bigger table having more entries as required and replace that tablename in WITH clause like you said max 9 in above case and just limit them to the count of your tables month entries

answered Dec 15, 2021 at 23:11

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use variables inside functions in Redshift?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related