Formatting Case Statement Issue

[CognosMoose]

Hi all,

The case statement below works for me, but is ugly as it gets.  I would like to combine conditions so that I have:

when 1,4,7,10...
when 2,5,8,11...
when 3,6,9,12

I have tried everything, and just couldn't get the syntax to work! 

Thank you.

Case extract(month, current_date)

when 1 then _first_of_month (current_date)
when 2 then _first_of_month (_add_months (current_date,-1))
when 3 then _first_of_month (_add_months (current_date,-2))
when 4 then _first_of_month (current_date)
when 5 then _first_of_month (_add_months (current_date,-1))
when 6 then _first_of_month (_add_months (current_date,-2))
when 7 then _first_of_month (current_date)
when 8 then _first_of_month (_add_months (current_date,-1))
when 9 then _first_of_month (_add_months (current_date,-2))
when 10 then _first_of_month (current_date)
when 11 then _first_of_month (_add_months (current_date,-1))
when 12 then _first_of_month (_add_months (current_date,-2))

end

[AnalyticsWithJay]

Add a data item called 'Current Month' with the following expression:

Code Select Expand

extract(month, current_date)


For your case statement, here's what a sample looks like:

Code Select Expand

CASE WHEN [Current Month] IN (1,4,7,10)
                THEN _first_of_month (current_date)
           WHEN  [Current Month] IN (2,5,8,11)
                 THEN _first_of_month (_add_months (current_date,-1))
           WHEN ... THEN ...
           ELSE ...
END

[CognosMoose]

This did it.  Thank you much.  Much more maintainable.

[AnalyticsWithJay]

You're welcome :)

[BigChris]

Can't you use something like:

Code Select Expand

case
  when extract(month, current_date) in (1,4,7,10) then  _first_of_month (current_date)
  when extract(month, current_date) in (2,5,8,11) then  _first_of_month (_add_months (current_date,-1))
  when extract(month, current_date) in (3,6,9,12) then  _first_of_month (_add_months (current_date,-2))
  else null
END

[Michael75]

Or why not:

Code Select Expand

case
  when mod(extract(month, current_date),3) = 1 then  _first_of_month (current_date)
  when mod(extract(month, current_date),3) = 2 then  _first_of_month (_add_months (current_date,-1))
  when mod(extract(month, current_date),3) = 0 then  _first_of_month (_add_months (current_date,-2))
  else null
END

There are many ways to skin a cat :)