Hello I have a table called Logs which has 4 columns

IP varchar(30), Users varchar(30), Logdate (smalldatetime), Logtime
varchar(30)

Not best table design but its a quick project.

I need to find the most recent logdate and time for each user.

Example data

IP                   Users           LogDate                         LogTime
10.20.10.30     SamW          2006-04-09 00:00:00      12:00:35 AM
10.20.10.30     SamW          2006-04-08 00:00:00      12:00:35 AM
10.20.10.30     BilllllW          2006-04-08 00:00:00      12:00:35 AM
10.20.10.30     BilllllW         2006-04-09 00:00:00      12:00:35 AM


I need just the recent logdate and logtime for each user.

TIA

SELECT
     IP
   , Users
   , max( cast( LogDate AS varchar(11)) + LogTime )
FROM Logs
GROUP BY
     IP
   , Users

If LogTime is NOT 24 hour time, there may be some issues with this. But I think that this will move you in the direction you desire.

Show quoteHide quote

Why on earth are logdate and logtime in separate columns?

Getting the most recent date is easy:

SELECT Users, MAX(LogDate)
    FROM dbo.Logs
    GROUP BY Users;

Since you decided to split one piece of data into two, for some reason,
getting the most recent date and time is a little more complex:

SELECT Users, MAX(LogDate)
FROM
(
    SELECT Users,
    LogDate = CONVERT(DATETIME, CONVERT(CHAR(8), LogDate, 112) + '
'+LogTime)
    FROM dbo.Logs
) x
GROUP BY Users;



Show quoteHide quote

A different solution, just for variety.

CREATE TABLE Logs
(IP varchar(30),
Users varchar(30),
Logdate smalldatetime,
Logtime varchar(30))

INSERT Logs VALUES ('10.20.10.30', 'SamW',     '2006-04-09 00:00:00',
'12:00:35 AM')
INSERT Logs VALUES ('10.20.10.30', 'SamW',     '2006-04-08 00:00:00',
'12:00:35 AM')
INSERT Logs VALUES ('10.20.10.30', 'BilllllW', '2006-04-08 00:00:00',
'12:00:35 AM')
INSERT Logs VALUES ('10.20.10.30', 'BilllllW', '2006-04-09 00:00:00',
'12:00:35 AM')

SELECT *
  FROM Logs as L
WHERE NOT EXISTS
       (select * from Logs as X
         where X.Users = L.Users
           and (X.Logdate > L.Logdate
            or  convert(datetime,
                        convert(char(11),X.Logdate,112)
                      + X.Logtime)
                        convert(char(11),L.Logdate,112)
                      + L.Logtime) ))

Roy Harvey
Beacon Falls, CT

On Thu, 29 Jun 2006 11:59:01 -0700, Lontae Jones
<LontaeJo***@discussions.microsoft.com> wrote:

Show quoteHide quote

SQLXML 4.0 "timestamp" updategram question
Identity keys in a One-to-Zero-or-Many relationship
Insert in every ID
SQL Newbie
Iteration in a stored procedure