Solved

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

Posted on 2008-10-07
4
684 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
  • 3
4 Comments
 
LVL 59

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 59

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 59

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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 …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

825 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