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

avatar image

Jennifer
36 3 3 5

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

avatar image

Benjamin
320 3 5 8

(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

avatar image

TG
1.8k 3 5

(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

avatar image

Madhivanan
1.1k 2 5 9

(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.

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:

x425
x80
x61

asked: Oct 12, 2009 at 05:55 PM

Seen: 5467 times

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

Copyright 2016 Redgate Software. Privacy Policy