Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2005
    Posts
    53

    Unanswered: report for last 6 or 3 months

    Hi i have this query,

    how can i do for last 6 or 3 months report:

    select username from dba_users
    where username not in(
    select distinct user_id
    from stats$user_log
    where logon_day between (to_char(sysdate-182,'DD-MON-YYYY')) and to_char(sysdate-25,'DD-MON-YYYY')
    )
    order by username

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >how can i do for last 6 or 3 months report:
    Clarify what the above means.
    Provide examples for the date range to be reported based upon different run dates.

    Code:
    SELECT   username 
    FROM     dba_users 
    WHERE    username NOT IN (SELECT DISTINCT user_id 
                              FROM   stats$user_log 
                              WHERE  logon_day BETWEEN (To_char(SYSDATE - 182,'DD-MON-YYYY')) AND To_char(SYSDATE - 25,'DD-MON-YYYY')) 
    ORDER BY username
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2005
    Posts
    53
    Hi,

    I want to change this part of query
    (to_char(sysdate-182,'DD-MON-YYYY')) and to_char(sysdate-25,'DD-MON-YYYY')

    to get data for last 6 months

    Thanks
    Pat

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >how can i do for last 6 or 3 months report:
    >to get data for last 6 months

    Make up your mind.

    Clarify what the above means.
    Provide examples for the date range to be reported based upon different run dates.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Not sure why you are converting a date to char so you can compare it to a date.

    SYSDATE - 182 will give you the date 6 months ago. So this will give you all the users who have logged on in the last 6 months.

    Code:
    SELECT DISTINCT user_id 
    FROM   stats$user_log 
    WHERE  logon_day <= SYSDATE - 182
    Change 182 to 91 to run for last 3 months instead. Oracle also has add_months() function.


    Code:
    SELECT DISTINCT user_id 
    FROM   stats$user_log 
    WHERE  logon_day <= add_months(SYSDATE, -6)

  6. #6
    Join Date
    Jun 2005
    Posts
    53
    Hi,

    thanks artacus

    that worked!!

    thanks
    pat

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    do NOT convert to character when you are checking against a date type column. It will NOT work correctly!!!!
    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
  •