Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: aggregating data.....9i

    Hi guys

    I was looking for a way to aggregate data for the past week along the lines of the following code which gives data for the past month:

    Code:
    where to_char(datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy')
    so that I don't have to use:

    Code:
    Where dateime between TRUNC(SYSDATE)-7 and TRUNC(SYSDATE)
    Regards
    Sheraz

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    I was looking for a way to aggregate data for the past week along the lines of the following code which gives data for the past month:
    And your problem is??

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    The issue is I can fetch a report using:

    Code:
    to_char(datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy')
    any time and it will always give me the stats for the previous whole month.

    But if I use:

    Code:
    between trunc(sysdate)-7 and trunc(sysdate)
    stats for the previous 7 days are fetched and not the last week. Hope you understand now.

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    Is there a way to select data from the last saturday 00:00:00 to Friday 23:59:59?

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What's wrong with the first statement?

  6. #6
    Join Date
    Aug 2008
    Posts
    464
    As I said, the first statement:

    Code:
    between trunc(sysdate)-7 and trunc(sysdate)
    needs to be run on saturday to get the stats for the period Sat to Fri of the last week (considering the week is from Sat to Fri).

    But if I want to run the query on Sun or Mon, the 7-day period the stats will be fetched for will shift too.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool WW or IW?

    Try this:
    Code:
    SELECT *
      FROM YourTable
     WHERE the_date BETWEEN Trunc(SYSDATE,'IW') - 1
                        AND Trunc(SYSDATE,'IW') + 5;
    Last edited by LKBrwn_DBA; 03-07-10 at 14:45. Reason: Corrected condition date to SYSDATE
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I meant, what's wrong with:

    to_char(datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy')

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink Data is for last week

    Quote Originally Posted by shammat View Post
    I meant, what's wrong with:

    to_char(datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy')
    OP wants to select previous 7 days data from the "last saturday 00:00:00 to Friday 23:59:59".
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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