7 Replies Latest reply on Dec 3, 2012 9:11 PM by Sven W.

# Help required for dynamic quarter calculation

I have a requirement, where the user will have the liberty to choose his self defined Quarter and the report output will be grouped based on what he selects.

The data in the table is at daily grain.

So ,if the user selects date as 1st April and number of quarters as 2.

The report should show the data aggregated for previous 2 quarters where 1st April is the start date.

i.e.Feb-March-April as Q2
and Nov-Dec-Jan as Q1

Please suggest how to implement this.

Best Regards,
Sud
• ###### 1. Re: Help required for dynamic quarter calculation
Trinity317 wrote:
I have a requirement, where the user will have the liberty to choose his self defined Quarter and the report output will be grouped based on what he selects.

The data in the table is at daily grain.

So ,if the user selects date as 1st April and number of quarters as 2.

The report should show the data aggregated for previous 2 quarters where 1st April is the start date.

i.e.Feb-March-April as Q2
and Nov-Dec-Jan as Q1

Please suggest how to implement this.

Best Regards,
Sud
use ADD_MONTH() function specifying minus two as second argument
• ###### 2. Re: Help required for dynamic quarter calculation
I am not sure how you are saying to implement add_month function.

Because,say suppose user enters 5 quarters.

so, it has to go back to 5 quarters starting with the date he entered.
In that case , I have do the add_months 5 times. And that too by hardcoding the same.

Isn't there a dynamic way of aggregating 3 months.

• ###### 3. Re: Help required for dynamic quarter calculation
first post working SQL with a sample hard coded solution; then can show what changes to make it dynamic
• ###### 4. Re: Help required for dynamic quarter calculation
Hi,

Welcome to the forum!
Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements) for all the tables involved, so the people who want to help you can re-create the problem and test their ideas. Also post the results you want from that data, and an explanation of how you get those results from that data.
If the problem involves parameters (such as the number of quarters you want, and the starting point of one of the quarters) then post a few sets of parameters, and the results you want from the same sample data for each set.
Explain, using specific examples, how you get those results from that data.
Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
See the forum FAQ {message:id=9360002}

Here's one way you can pass the set the number of quarters, and the starting date of the last quarter wanted:
``````WITH   params      AS
(
SELECT  TO_DATE ( '01-Apr-2012'     -- Beginning of last quarter
, 'DD-Mon-YYYY'
)     AS s_date
,     5          AS num_quarters
FROM     dual
)
,     quarters_wanted         AS
(
, 3 * (1 - LEVEL)
)   AS start_dt
, 3 * (2 - LEVEL)
)   AS end_dt
FROM     params
CONNECT BY     LEVEL <= num_quarters
)
SELECT       q.start_date
,       COUNT (*)     AS num_rows
FROM       quarters_wanted  a
JOIN       your_table        t  ON  t.date_column  >= q.start_date
AND t.date_coplumn <  q.end_date
GROUP BY  q.start_date
;``````
No matter what date you pick (it doesn't have to be the beginning of a month) or how many quarters you want, you only have to change 2 lines in the params sub-query.

If you want to include quarters that have no data in your table (with a COUNT of 0) then change the inner JOIN in the main query to a LEFT OUTER JOIN.
• ###### 5. Re: Help required for dynamic quarter calculation
My Date_DIM table is like below:

DATE_ID     CAL_DATE     YEAR     MONTH     MONTH_FULL     WEEK_OF_YEAR     DAY_OF_WEEK     CR_DATE
20111004     04-OCT-11     2011     10     October     40     Tuesday     13-SEP-12
20111005     05-OCT-11     2011     10     October     40     Wednesday     13-SEP-12
20111006     06-OCT-11     2011     10     October     40     Thursday     13-SEP-12
20111007     07-OCT-11     2011     10     October     40     Friday     13-SEP-12
20111008     08-OCT-11     2011     10     October     40     Saturday     13-SEP-12
20111009     09-OCT-11     2011     10     October     40     Sunday     13-SEP-12
20111130     30-NOV-11     2011     11     November     48     Wednesday     13-SEP-12
20111201     01-DEC-11     2011     12     December     48     Thursday     13-SEP-12
20111202     02-DEC-11     2011     12     December     48     Friday     13-SEP-12
20111203     03-DEC-11     2011     12     December     48     Saturday     13-SEP-12

the entered date is matched against CAL_DATE and the quarter is calculated based on that.
• ###### 6. Re: Help required for dynamic quarter calculation
Thank you so much for your response.Its working perfectly for me

Best Regards,
Sud
• ###### 7. Re: Help required for dynamic quarter calculation
Something like this might also work. Instead of "to_date(:p_start_date)" you can use a pl/sql date variable, to get the same result.
``````with testdata
as (select t.yourDatecol
,trunc(t.yourDatecol,'Q') real_quarter_start
,to_char(t.yourDatecol,'Q') real_quarter_number
,to_date(:p_start_date) start_date
,months_between(to_date(:p_start_date), trunc(to_date(:p_start_date),'YEAR')) quarter_offset