Solved

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

Posted on 2008-10-06
6
1,034 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 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