Archives of the TeradataForum

Message Posted: Wed, 10 Sep 2014 @ 11:19:56 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

This is probably what you have in mind:

     SELECT
         username
       , COALESCE (MAX(logdate), CAST ('1900-01-01' AS DATE) ) AS LAST_LOGIN_DT
       FROM metro.logonoff
       WHERE event = 'logon'
       GROUP BY 1
       HAVING LAST_LOGIN_DT < DATE-90
       ORDER BY 2 DESC
       ;

Of course, you have to be sure that the EventLog table is not going through some archive process and is not truncated regularly. If so, you have to adapt the SQL accordingly and check the history table as well.

You might also want to join with the dbc.users view in order to filter out the users that have been dropped already.

Regards,

Tomas

	<Prev	Next>		<<First	<Prev	Next>	Last>>