7 Replies Latest reply: Mar 5, 2013 2:30 PM by 994749 RSS

    Quarterly Report Help

    994749
      Hello All,
      I been trying to figure this out the last couple of days. Thought I got it but no luck. I'm trying to create a query that will show me 1Q 2Q etc...

      Since we are in March I would like to run the report for 4Q of 2012.

      so far I have the query below... Plus sorry i'm new at building querys.


      select
      a.opendate "OpenDate",
      a.name "Agents Name"

      from si_signup a

      I notice the open date field is format in such a weird way it's show year and time.
      Example of date - 10/1/2012 12:00:00 AM

      Thank you in advance and if you need anything else please let me know.
        • 1. Re: Quarterly Report Help
          Hoek
          Welcome to the forum.

          Please read:
          {message:id=9360002} , especially #5 to 9, and edit your question, post CREATE TABLE (so we know the datatypes etc.) + INSERT INTO statements, database version and the desired output. Use the code tag as explained in the link, so your code/examples will appear in an indented/formatted way on the forum.
          I notice the open date field is format in such a weird way it's show year and time.
          That's not weird? What is so weird about it?
          • 2. Re: Quarterly Report Help
            Hoek
            edit

            This will probably not meet your requirement, but might give an idea:
            You can generate multiple rows from dual using CONNECT BY and LEVEL.
            SQL> select to_char(add_months(sysdate, level-1), 'dd/mm/yyyy hh24:mi:ss') dt
              2  ,      to_char(add_months(sysdate, level-1), 'Q') q
              3  from   dual
              4  connect by level <= 12;
            
            DT                  Q
            ------------------- -
            05/03/2013 00:34:42 1
            05/04/2013 00:34:42 2
            05/05/2013 00:34:42 2
            05/06/2013 00:34:42 2
            05/07/2013 00:34:42 3
            05/08/2013 00:34:42 3
            05/09/2013 00:34:42 3
            05/10/2013 00:34:42 4
            05/11/2013 00:34:42 4
            05/12/2013 00:34:42 4
            05/01/2014 00:34:42 1
            05/02/2014 00:34:42 1
            
            12 rows selected.
            Now, if you're able to provide the DDL/DML (a small, simplified example will do) along with the desired output, you'll get your answer a lot faster.
            • 3. Re: Quarterly Report Help
              994749
              Thank you for the advice...

              I"m running a Oracle Db 11g Ent
              PL/Sql Release 11.2.0

              Let me see if I could explain this correct.


              select
              a.opendate "OpenDate",
              a.name "Agents Name",
              a.city "city"

              from si_signup a


              The output of this should of the report should give me the total accounts open for the 4Q that will display the opendate, Agent name, and city.... 10 , 11, and 12 month.

              D Name City
              10/01/2012 Agent New York

              12/31/2012

              Sorry If I can't really explain it correct like I said I just started building query.
              • 4. Re: Quarterly Report Help
                Frank Kulash
                Hi,

                Welcome to the forum!
                991746 wrote:
                Hello All,
                I been trying to figure this out the last couple of days. Thought I got it but no luck. I'm trying to create a query that will show me 1Q 2Q etc...

                Since we are in March I would like to run the report for 4Q of 2012.
                Do you mean you want to see rows from the previous quarter, that is, the quarter before the current quarter? (During March 2013, the current quarter is the Q! of 2013, so the previous quarter is Q$ of 2012.)
                so far I have the query below... Plus sorry i'm new at building querys.


                select
                a.opendate "OpenDate",
                a.name "Agents Name"

                from si_signup a
                That will show you all rows from all quarters.
                If you only want rows where opendate is in the previous quarter:
                SELECT    opendate
                ,       name          AS agents_name
                FROM       si_signup
                WHERE       opendate     >= ADD_MONTHS (TRUNC (SYSDATE, 'Q'), -3)
                AND       opendate     <              TRUNC (SYSDATE, 'Q')
                ;
                I notice the open date field is format in such a weird way it's show year and time.
                Example of date - 10/1/2012 12:00:00 AM
                Actually, that's not so strange.
                Thank you in advance and if you need anything else please let me know.
                Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that 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, with specific examples.
                Always say which version of Oracle you're using (for example, 11.2.0.2.0).
                See the forum FAQ {message:id=9360002}
                • 5. Re: Quarterly Report Help
                  994749
                  ** Frank **
                  Thank you very much for showing me the query that worked PERFECT!!! That's what I been looking.
                  You made my job much easier since I have to convert so many query to quarterly end.
                  • 6. Re: Quarterly Report Help
                    Frank Kulash
                    Hi,

                    I'm glad I could help!

                    If the question is answered, please mark the thread asw "Answered". That way, people with similar problems will know to look here, and people who want to solve problems will know not to.
                    • 7. Re: Quarterly Report Help
                      994749
                      The query Frank gave helped me out.

                      WHERE ac.opendate >= ADD_MONTHS
                      (TRUNC (SYSDATE, 'Q'), -3)
                      ANd ac.opendate < TRUNC (SYSDATE, 'Q')