Oracle TRUNC date by quarter but with custom year start date
I need to get aggregate values on a quarterly basis which can be done using the trunc function with the option 'Q' like so
select count(*), trunc(DATE_COL, 'Q') from TABLE_NAME
group by trunc(DATE_COL, 'Q')
But the problem here is that I need the year to start on the 1st Dec of the previous year instead of 1st Jan (Ex: the Q1 2015 starts on 1-Dec-2014 and ends on 28-Feb-2015, as opposed to 1-Jan-2015 to 31-Mar-2015). Added: So Q1 includes Dec, Jan, Feb; Q2 includes Mar, Apr, May; Q3 includes Jun, Jul, Aug; Q4 includes Sep, Oct, Nov
PS: I'm using Oracle 11g and I'll be running these queries via PHP 5.3.
Seems to me you have several issues here.
1) To push a date from December into the native Q1 bundle, use add_months(:date,1). That moves everything by your offsets into Oracle's Q1,2,3,4 for easy aggregation.
2) Labeling. If you need the query to do dynamic labeling, then APC and Lalit have you covered for finding the correct start date. From there, last_day(add_months(Quarter_start_date,3)) would get you the other endpoint!
3) OK, so are you looking for a return of one row per quarter? Or one row per year with the four quarters as columns? If one row per quarter do you need to ensure that you return a row for a quarter where no data exists?
But to do a simple aggregate by Quarter with your one-month offset:
SELECT count(*)
, add_months(trunc(add_months(your_date_field,1),'Q'),-1) q_start_date
FROM YOUR_TABLE
GROUP BY add_months(trunc(add_months(your_date_field,1),'Q'),-1);
"I need the year to start on the 1st Dec of the previous year instead of 1st Jan (Ex: the Q1 2015 starts on 1-Dec-2014 and ends on 28-Feb-2015"
Okay, so you want to shift all the Quarters back a month. That's simple: use ADD_MONTHS() with a negative number to subtract a month....
select count(*), add_months(trunc(DATE_COL, 'Q'), -1) Q
from TABLE_NAME
group by add_months(trunc(DATE_COL, 'Q'), -1)
ADD_MONTHS() is a standard Oracle function. Find out more.
I need the year to start on the 1st Dec of the previous year instead of 1st Jan (Ex: the Q1 2015 starts on 1-Dec-2014
To get 1-DEC-2014 instead of 1-JAN-2015 for current quarter, you could shift a day back from this quarter and then get the 1st day of that month.
Update @APC's solution using ADD_MONTHS is nicer and neat than mine using TRUNC twice. I advice to use his solution. Mine could be an alternative, just good to know.
For example,
SQL> SELECT trunc(trunc(SYSDATE, 'Q') - 1, 'month') Q1 FROM dual;
Q1
---------
01-DEC-14
SQL>
Try,
SELECT COUNT(*),
TRUNC(TRUNC(DATE_COL, 'Q') - 1, 'month') Q1
FROM TABLE_NAME
GROUP BY TRUNC(TRUNC(DATE_COL, 'Q') - 1, 'month');
上一篇: 按联邦会计年度排列月份