Need to find the most recent date based off the most recent time

Question

question

Jennifer asked Oct 12, '09

Need to find the most recent date based off the most recent time

I have a table that keeps tracks of all errors logged for an application I am running. This table includes a column for the date the error occurred, a column for the time, and the error number. There can be several errors with the same error number logged. For example, the table can look like this:

ErrorNum     Date          Time                    
245          10/12/2009    01:04:12                    
244          10/12/2009    09:34:56                    
245          10/12/2009    15:12:26                    
244          10/11/2009    23:45:47

I am trying to query a list of each error number and the most recent date and time it occurred. So my results should be:

ErrorNum     Date          Time                    
245          10/12/2009    15:12:26                    
244          10/12/2009    09:34:56

I have tried 2 different queries. The first one is:

select errornum, max(logdate), max(logtime)                    
from log                    
group by errornum

But my results include each error number, and the most current date and the most current time. But it does not neccessarily mean the date and the time are from the same record. It just get the most recent date and the most recent time for each error number.

I also tried this query:

select * from Log                    
Where LogDate = (Select max(LogDate) from Log)                    
And LogTime = (Select max(LogTime) from Log                    
Where LogDate = (Select max(LogDate) from Log))

But my results only include 1 record that ahs the most current date and time and the time is based on the date. The second query works, but I need to get it for all error numbers and not just the one that is the most recent.

Each record has a column called "LogText" which is a description of the error. How can I view the LogText for each of the most recent errors? I tried to just add LogText after the Select statement and I added it in the Group By part too. Let me know if you have any suggestions

I also need to include the errormsg column that is in another table named Errors. Table Errors has a column for the ErrorNum and I need to get the ErrorMsg that matches up with each ErrorNum in the Errors table.

Please let me know if you have any suggestions.

Thanks, Jen

query datetime group-by

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

graz ♦ commented · Oct 12, 2009 at 11:15 PM

Jennifer, if you have additional information, please update your question. You can use this comment to say that your question is updated. If one of these answers works for you please check it as the answer. Thanks!

0 ·

Jennifer commented · Oct 13, 2009 at 07:49 PM

I have updated my question. Please help me with the last paragraph I added. Thanks!

0 ·

Madhivanan commented · Oct 14, 2009 at 02:49 AM

See my answer. You need to join the table error with the query I posted

0 ·

Answer 1 · 2009-10-12T18:33:05Z

Benjamin answered Oct 12, '09

NEW RESPONSE (based on new requirement in comment): In this new answer, I have added the LogText field and an additional JOIN to add that field to the most recent entries. If there were multiple entries with the exact same date and time, then you would see more than 1 entry for the same ErrorNum in that case. I hope this is the desired result, otherwise the query could be altered to only display a maximum of 1 result per ErrorNum regardless of how many logs occurred at the MAX date and time.

--here is the answer

SELECT            
    MostRecent.[ErrorNum],            
    MostRecent.[ErrorDateTime],            
    OriginalLog.[LogText]            
FROM            
    [dbo].[log] OriginalLog            
    INNER JOIN (            
    	SELECT            
    		[ErrorNum],            
    		MAX( DATEADD(ms, DATEDIFF(ms, 0, [LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [LogDate]))) ) AS [ErrorDateTime]            
    	FROM [dbo].[log]            
    	GROUP BY            
    		[ErrorNum]            
    	) AS MostRecent            
    ON OriginalLog.[ErrorNum] = MostRecent.[ErrorNum] AND            
    	MostRecent.[ErrorDateTime] = DATEADD(ms, DATEDIFF(ms, 0, OriginalLog.[LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, OriginalLog.[LogDate])))

--SQL Server 2008, full example --change the date and time types to datetime for SQL 2005 or 2000

CREATE TABLE [dbo].[log] (            
    [ErrorNum] int NOT NULL,            
    [LogDate] date NOT NULL,            
    [LogTime] time NOT NULL,            
    [LogText] varchar(128) NOT NULL )

--change the insert statement to individual inserts for SQL 2005 or 2000

INSERT INTO [dbo].[log] ([ErrorNum], [LogDate], [LogTime], [LogText])            
VALUES  (245, '10/12/2009', '01:04:12', 'Invalid input from form MyFormA'),            
    	(244, '10/12/2009', '09:34:56', 'Unable to save form data MyFormA'),            
    	(245, '10/12/2009', '15:12:26', 'Invalid input from form MyFormB'),            
    	(244, '10/11/2009', '23:45:47', 'Unable to save form data MyFormB')

--display the full resultset but concatenate the Date and Time fields

SELECT            
    [ErrorNum],            
    DATEADD(ms, DATEDIFF(ms, 0, [LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [LogDate]))),            
    [LogText]            
FROM [dbo].[log]

--now apply the GROUP BY and MAX functions

SELECT            
    MostRecent.[ErrorNum],            
    MostRecent.[ErrorDateTime],            
    OriginalLog.[LogText]            
FROM            
    [dbo].[log] OriginalLog            
    INNER JOIN (            
    	SELECT            
    		[ErrorNum],            
    		MAX( DATEADD(ms, DATEDIFF(ms, 0, [LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [LogDate]))) ) AS [ErrorDateTime]            
    	FROM [dbo].[log]            
    	GROUP BY            
    		[ErrorNum]            
    	) AS MostRecent            
    ON OriginalLog.[ErrorNum] = MostRecent.[ErrorNum] AND            
    	MostRecent.[ErrorDateTime] = DATEADD(ms, DATEDIFF(ms, 0, OriginalLog.[LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, OriginalLog.[LogDate])))

--clean up for the example

DROP TABLE [dbo].[log]

ORIGINAL RESPONSE: The trouble is you are MAXing the date and times separately. All you have to do is concatenate the LogDate and LogTime fields and then MAX that. --here is the answer

SELECT            
    [ErrorNum],            
    MAX( DATEADD(ms, DATEDIFF(ms, 0, [LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [LogDate]))) )            
FROM [dbo].[log]            
GROUP BY            
    [ErrorNum]

I am grateful to Scott White for his simple way to concantenate the fields. Since you have the date and time in separate fields, I went ahead and did my example in SQL Server 2008, however, I have made notations so you can easily alter the example for 2005 or 2000. Let me know if you need more help with this.

--SQL Server 2008, full example
--change the date and time types to datetime for SQL 2005 or 2000

CREATE TABLE [dbo].[log] (            
    [ErrorNum] int NOT NULL,            
    [LogDate] date NOT NULL,            
    [LogTime] time NOT NULL )

--change the insert statement to individual inserts for SQL 2005 or 2000

INSERT INTO [dbo].[log] ([ErrorNum], [LogDate], [LogTime])            
VALUES  (245, '10/12/2009', '01:04:12'),            
    	(244, '10/12/2009', '09:34:56'),            
    	(245, '10/12/2009', '15:12:26'),            
    	(244, '10/11/2009', '23:45:47')

--display the full resultset but concatenate the Date and Time fields

SELECT            
    [ErrorNum],            
    DATEADD(ms, DATEDIFF(ms, 0, [LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [LogDate])))            
FROM [dbo].[log]

--now apply the GROUP BY and MAX functions

SELECT            
    [ErrorNum],            
    MAX( DATEADD(ms, DATEDIFF(ms, 0, [LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [LogDate]))) )            
FROM [dbo].[log]            
GROUP BY            
    [ErrorNum]

--clean up for the example

DROP TABLE [dbo].[log]

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-10-12T18:24:49Z

TG answered Oct 12, '09

This code makes a few assumptions:

you have sql server 2005 or later
LogDate and LogTime are either numeric or datetimes

            
select errornum            
       --min/max doesn't matter here since just one value is returned by cross apply            
       ,max(LogDate) LogDate             
       ,max(LogTime) LogTime            
from   [Log] as l            
cross apply (select top 1 LogDate, logTime             
              from [Log]            
              where errorNum = l.errorNum            
              order by LogDate desc, LogTime desc            
       ) ca            
group by errornum

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-10-13T04:18:00Z

Madhivanan answered Oct 13, '09

Another method

declare @t table(ErrorNum     int,Date          varchar(100),Time varchar(100))            
insert into @t            
select 245,          '10/12/2009',    '01:04:12' union all            
select 244,          '10/12/2009',    '09:34:56' union all            
select 245,          '10/12/2009',    '15:12:26' union all            
select 244,          '10/11/2009',    '23:45:47'             
            
select t1.Errornum,date,time from (select *,cast(date+' '+time as datetime) as date_time from @t) as t1 inner join            
(            
select ErrorNum,max(cast(date+' '+time as datetime)) as date_time from @t            
group by ErrorNum            
) as t2 on t1.ErrorNum=t2.ErrorNum and t1.date_time =t2.date_time            
order by t1.date_time desc

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question