Solved

SQL - Getting most recent date from rows

Posted on 2009-07-14
13
412 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 142

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP not parsing ' character 12 40
Flattening heirachies 3 31
VB.NET 2008 - SQL Timeout 9 24
T-SQL: New to using transactions 9 31
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

810 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