Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    46

    Unanswered: Inactive days from last login

    Hello Experts,

    I am working in Oracle 9.2.0.7 and I am trying to find the no. of days inactive from the last login of all the users in the system.

    I have created_dt column in my access_table where all the logins of all the users are stored.

    Scenario 1:

    One user logged into the system on 17/09/2006 with one assigned role and again the same user logs in with the different
    role on the same day ie., 17/09/2006.

    Now when I find the days inactive from the last login I get 0 as the answer, which is right.

    Scenario 2:

    One user logged into the system on 16/09/2006 and again logs into the system on 18/09/2006.
    When I check the days inactive from the last login, I get 2 as the answer which is wrong and the expected
    answer is 1 as one day the user had not logged in ie., on 17/09/2006.

    The query I used is :


    SELECT a.user_first_nm "First Name", a.user_last_nm "Last Name",
    ROUND (a.created_dt-
    ( SELECT MAX (b.created_dt)
    FROM user_access_log a
    WHERE b.created_dt < a.created_dt
    AND b.logged_user_id=a.logged_user_id
    ), 0 ) "Days Inactive from last login"


    Can u pl. provide the solution for this issue asap.

    Thanks in advance.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    This is a way. You will have to bind the user_id. Tested it on 9iR2, your millage may vary.
    Code:
    SQL> create table t (
      2     user_id         number,
      3     log_date        date
      4  )
      5  /
    
    Table created.
    
    SQL> insert into t values ( 1, to_date( '17/09/2006', 'dd/mm/yyyy' ) );
    
    1 row created.
    
    SQL> insert into t values ( 1, to_date( '17/09/2006', 'dd/mm/yyyy' ) );
    
    1 row created.
    
    SQL> insert into t values ( 2, to_date( '16/09/2006', 'dd/mm/yyyy' ) );
    
    1 row created.
    
    SQL> insert into t values ( 2, to_date( '18/09/2006', 'dd/mm/yyyy' ) );
    
    1 row created.
    
    SQL> var user_id number
    SQL>
    SQL> exec :user_id := 1;
    
    PL/SQL procedure successfully completed.
    
    SQL> select count( * )
      2    from (
      3  select mindate + level - 1 dt
      4    from (
      5  select trunc( min( log_date ) ) mindate,
      6         trunc( max( log_date ) ) maxdate
      7    from t
      8   where user_id = :user_id
      9         )
     10  connect by level <= trunc( maxdate - mindate ) + 1
     11         ) x, t
     12   where x.dt = trunc( t.log_date(+) )
     13     and t.user_id(+) = :user_id
     14     and t.log_date is null
     15  /
    
      COUNT(*)
    ----------
             0
    
    SQL> exec :user_id := 2;
    
    PL/SQL procedure successfully completed.
    
    SQL> /
    
      COUNT(*)
    ----------
             1

  3. #3
    Join Date
    May 2006
    Posts
    46

    Inactive days from last login

    Hi Martinez,

    Thanks for the response.

    But still I am not clear about this query. Can u pl. explain me this.
    What is this level and connect by level do ? I am seeing for the first time.

    It would be better if u can give me a solution related to my query.

    Thanks

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You are joking, right?

    I didn't check JMartinez's solution, but - knowing him for a while now and seeing his posts - I strongly believe it is correct. You wrote a query which didn't work - he showed you another approach.

    This seems as if you tried to build a house starting from the roof - didn't succeed. Then, asking a mason for an advice, he says "start from the basement". Your answer would be "no, I'd rather you to show me the right way to build the house starting from the roof".

    Not hearing about level (a pseudocolumn) and connect by (used for hierarhical queries), but - since yesterday - knowing those terms, really should be enough for you to search for them by yourself. There are many search engines available, Google is one of the most popular. There is also Oracle Technology Network with genuine Oracle documentation, and - in my opinion - one should show good will and do some research, not expect to be spoonfed all the time.

  5. #5
    Join Date
    May 2006
    Posts
    46

    Inactive days from last login

    Thanks for the advice.
    I will search in the site for this and understand as I have just started to work on SQL queries.
    But as this has been raised as an issue, I wanted the solution immediately.


    Thanks

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Just run the query from inside out so you can see what each step does. I had to create an example for you, since you didn't provide us with sufficient information to work with.

    Table t has two fields, user_id and log_date, so you can log each user login date (as you described). I started creating only two user_id (1 and 2), with the dates on the scenarios you provided.

    I started with
    Code:
      5  select trunc( min( log_date ) ) mindate,
      6         trunc( max( log_date ) ) maxdate
      7    from t
      8   where user_id = :user_id
    which would select me the min/max logging dates for the given user (:user_id is a sqlplus bind variable of type number). That will help me identify the set of rows I need to create in order to cover all the days in this range.

    Then I used
    Code:
      3  select mindate + level - 1 dt
      4    from (
      5  select trunc( min( log_date ) ) mindate,
      6         trunc( max( log_date ) ) maxdate
      7    from t
      8   where user_id = :user_id
      9         )
     10  connect by level <= trunc( maxdate - mindate ) + 1
    To generate those rows. The connect by level is just a trick to generate rows on demand without touching any tables (you could have selected any table you believe would cover the whole range of days from the min and max logging dates of a given user).

    Then I just left joined (the (+) on the fields say this, you could have used left join if on 9i and above if you like) table t, on the fixed loging dates of the user and counted those days that were null, since that indicate me user has not loged-on on those dates.

    I usually set up examples that would guide you to a solution, rather than giving you exact solutions to your given problem.

  7. #7
    Join Date
    May 2006
    Posts
    46

    Inactive days from last login

    Hi,

    thanks a lot. Now I got the solution and the explanation as well. Now my problem is solved and the query works fine.

    Thanks

Posting Permissions

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