2 Replies Latest reply on Jul 1, 2020 9:06 PM by jaramill

    data that includes current and previous quarter


      create table m_test(x number, y date);

      insert into m_test values(1,to_date('01-FEB-2020','DD-MON-YYYY'));

      insert into m_test values(2,to_date('03-FEB-2020','DD-MON-YYYY'));

      insert into m_test values(3,to_date('01-APR-2020','DD-MON-YYYY'));

      insert into m_test values(4,to_date('10-MAY-2020','DD-MON-YYYY'));

      insert into m_test values(5,to_date('01-JUL-2020','DD-MON-YYYY'));

      insert into m_test values(6,to_date('01-JUL-2020','DD-MON-YYYY'));

      insert into m_test values(7,to_date('08-JUL-2020','DD-MON-YYYY'));




      I am trying to do 2 things,


      1. Get data where the y column falls under the current quarter and previous quarter, which means 4/1/2020 - 9/30/2020, I wrote a query that handles give me data that falls in current quarter (below), how can I improvise to include previous quarter too? result should be records starting x column values 3 until 7, as the dates fall under current and previous quarter


      SELECT * FROM m_test where y between (select trunc(sysdate,'Q') FROM DUAL )

          AND (SELECT add_months(trunc(SYSDATE, 'q'), 3) -1 FROM DUAL)


      2. I need to add a column that identifies if the record belongs to current quarter or previous and show "current" or "previous" accordingly, how do I do that ?

        • 1. Re: data that includes current and previous quarter
          Frank Kulash



          Thanks for posting the sample data.  Don't forget to post the exact results you want from that data.  It's great to describe the results, but describe them in addition to (not instead of) actually showing them.


          Are these the results you want from the sample data (when run in the quarter starting July 1, 2020)?

                X Y           C_OR_P

          ------- ----------- --------

                3 01-Apr-2020 Previous

                4 10-May-2020 Previous

                5 01-Jul-2020 Current

                6 01-Jul-2020 Current

                7 08-Jul-2020 Current


          Here's one way to get them.

          SELECT    m.*

          ,         CASE

                        WHEN  y >= TRUNC (SYSDATE, 'Q')

                        THEN  'Current'

                        ELSE  'Previous'

                    END  AS c_or_p

          FROM      m_test  m

          WHERE     y  >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q')

                                     , -3


          AND       y  <  ADD_MONTHS ( TRUNC (SYSDATE, 'Q')

                                     , 3


          ORDER BY  y


          You already knew how to use ADD_MONTHS to find the beginning of the following quarter.  You can use ADD_MONTHS the same way to find the beginning of the previous quarter.


          There's no reason to use the dual table in this problem.

          • 2. Re: data that includes current and previous quarter

            Thanks for providing the DDL and DML statements.

            Please state your full Oracle database version.

            Please show the output you expect after running your query.