x

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

more ▼

asked Oct 12, 2009 at 05:55 PM in Default

Jennifer gravatar image

Jennifer
35 3 3 3

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!
Oct 12, 2009 at 11:15 PM graz ♦
I have updated my question. Please help me with the last paragraph I added. Thanks!
Oct 13, 2009 at 07:49 PM Jennifer
See my answer. You need to join the table error with the query I posted
Oct 14, 2009 at 02:49 AM Madhivanan
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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]
more ▼

answered Oct 12, 2009 at 06:33 PM

Benjamin gravatar image

Benjamin
320 2 3 4

Great, that worked perfectly! and it was pretty simple! I just have one other question. 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.

Thanks,
Jennifer
Oct 12, 2009 at 07:28 PM Jennifer

Hi Jennifer, I hope you also find this additional answer helpful and will mark this post accordingly. Thanks. Benjamin

I tried to enter the new answer here but I ran out of allowed characters. Please find my new answer in my editted response.
Oct 12, 2009 at 10:46 PM Benjamin
Thanks! It works perfect. I appreciate your help!
Oct 13, 2009 at 12:19 PM Jennifer
Hi Benjamin, I just remembered one more part that I need the query to do. I 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. I added this additional request to my original question.
Thanks!
Oct 13, 2009 at 01:54 PM Jennifer
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 12, 2009 at 06:24 PM

TG gravatar image

TG
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 13, 2009 at 04:18 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x371
x94
x37

asked: Oct 12, 2009 at 05:55 PM

Seen: 4394 times

Last Updated: Oct 20, 2009 at 02:57 PM