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

    Help required for dynamic quarter calculation

    688922
      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
          sb92075
          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
            688922
            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.

            Please suggest.
            • 3. Re: Help required for dynamic quarter calculation
              sb92075
              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
                Frank Kulash
                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
                (
                     SELECT  ADD_MONTHS ( start_date)
                                  , 3 * (1 - LEVEL)
                                  )   AS start_dt
                     ,     ADD_MONTHS ( start_date)
                                  , 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
                  688922
                  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
                    688922
                    Thank you so much for your response.Its working perfectly for me

                    Best Regards,
                    Sud
                    • 7. Re: Help required for dynamic quarter calculation
                      Sven W.
                      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
                            ,add_months(trunc(t.yourDatecol,'MM'), months_between(to_date(:p_start_date), trunc(to_date(:p_start_date),'YEAR'))) modified_quarter_start
                            ,to_char(add_months(trunc(t.yourDatecol,'MM'), months_between(to_date(:p_start_date), trunc(to_date(:p_start_date),'YEAR'))),'Q') modified_quarter_number
                            from yourtable t
                         )
                      select modified_quarter_start , count(*)
                      from testdata
                      where modified_quarter_start between add_months(to_date(:p_start_date),-1 * :p_number_of_months) and to_date(:p_start_date)
                      group by modified_quarter_start
                      ;
                      It avoids the construction of a monthly/quartly list. This is usefull, if you aggregate and do not care if there are no aggregation columns for certain quarters (if there is no data).

                      Only the "modified_quarter_start" column is needed. I added the other columns to help you understanding the logic that is behind. Simply do a select * without grouping, to see what math is done there. if there is an error post an example, so that we can work with some real data.