Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-10-07
4
Medium Priority
?
712 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 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

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 …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

604 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