?
Solved

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

Posted on 2008-10-06
6
Medium Priority
?
1,044 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
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…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

571 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