snippetMinor
Oracle Query by Quarter
Viewed 0 times
oraclequeryquarter
Problem
I have a query that I want to get data from the past 5 quarters, but it doesn't work the way it should:
It doesn't do what I want. How do I make sure I get all rows for the current quarter and the last 4, no matter what day of the quarter it is?
select to_char(eventdate,'YYYY') || ' Q-' || to_char(eventdate, ' Q'), ...
from ...
where eventdate between (sysdate - 458) and sysdateIt doesn't do what I want. How do I make sure I get all rows for the current quarter and the last 4, no matter what day of the quarter it is?
Solution
testbed:
from foo
where eventdate>=add_months(trunc(sysdate, 'Q'), -12); -- trunc() gets first day of the Q
-- then add_months takes us back 4 Qs
/*
TO_CHAR(EVENTDATE,'YYYY"Q"-Q')
------------------------------
2011 Q- 4
2011 Q- 3
2011 Q- 2
2011 Q- 1
2010 Q- 4
*/
`
create table foo as
select add_months(sysdate, -(level-1)*3) as eventdate from dual connect by level
query:
select to_char(eventdate,'YYYY "Q"- Q') -- "Q" is treated as a literal due to the quotesfrom foo
where eventdate>=add_months(trunc(sysdate, 'Q'), -12); -- trunc() gets first day of the Q
-- then add_months takes us back 4 Qs
/*
TO_CHAR(EVENTDATE,'YYYY"Q"-Q')
------------------------------
2011 Q- 4
2011 Q- 3
2011 Q- 2
2011 Q- 1
2010 Q- 4
*/
`
Context
StackExchange Database Administrators Q#9951, answer score: 4
Revisions (0)
No revisions yet.