Solved

SQL - Getting most recent date from rows

Posted on 2009-07-14
13
418 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

752 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