Oracle Query by Quarter

Question

I have a query that I want to get data from the past 5 quarters, but it doesn't work the way it should:

select to_char(eventdate,'YYYY') || ' Q-' || to_char(eventdate, ' Q'), ...
from ...
where eventdate between (sysdate - 458) and sysdate

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?

Answer 1

testbed:

create table foo as 
select add_months(sysdate, -(level-1)*3) as eventdate from dual connect by level<10;

select * from foo;
/*
EVENTDATE                 
------------------------- 
30-DEC-11 16.09.20        
30-SEP-11 16.09.20        
30-JUN-11 16.09.20        
30-MAR-11 16.09.20        
30-DEC-10 16.09.20        
30-SEP-10 16.09.20        
30-JUN-10 16.09.20        
30-MAR-10 16.09.20        
30-DEC-09 16.09.20        
*/

query:

select to_char(eventdate,'YYYY "Q"- Q') -- "Q" is treated as a literal due to the quotes
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            
*/

Answer 2

Most data models are lacking in a good DATE Dimension and thus force developers and report developers to rely on date arithmetic to find date boundaries that are relevant to the business model. (Fiscal Year, Fiscal Quarter, Fiscal Period, Calendar Quarter, etc.) A good CALENDAR table would go a long way to making your life easier.

A simple EventDate BETWEEN SYSDATE - 458 and SYSDATE risks truncating dates out of your oldest quarter. Take TODAY as an example: SYSDATE - 458 yields 2010-09-28. If my math is correct the 3rd Quarter of 2010 started on July 1, 2010.

You need roughly 548 days to make sure you are covering the entire range of current quarter plus the previous four full quarters. Trouble is that when you this will cause some overlap as your current quarter is partially complete. So you are faced with some additional logic to truncate out the fifth oldest quarter that you don't wish to include.

My PL/SQL isn't the sharpest right now to write that logic but I hope the explanation helps shed some light on the approach you will need to take.

SELECT
FROM Events
WHERE EventDate BETWEEN SYSDATE - 548 AND SYSDATE
  AND TO_CHAR(EventDate, 'Q') >= 
      CASE WHEN TO_CHAR(SYSDATE, 'Q') = 4 
           THEN 3 
           WHEN TO_CHAR(SYSDATE,'Q') = 3 
           THEN 2 WHEN TO_CHAR(SYSDATE, 'Q') = 2 
           THEN 1 WHEN TO_CHAR(SYSDATE 'Q') = 1 THEN 4 
      END
  AND /* Have to handle calendar year as well */

Hope this helps.