Archives of the TeradataForum

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

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

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;

Cheers,

Dave

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

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