Query based on Time Interval - SQL Server Forums

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)

Query based on Time Interval

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-26 : 11:13:41

alex writes "I am trying to perform a query on a table that has the following columns:

Row_ID
DateTimeStamp
SomeValue

The end user would like to be able to query for the table for records that have an DateTimeStamp interval that is 5 minutes apart. The problem I am having is that When I do a DATEDIFF in the query against the minites in the the DateTimeStamp column, multiple records are returned. Here is the query I have been using to get to the point I am at now:

SELECT DATEDIFF(n, '7/9/2002 10:24:44', DateTimeStamp) AS minDiff, Row_ID,SomeValue
FROM MM_SIP64F_RunDetails
WHERE (DATEDIFF(n, '7/9/2002 10:24:44', DateTimeStamp) % 5 = 0)

'7/9/2002 10:24:44' is just an arbitrary date that I am using

The query current returns something like this:


0	7760	2002-07-09 10:24:44.000   1.1
0	7761	2002-07-09 10:24:44.000   2.2
0	7762	2002-07-09 10:24:54.000   3.2
5	7788	2002-07-09 10:29:04.000   4.2
5	7789	2002-07-09 10:29:14.000   5.2
5	7793	2002-07-09 10:29:54.000   6.2
10	7820	2002-07-09 10:34:04.000   7.2
10	7821	2002-07-09 10:34:14.000   9.2
10	7822	2002-07-09 10:34:24.000   9.2
15	7852	2002-07-09 10:39:04.000   10.2
15	7856	2002-07-09 10:39:44.000   11.2
15	7857	2002-07-09 10:39:54.000   12.2

I would like it to return the first occurance of the matching interval like this:


0	7760	2002-07-09 10:24:44.000   1.1
5	7788	2002-07-09 10:29:04.000   4.2
10	7820	2002-07-09 10:34:04.000   7.2
15	7852	2002-07-09 10:39:04.000   10.2

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-26 : 21:28:43

Try mixing in min(Row_ID) and GROUP BY. You might need to do a correlated subquery in the WHERE clause to restrict the rows.

alexc
Starting Member

10 Posts

Posted - 2002-08-27 : 10:31:23

I have tried min(Row_ID) and Group By without any luck. I have tried several different subqueries and still have nto found one that works. I hope someone comes along that can solve this problem.

Alex

quote:

Try mixing in min(Row_ID) and GROUP BY. You might need to do a correlated subquery in the WHERE clause to restrict the rows.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-27 : 10:42:08

you almost had it :)

DECLARE @t TABLE (Row_ID INT IDENTITY, DateTimeStamp DATETIME, SomeValue DECIMAL(38, 8))
DECLARE @i INT

SET @i = 0
WHILE @i < 12
BEGIN
INSERT INTO @t (DateTimeStamp, SomeValue) VALUES (DATEADD(n, (@i % 5) * 5, '2002-07-09 10:24:44.000'), @i * RAND() + 1)
SET @i = @i + 1
END

SELECT minRows.minDiff, t.Row_ID, t.SomeValue
FROM @t AS t
INNER JOIN (SELECT DATEDIFF(n, '7/9/2002 10:24:44', DateTimeStamp) AS minDiff, MIN(Row_ID) AS Row_ID FROM @t WHERE (DATEDIFF(n, '7/9/2002 10:24:44', DateTimeStamp) % 5 = 0) GROUP BY DATEDIFF(n, '7/9/2002 10:24:44', DateTimeStamp)) minRows ON t.Row_ID = minRows.Row_ID

alexc
Starting Member

10 Posts

Posted - 2002-08-27 : 10:49:02

Thanks,

Thats exactly what I needed.

Alex

quote:

you almost had it :)

DECLARE @t TABLE (Row_ID INT IDENTITY, DateTimeStamp DATETIME, SomeValue DECIMAL(38, 8))
DECLARE @i INT

SET @i = 0
WHILE @i < 12
BEGIN
INSERT INTO @t (DateTimeStamp, SomeValue) VALUES (DATEADD(n, (@i % 5) * 5, '2002-07-09 10:24:44.000'), @i * RAND() + 1)
SET @i = @i + 1
END

SELECT minRows.minDiff, t.Row_ID, t.SomeValue
FROM @t AS t
INNER JOIN (SELECT DATEDIFF(n, '7/9/2002 10:24:44', DateTimeStamp) AS minDiff, MIN(Row_ID) AS Row_ID FROM @t WHERE (DATEDIFF(n, '7/9/2002 10:24:44', DateTimeStamp) % 5 = 0) GROUP BY DATEDIFF(n, '7/9/2002 10:24:44', DateTimeStamp)) minRows ON t.Row_ID = minRows.Row_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources