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
Add a data item called 'Current Month' with the following expression:
extract(month, current_date)
For your case statement, here's what a sample looks like:
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
This did it. Thank you much. Much more maintainable.
You're welcome :)
Can't you use something like:
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
Or why not:
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 :)