Determine current Quarter based on sysdate

How do I determine the current & previous quarter based on the current date(sysdate)?

 (SELECT  CONCAT (TO_CHAR(PERF_PRD_ENDDT,'YYYY-'),concat ('Q',TO_CHAR(PERF_PRD_ENDDT,'Q') ))from dual) as CurQtr
    . (SELECT  CONCAT (TO_CHAR(PERF_PRD_ENDDT,'YYYY-'),concat ('Q',TO_CHAR(PERF_PRD_ENDDT,'Q')-1 ))from dual) as PrevQtr

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
Pavel GushchinConnect With a Mentor ProgrammerCommented:
As I don't understand exactly what do you need, I can only guess... From your description it looks like this:
select to_char(PERF_PRD_ENDDT,'YYYY-"Q"Q') as CurQtr, 
       to_char(add_months(trunc(PERF_PRD_ENDDT,'mm'),-3),'YYYY-"Q"Q') as PrevQtr,
       decode(E.EVALUATION_CAT,'C','COST','M','MANAGEMENT','Q','QUALITY','S','SCHEDULE','T','TECHNICAL') AS GPA_CATEGORY,
       decode(E.EVALUATION_CAT,'C','Gold','M','Yellow','Q','Brown','S','Brown','T','Brown') as CurColor,
       decode(E.EVALUATION_CAT,'C','Silver','M','Yellow','Q','Brown','S','Brown','T','Brown') as PrevColor,
       e.eval_cmt

Open in new window

0
 
Pavel GushchinProgrammerCommented:
select to_char(sysdate,'YYYY-"Q"Q') as CurQtr, 
       to_char(add_months(trunc(sysdate,'mm'),-3),'YYYY-"Q"Q') as PrevQtr 
from dual

Open in new window

0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks that did the trick, however, now I need to use these results to determine which color("g.SUPP_PERF_LVL") is required for the Previous vs Current for the following category.

so if the curQtr for cost might be Gold and PreQtr may be silver.
where Management PrevQtr = Yellow

sample
    Select
  (  SELECT to_char(sysdate,'YYYY-"Q"Q') as CurQtr FROM dual)
, (  SELECT to_char(add_months(trunc(sysdate,'mm'),-3),'YYYY-"Q"Q') as PrevQtr FROM dual)
, case
      WHEN E.EVALUATION_CAT = 'C' THEN 'COST'
      WHEN E.EVALUATION_CAT = 'M' THEN 'MANAGEMENT'
      WHEN E.EVALUATION_CAT = 'Q' THEN 'QUALITY'
      WHEN E.EVALUATION_CAT = 'S' THEN 'SCHEDULE'
      WHEN E.EVALUATION_CAT = 'T' THEN 'TECHNICAL'
  END AS GPA_CATEGORY 
  ,  g.SUPP_PERF_LVL
 ,  e.eval_cmt
 

Open in new window

0
 
Pavel GushchinProgrammerCommented:
First of all, i've posted answer above with using sysdate, but you need to use PERF_PRD_ENDDT i think.
What about the second question, you're need to describe it more detaily.
How does the colors corresponds to SUPP_PERF_LVL? It's better to post the full query.
You're selecting results for the one fixed quarter for the PERF_PRD_ENDDT date.
Do you need to select it twice? Or do you need to return 2 columns of colors (for current quarter and for previous color)?
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Or do you need to return 2 columns of colors (for current quarter and for previous color)?

Yes two columns previous & current for each category.  I switch my approach to using rank function so I need something like this:

For each Supplier & Category return the SUPP_PERF_LVL(which is a color) one for each quarter (Prev & current).  

I am not at work so I cannot  give you my actual query.  Hopefully you will be able to assist me in some kind of string or function that will return the correct results.

Thanks,

Karen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.