Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
"As you can see". Honestly I cannot see anything there except for an invalid SQL statement. Btw: it should be where eventdate between sysdate - 458 and sysdate –  a_horse_with_no_name Dec 30 '11 at 13:39
    
The query has a lot of joins, I left out the irrelevent parts. It works, so it surely isnt invalid. I think the only thing that needs tweeking is the where clause? –  mike628 Dec 30 '11 at 14:52
1  
Well the where clause is invalid (as I have already pointed out). And it helps other people if you post valid statements. –  a_horse_with_no_name Dec 30 '11 at 14:55

3 Answers 3

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            
*/
share|improve this answer
    
The trunc(sysdate, 'Q') is subtle. At first blush I thought the trunc() would just give you the current date until I looked again an realized the second parameter. Very nice. –  RobPaller Dec 30 '11 at 18:36

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.

share|improve this answer
1  
Thanks Rob, as a nooB, I couldnt upvote, but I ceratainly would. Great Explanation. –  mike628 Dec 30 '11 at 16:11
1  
@user74118 I've done it for you :-) –  Jack Douglas Dec 30 '11 at 17:51

Run the query in any quarter and if you want to get all the records in that quarter(in that year) based on CREATION_DATE in tables :

select * from emp_table
where CEIL( (EXTRACT(month from creation_date))/3) = CEIL( (EXTRACT(month from sysdate))/3)
and   EXTRACT(year from creation_date) = EXTRACT(year from sysdate);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.