Using Current_Date to Create Financial Year Quarters

Question

Hi all,

I am running this SQL in SQL Developer and it gets the results as I expected. I want to generate the same thing in OBIEE column formula 12c Version which returns the value in Financial Quarter.  All I am trying to display the number of financial quarters to date in financial year.

Select

Case when trunc(sysdate) between ('01-JUL-2018') and ('30-SEP-2018') then 'Financial Quarter: 1'

Else

Case when trunc(sysdate) between ('01-OCT-2018') and ('31-DEC-2018') then 'Financial Quarter: 1 - 2'

Else

Case when trunc(sysdate) between ('01-JAN-2019') and ('31-MAR-2019') then 'Financial Quarter: 1 - 3'

Else

Case when trunc(sysdate) between ('01-APR-2019') and ('30-JUN-2019') then 'Financial Quarter 1 - 4'

END

END AS FIN_QTR from Dual;

OBIEE Column formula:

Case when Current_date between ('01-JUL-2018') and ('30-SEP-2018') then 'Financial Quarter: 1'

Else

Case when Current_date between ('01-OCT-2018') and ('31-DEC-2018') then 'Financial Quarter: 1 - 2'

Else

Case when Current_date between ('01-JAN-2019') and ('31-MAR-2019') then 'Financial Quarter: 1 - 3'

Else

Case when Current_date between ('01-APR-2019') and ('30-JUN-2019') then 'Financial Quarter 1 - 4'

END

Thanks in advance.

Omar

Thomas Dodds · Answer

Build a proper calendar dimension ...

Robert Angel · Answer

Hi Gianni,

if the edit was aimed at me then no need, I am 'bigger' than the points awarded herein, if you take the gamification seriously then you have probably forgotten why you started playing the game...

if it was aimed at others then; - "Honi Soit qui mal y pense"

And I know exactly what you mean on the case statement, from writing epm business rules I got into the habit of having the most used IF option first, so the unnecessary nesting really hit me in the eye!

Gianni Ceresa · Answer

Hi,

Without going into the details which Robert already covered, it's worth having a look at the CASE WHEN syntax (both SQL and OBIEE): it accepts multiple WHEN statements, so you definitely need to consider getting rid of your embedded CASE WHEN and have single unique CASE WHEN with the 3 (or 4 in case you need to manage a date out of range) statements.

CASE

WHEN .... THEN ....

ELSE ....

END

EDIT: just saw that was actually what Robert posted first  But that CASEs hurt my eyes so much I missed it