Archives of the TeradataForum

Message Posted: Wed, 19 Jun 2013 @ 13:50:29 GMT

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

Hi,

This may work.

     sel a.*, b.*
     from (sel username, substr(commentstring,1,30) "Full Name",
          cast(createtimestamp as date format 'MM-DD-YYYY')  CreateDate
       from dbc.users
         /* Select users that were created at least 60 days ago */
       where
      Username not in ('All_Users','SysAdmin','Sys_Calendar')
     and cast(createtimestamp as date) <= current_date - 60 ) a  left outer join (sel
     username, max(Logondate) latest_logon,  current_date - max(Logondate) days_since  from
     sys_hist.logonoff_hist  where event='Logon'
     and Username not in ('All_Users','SysAdmin','Sys_Calendar')
      group by 1 ) b
      on a.username=b.username
        /* Select users that have not logged on in the last 45 days */ where days_since is
     null
       or days_since >= 60
     order by 1;

James Park
Database Specialist

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