Calculate previous quarters from sysdate

[Former Member]

Hi

I have a requirement to take the sysdate, and create a filter for the past 4 quarters.

Eg... if today is Q2 2010, when they drag the filter in the report, it should show Q1 2010, Q4 2009, Q3 2009 and Q2 2009.

It should exclude the current quarter.

I had a similar requirement to calculate future quarters, including current quarter. I have the following sql -

ALIAS_DIM_DATE.CALENDAR_DATE Between (select trunc(sysdate,'Q') from dual) and (select add_months(trunc(sysdate,'Q'),12)-1 from dual)

How do I do the same to go back quarters? I tried adding a negative value after sysdate (-12) but it didn't work.

Kinda new to this, so would appreciate some help!

thanks

Riya.

[former_member207342]

refer this post to get you started.

Also, If you have date_dim then you must be having Quarter column over there you can play around that.

--Kuldeep

Edited by: Kuldeep Chitrakar on Mar 9, 2010 8:25 AM

[Former Member]

select (case when to_char(sysdate,'ddd') Between 1 and 90 Then to_char((sysdate+90-to_char(sysdate,'ddd')),'Q')

end) as Quat from dual

union

select (case when to_char(sysdate91,'ddd') Between 91 and 182 Then to_char((sysdate181-to_char(sysdate,'ddd')),'Q')

end) as Quat from dual

union all

select (case when to_char(sysdate183,'ddd') Between 183 and 273 Then to_char((sysdate273-to_char(sysdate,'ddd')),'Q') end) as Quat

from dual

union

select (case when to_char(sysdate+274,'ddd') Between 274 and 365 Then to_char((sysdate-365-to_char(sysdate,'ddd')),'Q')

end ) as Quat from dual;

Take as object form Universe......

This may helps you.......

[Former Member]

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=1 THEN

TO_CHAR(SYSDATE-90,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=1 THEN

TO_CHAR(SYSDATE-182,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=1 THEN

TO_CHAR(SYSDATE-274,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=1 THEN

TO_CHAR(SYSDATE-365,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=2 THEN

TO_CHAR(SYSDATE-90,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=2 THEN

TO_CHAR(SYSDATE-182,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=2 THEN

TO_CHAR(SYSDATE-274,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=2 THEN

TO_CHAR(SYSDATE-365,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=3 THEN

TO_CHAR(SYSDATE-90,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=3 THEN

TO_CHAR(SYSDATE-182,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=3 THEN

TO_CHAR(SYSDATE-274,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=3 THEN

TO_CHAR(SYSDATE-365,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=4 THEN

TO_CHAR(SYSDATE-90,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=4 THEN

TO_CHAR(SYSDATE-182,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=4 THEN

TO_CHAR(SYSDATE-274,'YYYYQ') END) QQ FROM DUAL

UNION

SELECT (CASE WHEN TO_CHAR(SYSDATE,'Q')<=4 THEN

TO_CHAR(SYSDATE-365,'YYYYQ') END) QQ FROM DUAL

/