9 Replies Latest reply on Jan 10, 2019 8:35 AM by B.Delmée

    Last Quarter Data

    Bale

      Hi everyone,

      i have a question!

       

      i am generating a report, it had almost 10 years data but i want to get only previous quarter data only.

       

      For example: presently we are in Q1 -2019 but the report should be on Q4 -2018.

      Date Format:  'MMDDYYYY'

       

      Thank you

        • 1. Re: Last Quarter Data
          cormaco

          Please change you username into something readable:

          How can I change my Display Name?

           

          This gives the first and last day of the previous quarter:

          select 
              to_char(trunc(sysdate - interval '3' month,'Q'),'MMDDYYYY') as first_day,
              to_char(trunc(sysdate,'Q') - interval '1' day,'MMDDYYYY') as last_day 
          from dual
          
          FIRST_DA LAST_DAY
          -------- --------
          10012018 12312018
          
          
          
          • 2. Re: Last Quarter Data
            Gaz in Oz

            Here's one way:

             

            alter session set nls_date_format = 'yyyy-mm-dd';
            
            Session altered.
            
            with x (id, dt) as (
               select level, add_months(trunc(sysdate), -level) from dual connect by level <48
            )
            select to_char(dt, 'MMDDYYYY') "MMDDYYYY", -- result you want
                   /* these columns for debug, just showing the dates used */
                   to_char(dt, 'q') qtr,
                   last_day(add_months(trunc(sysdate, 'q'), -4)) begin_last_qtr,
                   trunc(sysdate, 'q') -1                        end_last_qtr
            from   x
            where  dt > last_day(add_months(trunc(sysdate, 'q'), -4))
            and    dt <= trunc(sysdate, 'q') -1;
            
            MMDDYYYY Q BEGIN_LAST END_LAST_Q
            -------- - ---------- ----------
            12072018 4 2018-09-30 2018-12-31
            11072018 4 2018-09-30 2018-12-31
            10072018 4 2018-09-30 2018-12-31
            
            3 rows selected.
            

            Note lines 01 to 07 used just to generate dummy data and show the dates used in the where clause.

            Lines 09 - 12. also just used to illustrate what is going on.

            Line 14 and 15 is the where clause you would need to get the previous qtr results.

             

            As you supplied zero data to work with then that was all necessary.

            You just need to use the where clause with your table/s, and your data...

            and possibly the select to_char(dt, 'MMDDYYYY') to generate your ambiguous date format display.

            • 3. Re: Last Quarter Data
              B.Delmée

              Note this forum is for questions related to the SQLDeveloper GUI tool. For generic SQL/PLSQL questions, please post here in the future. Here is another link to their fine posting guidelines on how to best explain your issue and help others help you.

               

              Another possibility is as follows

               

              WITH cte AS (
                SELECT trunc(SYSDATE -LEVEL) dt FROM dual CONNECT BY LEVEL <= 365
              )
              SELECT dt, trunc(add_months(dt,-3),'Q') qs, trunc(dt,'Q')-1 qe
              FROM cte
              order by dt
              ;
              
              • 4. Re: Last Quarter Data
                ptok

                SELECT

                    add_months(trunc(SYSDATE, 'YYYY'),(to_number(TO_CHAR(SYSDATE, 'Q')- 1)- 1)* 3) quarter_start

                    , trunc(SYSDATE, 'Q')- 1 quarter_end

                FROM

                    dual;

                • 5. Re: Last Quarter Data
                  Bale

                  Thank you very much for your reply, really appreciate your help.

                   

                  I changed my display name and it works out.

                   

                  I am supposed to write my query as below mentioned, so could you please help me where do you want me to use your logic??

                   

                  Select

                  SubName,

                  rollnum,

                  class,

                  section,

                  examdate,

                  results,

                  status,

                  from school.xclass

                  where staus = 'Present'

                  • 6. Re: Last Quarter Data
                    Bale

                    Hi Delmee,

                     

                    I am sorry this is my first post and I will follow your links.

                     

                    Thank you for your reply.

                    • 7. Re: Last Quarter Data
                      B.Delmée

                      Assuming "examdate" is your date field, something akin to the following (or you may adjust the where clause to any of the above suggestions if you prefer).

                       

                      Select
                           SubName,
                           rollnum,
                           class,
                           section,
                           examdate,
                           results,
                           status,
                      from school.xclass
                      where staus = 'Present'
                        and examdate >= trunc(add_months(sysdate,-3),'Q') 
                        and examdate < trunc(sysdate,'Q');
                      
                      • 8. Re: Last Quarter Data
                        Bale

                        Hi Delmee,

                         

                        I am sorry to ask this question I am new to SQL and may I know what is the dt ??

                         

                        1.   and examdate >= trunc(add_months(dt,-3),'Q')  
                        2.   and examdate < trunc(dt,'Q');

                         

                        can I use the same logic in my Oracle SQL ??

                         

                        Thanks in advance.

                        • 9. Re: Last Quarter Data
                          B.Delmée

                          Sorry Bale, my bad, "dt" represents the current date aka "sysdate" in Oracle SQL - that was a case of hasty cut and paste, i edited the previous post for correctness.

                          Also note that this assumes "examdate" is of proper DATE type, *not* some string representation of a date.