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

Use LAG OLAP Specification.

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.

Try this:

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

Chinnu55 and umayer,

Sorry, I have completely misunderstood Chinnu55's requirement.

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.

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...