Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 21 Jul 2011 @ 18:16:32 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Last logon for each user
From:   DWellman

Sorry, but that query may not work (sometimes it will, it all depends on the data). It is not reliable because you are executing the MAX function against the LogDate and LogTime columns separately.

Assume that the last two occasions when a particular user logged on were as follows:

     10:30  20/07/2011
     10:40  19/07/2011

Your query below will return the following values Username Logoff 20/07/2011 10:40

(which isn't a date/time when they actually logged on).

When you have date and time in two columns and you need to find the latest date/time when an event happened you have to combine the two columns into a single value (representing a date AND time) and then do the MAX on that. One way of doing this is:

     Select username,max(  (logdate (format 'yyyy-mm-dd')) || (logtime (format
     '99:99:99')) ) as LogonDateTime
     From dbc.logonoff
     Where event = 'logon'
     Group by 1;



Ward Analytics Ltd - Information in motion (www.ward-analytics.com)

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023