Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2013
    Posts
    19

    Unanswered: Running Totals Between Last 3 Months and following 3 Months for a given date

    Dear All,
    We're trying to write an OLAP based SQL to retrieve the running total for a record between the last 3 months and the following 3 months.

    When I write the query as
    Code:
    SELECT SUM(AMOUNT) OVER (PARTITION BY ACCT_NO ORDER BY MDATE RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING), ACCT_NO FROM TABNAME;
    It gives me for the result for the last 3 days and the next 3 days. How do I modify the query to return for last 3 months?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Try ADD_MONTHS, such as
    Code:
    between add_months(sysdate, -3) and add_months(sysdate, 3)

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by jerome.r View Post
    Dear All,
    We're trying to write an OLAP based SQL to retrieve the running total for a record between the last 3 months and the following 3 months.
    You may use "numtoyminterval( 3, 'month' )" or "interval '3' month" expressions instead of 3 (which is 3 days, as you correctly spotted).

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    SELECT SUM(AMOUNT),acct_no
    from tabname
    where mdate between add_months(trunc(sysdate), -3) and add_months(trunc(sysdate), 3)
    group by acct_no
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Apr 2013
    Posts
    19
    Quote Originally Posted by beilstwh View Post
    Code:
    SELECT SUM(AMOUNT),acct_no
    from tabname
    where mdate between add_months(trunc(sysdate), -3) and add_months(trunc(sysdate), 3)
    group by acct_no
    I just thought that an OLAP query would be much faster and efficient. specially since the volume of my table is in millions

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Its going to have to do a full table scan anyway since it has to evaluate every record (unless there is an index on acct_no,mdate)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •