Solved

SQL - Getting most recent date from rows

Posted on 2009-07-14
13
414 Views
Last Modified: 2013-12-13
I have a complex query that I'm hoping to get smoe help on.

I have 2 Tables, linked together by IDs.  (agentID and resourceID)
One table contains a users information (ex: First Name, Last Name, Group Name, extension)
The second table contains a log of actions and a datetime field containing it's time of occurrance.

The problem that I am having is that 1 User, ex: John Doe, could actually have 7 different IDs, in Table 1, and several action logs linked to those ID in Table2 based on what Group he is in. (each group a user is in creates a new ID for them)

My query needs to ONLY pull the most recent datetime row for each users First and Last Name, regardless of what ID it is coming from. You will see that there is an extension that is common for each user which could be used to group each one together with all of their other records.

Here's an example of data:

Table1
resourceID  | FirstName  | LastName  | GroupName | extension |
10 | John | Doe |1  | 2002 |
11 | John | Doe | 2  | 2002 |
12 | John | Doe | 3  | 2002|
13 | Jane | Doe | 1  | 2005 |
14 | John | Smith | 1 |  2008 |
15 | John | Smith | 3 | 2008 |

Table 2
agentID    |   DateTime   | Status   | Event   |
10  |   2009-07-07 09:11:56   |   1   |   20   |
10  |   2009-07-05 06:12:45   |   1   |   30   |
11  |   2009-07-05 06:11:35   |   1   |   30   |
11  |   2009-07-05 06:12:55   |   1   |   10   |
12  |   2009-07-05 06:12:59   |   1   |   30   |
12  |   2009-07-07 06:04:23   |   2   |   10   |
12  |   2009-07-07 09:07:33   |   1   |   20   |
13  |   2009-07-06 09:11:14   |   1   |   20   |
14  |   2009-07-07 09:17:26   |   3   |   50   |


Results would be
John Doe | 2009-07-05 06:12:45   |   1   |   30   |
Jane Doe | 2009-07-06 09:11:14   |   1   |   20   |
John Smith | 2009-07-07 09:17:26   |   3   |   50   |

Any help would be great!
0
Comment
Question by:MrBaseball9
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24853215
can you please clarify if you are ussing mysql or mssql, and what version

note: this design is not really good (aka not normalized)

apart from that
select t1.firstname, t1.lastname, max(t2.datetime)
  from table1 t1
  left join table2 t2
    on t2.agentid = t1.resorceid
group by t1.firstname, t1.lastname

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24853221
SELECT FirstName+' '+ LastName, MaxDate , status, Event
FROM Table1 a
INNER JOIN  (SELECT AgentID, MAX(Datetime) maxDate FROM Table2 GROUP BY AgentID) b
INNER JOIN Table2 c on c.agentID = b.agentID and c.datetime = b.MaxDate
0
 

Author Comment

by:MrBaseball9
ID: 24853336
MSSQL and my query is going into  sqlsrv_query
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:MrBaseball9
ID: 24853378
PHP 5.2.10
0
 

Author Comment

by:MrBaseball9
ID: 24853480
NOTE: I need to collect all of the data from the row that contains the most recent DATE, per user.
0
 
LVL 14

Expert Comment

by:profya
ID: 24854191

SELECT FirstName + ' ' + LastName, b.[DateTime], b.Status, b.Event  FROM Tabl1 a INNER JOIN (SELECT AgentID, Max([DateTime]) As [DateTime], Status, Event FROM Table 2 GROUP BY AgentID, Status, Event) b ON a.AgentId=b.AgentId

Open in new window

0
 

Author Comment

by:MrBaseball9
ID: 24854347
No Dice so far on the queries.

A persons name "John Doe" has multiple entries in both tables. I need to pull out a row from Table2 that contains the most recent date for each user.

Below is the query that gets the most recent date and info based on agentID.
This is close, but since "John Doe" has several IDs, he's getting displayed several times.

SELECT p.resourceFirstName, p.resourceLastName, p.assignedTeamID, e.eventDateTime, e.eventType, e.reasonCode
FROM Resource p 
LEFT OUTER JOIN
AgentStateDetail e INNER JOIN
(SELECT agentID, MAX(eventDateTime) AS max_datetime
FROM AgentStateDetail
GROUP BY agentID) eMax ON eMax.agentID = e.agentID AND eMax.max_datetime = e.eventDateTime ON p.resourceID = e.agentID
WHERE     (e.agentID IS NOT NULL)
ORDER BY p.assignedTeamID, p.resourceLastName, p.resourceFirstName ASC

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24856138
SELECT  r.resourceFirstName,
        r.resourceLastName,
        r.assignedTeamID,
        a.eventDateTime,
        a.eventType,
        a.reasonCode
FROM    Resource r
        INNER JOIN AgentStateDetail a ON r.resourceID = e.agentID
        INNER JOIN ( SELECT p.resourceFirstName,
                            p.resourceLastName,
                            MAX(e.eventDateTime) MaxeventDateTime
                     FROM   Resource p
                            INNER JOIN AgentStateDetail e ON p.resourceID = e.agentID
                     GROUP BY p.resourceFirstName,
                            p.resourceLastName
                   ) d ON r.resourceFirstName = d.resourceFirstName
                          AND r.resourceLastName = d.resourceLastName
                          AND a.eventDateTime = d.MaxeventDateTime
ORDER BY r.assignedTeamID,
        r.resourceLastName,
        r.resourceFirstName ASC
0
 

Author Comment

by:MrBaseball9
ID: 24861569
ac,

This seems to be close, but when I run it through SQL Query Analyzer, I get the following message:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'e' does not match with a table name or alias name used in the query.
0
 

Author Comment

by:MrBaseball9
ID: 24861745
I just changed e.agentID to a.agentID and it worked like a charm as far as I can see. I will try and test teh query a little more but I think it's working!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24861953
Yes, you are right.  This line:
INNER JOIN AgentStateDetail a ON r.resourceID = e.agentID

Should be:
INNER JOIN AgentStateDetail a ON r.resourceID = a.agentID
0
 

Author Closing Comment

by:MrBaseball9
ID: 31603438
Thanks again!. I get lost on all of the inner joins!
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question