# 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
``````
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProgrammerCommented:
``````select to_char(sysdate,'YYYY-"Q"Q') as CurQtr,
from dual
``````
0
BI 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

``````    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

``````
0
ProgrammerCommented:
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
BI 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
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,
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
``````
0

Experts Exchange Solution brought to you by