This discussion is archived
4 Replies Latest reply: Dec 5, 2012 8:08 AM by Frank Kulash RSS

Rolling Quarter

688922 Newbie
Currently Being Moderated
Hi,

I have a situation where I have to group the data based on rolling quarter.
My sample data in the dim_time table is as follows:

DATE_ID     CAL_DATE     YEAR     QUARTER     MONTH     MONTH_FULL     WEEK_OF_YEAR     DAY_OF_WEEK     CR_DATE
19050418     18-APR-05     1905     2     4     April     16     Tuesday     13-SEP-12
19050419     19-APR-05     1905     2     4     April     16     Wednesday     13-SEP-12
19050420     20-APR-05     1905     2     4     April     16     Thursday     13-SEP-12
19050421     21-APR-05     1905     2     4     April     16     Friday     13-SEP-12

User enter the last date(say 15th Sep 2012) and the number of quarters he wants to see...say 2.
The output will be grouped by previous two quarters starting with 15th Sep.
i.e. Q1:1st Sep -15th Sep Q2:1st Jun -30th Jun
1st Aug - 31st Aug 1st May - 31st May
1st July - 31st July 1st Apr - 30th Apr

Please suggest how to achieve this.

Best Regards,
sud
  • 1. Re: Rolling Quarter
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    isn't this post similar to what your have posted here: {message:id=10725255}?

    I suggest to avoid opening again the same question considering that the previous one is still mark as unanswered.

    Also when you post code and output please enclose it between two lines starting with {noformat}
    {noformat}. It will be easier to read.
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 2. Re: Rolling Quarter
    688922 Newbie
    Currently Being Moderated
    The situation here is little bit different.

    Here my fact table has daily level data and the time dimension table have data as below:

    {
    DATE_ID     CAL_DATE     YEAR     QUARTER     MONTH     MONTH_FULL     WEEK_OF_YEAR     DAY_OF_WEEK     CR_DATE
    19050418     18-APR-05     1905     2     4     April     16     Tuesday     13-SEP-12
    19050419     19-APR-05     1905     2     4     April     16     Wednesday     13-SEP-12
    19050420     20-APR-05     1905     2     4     April     16     Thursday     13-SEP-12
    19050421     21-APR-05     1905     2     4     April     16     Friday     13-SEP-12
    }

    Please suggest a way where the user can view the report aggregated by quarters.

    Now, the rule is that any user can select his own quarter like the example I gave.It can start from any month as the user wish.And second is, he can go back to as many quarters as he wishes.

    Please suggest your thoughts in this.

    Best Regards,
    Sud
  • 3. Re: Rolling Quarter
    AlbertoFaenza Expert
    Currently Being Moderated
    Trinity317 wrote:
    Please suggest your thoughts in this.
    Mark your previous post as answered if it is solved.

    Please read SQL and PL/SQL FAQ
    In the previous post and in this post you haven't posted create table and insert statements. If you don't mind doing it will be easier to get help.

    Additionally when you put some code or output please enclose it between two lines starting with {noformat}
    {noformat}
    
    i.e.:
    {noformat}
    {noformat}
    SELECT ...
    {noformat}
    {noformat}
    
    
    Regards.
    Al
    
    Edited by: Alberto Faenza on Dec 5, 2012 5:02 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 4. Re: Rolling Quarter
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Sud,

    It still looks like this question was answered in your previous thread. In particular, {message:id=10725359} showed how to group by any given number of 3-month periods where the periods could begin on any given date.


    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
    If the problem involves parameters that you pass at run-time, then give a couple of different sets of parameters, and the results you want from the same sample data for each set.
    Simplify the problem as much as possible. Remove all tables and columns that play no role in this problem.
    Always say which version of Oracle you're using (for example, 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points