Selecting specific records in a date range.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting specific records in a date range.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-26 : 09:14:45

Edgardo writes "Edgardo writes "I have a database the contains server performance data. Every minute a server will upload it's data to the SQL server. I have a time field that stores the exact time and day each record was uploaded. Example


Counter     Time                 Value
------------------------------------------
Processor%  2002-03-07 13:13:13  89
Processor%  2002-03-07 13:14:13  50
Processor%  2002-03-07 13:15:13  25

I would like to be able to select records in a date range and pull only records that have a difference of say 15 minutes between them. Or if I care to be more detailed 3 minutes between them.

For example

Select * from perfdata where Time => '2002-03-07 13:13:13' and Time =< '2002-05-07 13:13:13'

And in that range only pull records that are 15 minutes apart."

Garth
SQLTeam Author

119 Posts

Posted - 2002-07-26 : 20:15:30

I *think* this will work...

CREATE TABLE PerfCounters
(
RowID int IDENTITY,
Counter varchar(15),
RecTime datetime,
Value int
)
go
INSERT PerfCounters VALUES ('Processor%','2002-03-07 13:13:13',89)
INSERT PerfCounters VALUES ('Processor%','2002-03-07 13:14:13',50)
INSERT PerfCounters VALUES ('Processor%','2002-03-07 13:15:13',25)
INSERT PerfCounters VALUES ('Processor%','2002-03-07 13:31:13',21)
INSERT PerfCounters VALUES ('Processor%','2002-03-07 13:32:13',40)
INSERT PerfCounters VALUES ('Processor%','2002-03-07 13:33:13',64)
INSERT PerfCounters VALUES ('Processor%','2002-03-07 14:33:13',30)
INSERT PerfCounters VALUES ('Processor%','2002-03-07 23:33:13',70)
INSERT PerfCounters VALUES ('Processor%','2002-03-08 00:00:01',76)

SELECT *,
DATEDIFF(mi,(SELECT RecTime FROM PerfCounters WHERE RowID=(a.RowID-1)),a.RecTime) AS TimeGap
FROM PerfCounters a
WHERE DATEDIFF(mi,(SELECT RecTime FROM PerfCounters WHERE RowID=(a.RowID-1)),a.RecTime)>15
ORDER BY RecTime

Garth
www.SQLBook.com

Edgardo
Starting Member

1 Post

Posted - 2002-08-01 : 10:43:10

Thanks for the responce, I've been breaking my head and came up with a dirty solution that works too using modulus.

Select * From perfdata where Counter = 'Processor%' and (DatePart(mi, Time)%10)=0 and Time > '2002-07-29 13:13:13' and Time < '2002-07-30 13:13:13' Order by time

I can just change the number each date divides into to get an interval of course it would be the same intervals over and over in this case 10 20 30 40 50 and 60 minute of every hour but that is fine and accomplishes what I need. Let me know what you think and thanks again.

Edited by - Edgardo on 08/01/2002 10:47:51

Edited by - Edgardo on 08/01/2002 10:51:04

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources