Solved

TSQL-How to get all rows within the last 15 minutes

Posted on 2008-10-07
4
702 Views
Last Modified: 2012-05-05
I have a table that logs phone data. This table gets a row inserted every time a person who is logged into the phone system changes their state. So if I pick up the phone or hang up the phone, each one of those events is logged in the table. The table has a column named EventDateTime. This has the datetime of each event.
I would like to pull all rows that are only 15 minutes old. I am having trouble with this. I have attached the create table statement and the top 1000 rows from my table. Based on when this question gets viewed, the expert will have to adjust the time values. I really need to do this by minute, so if 1 hour gets the results, please use 60 minutes.

Thanks!

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

top1000rows-10.07.2008.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
4 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22661486
Try this:
(I made 35 minutes to go 15 minutes before 1:00PM)
SELECT
	[agentID],
	[eventDateTime],
	[gmtOffset],
	[eventType],
	[reasonCode],
	[profileID]
FROM [AgentStateDetail]
WHERE [eventDateTime] >= DateAdd(mi, -35, getdate())

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22661488
Just adjust 35 until you get records you want. ;)
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22661491
Assuming your eventdatetime is written in local time, and not UTC:

select * from AgentStateDetail
where eventDatetime >= dateadd(n,-15,getdate())


If UTC

select * from AgentStateDetail
where eventDatetime >= dateadd(n,-15,getutcdate())
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22662111
Nice solution, Brandon.

>>Based on when this question gets viewed, the expert will have to adjust the time values.

I guess I read too much into that statement and tried to adjust.  Anyway, life goes on.  I will live to help another day.
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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