Solved

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

Posted on 2008-10-07
4
699 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

732 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