Solved

SQL - Getting most recent date from rows

Posted on 2009-07-14
13
425 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 

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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

627 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