Solved

TSQL how to get the most recent records by date and then group by an ID

Posted on 2008-10-06
6
1,037 Views
Last Modified: 2012-06-21
I have a table that basically takes inserts all day long. Every time a call agents phone changes status (ex: hangs up or places a call) a new row is inserted into this table. So for example agent 222 can have 20 rows inserted in 1 hour if he makes 20 calls. I need to find a way to get only the most recent records and the distinct ID's of all the agents. So we can see who is on the phone at the time of the query. I tried the following:

select top 50 * from agentStateDetail order by eventDateTime desc

This pulls the records but, agent 222 will show up a few times. I need a query that pulls the most recent rows with eventType ='5' and the distinct agent ID's. So I should probably get no more than 5 or 10 rows back.

I have attached the create statement for the table and the top 50 rows of the table.
CREATE TABLE [dbo].[AgentStateDetail](
	[agentID] [int] NOT NULL,
	[eventDateTime] [datetime] NOT NULL,
	[gmtOffset] [smallint] NOT NULL,
	[eventType] [tinyint] NOT NULL,
	[reasonCode] [smallint] NOT NULL DEFAULT (0),
	[profileID] [int] NOT NULL,
 CONSTRAINT [PK_AgentStatusDetail] PRIMARY KEY NONCLUSTERED

Open in new window

agentStateDetail-top50-data.txt
0
Comment
Question by:sqlagent007
[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
  • 3
  • 3
6 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22655418
select ad.* from AgentStateDetail ad
JOIN
(
select agentid, EventDateTime  = MAX(EventDateTime)
from agentstatedetail
where eventtype = 'S'
) g ON ad.agentid = g.agentid and ad.EventDateTime = g.EventDateTime
0
 
LVL 1

Author Comment

by:sqlagent007
ID: 22655443
Msg 8118, Level 16, State 1, Line 1
Column 'agentstatedetail.agentID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22655455
whoops...sorry.

select ad.* from AgentStateDetail ad
JOIN
(
select agentid, EventDateTime  = MAX(EventDateTime)
from agentstatedetail
where eventtype = 'S'
group by agentid
) g ON ad.agentid = g.agentid and ad.EventDateTime = g.EventDateTime
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 1

Author Comment

by:sqlagent007
ID: 22655507
thank you so much, I am much farther along, however there are still rows in the query from 2007. I would like only the rows from 2008, but obviously I can't hard code '2008' in the query. Do I need to do a where datediff or something?

thanks, I have attached the results.
afterQuery.txt
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22655531
OK, so if you only want the current year...

declare @Begin smalldatetime
set @Begin = '01/01/' + cast(year(getdate()) as varchar(4))

select ad.* from AgentStateDetail ad
JOIN
(
select agentid, EventDateTime  = MAX(EventDateTime)
from agentstatedetail
where eventtype = 'S'
group by agentid
) g ON ad.agentid = g.agentid and ad.EventDateTime = g.EventDateTime
where ad.EventDateTime >= @Begin
0
 
LVL 1

Author Comment

by:sqlagent007
ID: 22655559
Thank you so much!
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

622 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