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

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
LVL 1
sqlagent007Asked:
Who is Participating?
 
chapmandewCommented:
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
 
chapmandewCommented:
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
 
sqlagent007Author Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
chapmandewCommented:
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
 
sqlagent007Author Commented:
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
 
sqlagent007Author Commented:
Thank you so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.