Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: New to DB2, help needed in writing a query.

    Hi,

    I am new to DB2 SQL and need help

    I have a table "LoggedInfo" which contains fields like
    userID - varchar,
    loggingTime - timestamp,
    loggedOutTime - timestamp

    Whenever a user log-in to or log-out from our application, we log data to "LoggedInfo" table. So we can have multiple records with userID="ABC"

    for ex:

    userID loggingTime loggedOutTime
    ABC 2011-06-15 13:32:58.753 2011-04-15 13:35:58.753
    DEF 2005-05-15 13:32:58.753 2005-05-15 13:35:58.753
    DEF 2005-01-15 13:32:58.753 2005-01-15 13:35:58.753
    DEF 2004-04-15 13:32:58.753 2004-04-15 13:35:58.753
    ABC 2010-06-15 13:32:58.753 2010-06-15 13:35:58.753
    XYZ 2005-06-15 13:32:58.753 2005-04-15 13:35:58.753
    ABC 2005-07-15 13:32:58.753 2005-07-15 13:35:58.753
    XYZ 2005-08-15 13:32:58.753 2005-08-15 13:35:58.753

    Problem:

    Want to retrieve record of users who haven't logged into our application from past 30 days.

    Result:

    DEF 2005-05-15 13:32:58.753
    XYZ 2005-08-15 13:32:58.753

    Please provide the query

    Thanks,
    Chinnu

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Use LAG OLAP Specification.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two questions.

    Q1) What are the meaning of columns?

    For example:
    ABC 2011-06-15 13:32:58.753 2011-04-15 13:35:58.753
    XYZ 2005-06-15 13:32:58.753 2005-04-15 13:35:58.753
    "loggedOutTime" were earlier than "loggingTime".

    Q2) How to know "... haven't logged into our application from past 30 days"?

    For example:
    ABC 2011-06-15 13:32:58.753 2011-04-15 13:35:58.753
    was last logged in(and logged out) at
    ABC 2010-06-15 13:32:58.753 2010-06-15 13:35:58.753

    DEF 2005-01-15 13:32:58.753 2005-01-15 13:35:58.753
    was last logged in(and logged out) at
    DEF 2004-04-15 13:32:58.753 2004-04-15 13:35:58.753

    But, those were not selected.

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    Try this:

    SELECT UserID , MAX(loggedOutTime)
    FROM loggingtable
    GROUP BY UserID
    HAVING MAX(loggedOutTime) < CURRENT TIMESTAMP - 30 DAYS

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Chinnu55 and umayer,

    Sorry, I have completely misunderstood Chinnu55's requirement.

  6. #6
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    by only looking at one table, all you are going to beable to provide is a list of those users
    who have not logged in/out in the last 30 days,
    that bothered to log in/out at all
    you are only going to provide a list of 'sporatic use' users. or those on vacation.
    if you want to know which users have not logged in/out within the last 30 days,
    wouldn't you want to consider those users who have authority to log in/out,
    but have not?

    sounds like make-work to me. generating useless statistics.
    Last edited by dbzTHEdinosaur; 06-17-11 at 10:36. Reason: added sarcasm
    Dick Brenholtz, Ami in Deutschland

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Another question is what happens with users that have logged in 40 days ago but not yet logged out. The query result would not include those...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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