1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query based on current calendar quarter

Discussion in 'SQL PL/SQL' started by bremen22, Aug 20, 2013.

  1. bremen22

    bremen22 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Good Morning all,

    First let me start by saying I am not a programer, DBA, or anything even close. Most of what I know has come from forums. Before I post I have googled my question, but not knowing what to search for makes things difficult. That being said....

    I have an Excel spreadsheet that queries an Oracle database and pulls in the desired information. For my current query I would like to show data from the current quater on one sheet and the previous quater on another sheet. I already have everything else figured out and done. My workbook has two different sheets with the queries. I just need the where statemehent for the current quater and the where statement for the previous quater.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Query based on current calendar quater

    And you need to provide a table definition to at least let us know HOW the quarters are identified in the source table.

    We can't help you if you don't provide information we need.
     
  3. bremen22

    bremen22 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Re: Query based on current calendar quater

    all will be based off of a date time field in one of my tables. I can not give the table or field name for security issues so table.field will have to suffice. However it is a date time field.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Query based on current calendar quater

    And how are your fiscal quarters defined? I suppose that's also unavailable for security reasons.

    Given all of the information you haven't provided this is one possible way to get the window you want:

    Code (SQL):
    SQL> CREATE TABLE toosecuretoname(
      2          unnamed_fld1    varchar2(20),
      3          unnamed_fld2    DATE,
      4          unnamed_fld3    NUMBER,
      5          unnamed_fld4    varchar2(40));
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2
      3          FOR i IN 1..365 loop
      4                  INSERT INTO toosecuretoname
      5                  VALUES('Can''t tell ya',sysdate+i, i, 'Really can''t tell ya');
      6          END loop;
      7
      8          commit;
      9
     10  END;
     11  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT SUM(unnamed_fld3)
      2  FROM toosecuretoname
      3  WHERE to_char(unnamed_fld2, 'MM') BETWEEN to_char(last_day(add_months(sysdate, -2)) + 1, 'MM') AND to_char(last_day(add_months(sysdate,
     +1)), 'MM')
      4  /
     
    SUM(UNNAMED_FLD3)
    -----------------
                18201
     
    SQL>
    Of course without any real information to work from you'll need to make this work for your table definition.
     
  5. bremen22

    bremen22 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    In my another one of my spreadsheets I have a statement that looks like this
    Code (SQL):
    WHERE "TABLE.DATE" >= ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), - 1)
    AND  "TABLE.DATE" <  TRUNC (SYSDATE, 'MONTH')
    This where statement grabs information for the previous month based on user system date. I am looking for something simialr to operate by quater. Our quaters are nothing fancy.
    Q1= Jan, Feb, Mar
    Q2=Apr, May, Jun
    Q3=Jul, Aug, Sep
    Q4=Oct, Nov, Dec.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The query I posted, which I ran yesterday, provides data for the third quarter (July through September). You now have an idea how to generate such a date window; it's your turn to modify that code to meet your needs.