Solved

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

Posted on 2008-10-06
6
1,035 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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