Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server
A table a like this
 

Date Number
-------------------------------------------------
2012-05-26 10:03:11.000 1
2012-05-26 10:03:54.000 3
2012-05-26 10:04:15.000 9
2012-05-26 10:04:33.000 6
2012-05-26 10:04:57.000 5
2012-05-26 10:05:34.000 7
2012-05-26 10:05:46.000 9
2012-05-26 10:06:24.000 8
2012-05-26 10:06:34.000 15
 
I want to get rows from this table by a time interval larger than 30s
those rows like this.
 
Date Number
-----------------------------------
2012-05-26 10:03:11.000 1
2012-05-26 10:03:54.000 3
2012-05-26 10:04:33.000 6
2012-05-26 10:05:34.000 7
2012-05-26 10:03:11.000 1
2012-05-26 10:06:24.000 8
 

 
Thanks
Posted 27-Dec-12 17:11pm
Comments
Sergey Alexandrovich Kryukov at 27-Dec-12 22:17pm
   
What do you mean by "get"? A query? Or create such rows? What did you try so far? What was the problem?
—SA
defygravity at 27-Dec-12 22:34pm
   
"get" means query.
Sergey Alexandrovich Kryukov at 27-Dec-12 22:37pm
   
OK, first of my questions is answered... :-)
—SA
defygravity at 27-Dec-12 22:36pm
   
I want to be able to query this table to select rows based on the date
 
So for example:
 
Select * from A where intervalBetweenTheRows = 30s or 35S etc
thanks
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi Defygravity
 
I create a table (t1) and fill the data as you mentioned, So now my table is look like this.
 
SELECT * FROM dbo.t1
D_Date                  ID
----------------------- -----------
2012-05-26 10:03:11.000 1
2012-05-26 10:03:54.000 2
2012-05-26 10:04:15.000 3
2012-05-26 10:04:33.000 4
2012-05-26 10:04:57.000 5
2012-05-26 10:05:34.000 6
2012-05-26 10:05:46.000 7
2012-05-26 10:06:24.000 8
2012-05-26 10:06:34.000 9
 
(9 row(s) affected)
 
Select Query to find......
 
SELECT a.D_Date , b.d_date, DATEDIFF(ss,a.D_Date , b.d_date) FROM t1 a, t1 b
WHERE a.ID = b.ID - 1
AND DATEDIFF(ss,a.D_Date , b.d_date) > 30
Output
D_Date                  d_date
----------------------- ----------------------- -----------
2012-05-26 10:03:11.000 2012-05-26 10:03:54.000 43
2012-05-26 10:04:57.000 2012-05-26 10:05:34.000 37
2012-05-26 10:05:46.000 2012-05-26 10:06:24.000 38
  Permalink  
Comments
defygravity at 28-Dec-12 1:49am
   
thank you for your help.but the output i prefer should be a little bit different
 

I just want to pick up some rows in original format under given interval .
And in the table i posted,the data under column "number" should be random values.
please give me more advices to solve this problem
P.S Vijay at 28-Dec-12 2:30am
   
As per my understanding, you want to compare row with a previous one, and fetch all dates which having 30 sec or more than that.... Right ?
 
But your mentioned output is not like that. Please look at below difference is shown in second .
D_Date Sec
----------------------- -----------
2012-05-26 10:03:11.000 43
2012-05-26 10:03:54.000 21
2012-05-26 10:04:15.000 18
2012-05-26 10:04:33.000 24
2012-05-26 10:04:57.000 37
2012-05-26 10:05:34.000 12
2012-05-26 10:05:46.000 38
2012-05-26 10:06:24.000 10
 
Please check and update me ... I am here.
defygravity at 28-Dec-12 3:38am
   
yes,that what i mean.but your idea is from last row to the first,right?
 
i want to do the job from first row to last,that means need to
compare row's date with given time,pick up the first one which time interval is more than 30s,and make this which picked up as the baseline to fetch the next row's date meets the condition,and do the loop until last or a given time
thanks.
P.S Vijay at 28-Dec-12 5:13am
   
Ohhh ...It is little bit challenging.. Give me some time, I will get back to you.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi Defygravity
 
I added a cursor over here.
 
CREATE TABLE T (D_Date Datetime)
 
DECLARE @P_Date Datetime
DECLARE @D_Date Datetime -- database name  
DECLARE @D_Date1 Datetime
DECLARE @Sec int
DELETE t
DECLARE db_cursor CURSOR FOR  
SELECT  D_Date FROM t1 
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @D_Date
	SET @P_Date = @D_Date 
	INSERT INTO T VALUES(@D_Date)
WHILE @@FETCH_STATUS = 0   
BEGIN  
PRINT @D_Date1 
		if (DATEDIFF(ss,@P_Date, @D_Date) >= 30)
			BEGIN 
				SET @P_Date = @D_Date
				INSERT INTO T VALUES(@P_Date)			
			END 
       
     FETCH NEXT FROM db_cursor INTO @D_Date
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor
 
SELECT * from T
 
Output is ....
 
D_Date
-----------------------
2012-05-26 10:03:11.000
2012-05-26 10:03:54.000
2012-05-26 10:04:33.000
2012-05-26 10:05:34.000
2012-05-26 10:06:24.000
  Permalink  
Comments
defygravity at 4-Jan-13 20:07pm
   
Thanks for your help ,and happy new year. Sorry, i had gone to a place where hard to find internet access.
 
May you do me a favor to show me the way to get whole rows from the table i paste here,not just the time column.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 337
1 DamithSL 310
2 OriginalGriff 220
3 Zoltán Zörgő 149
4 Peter Leow 115
0 OriginalGriff 7,510
1 DamithSL 5,519
2 Sergey Alexandrovich Kryukov 5,044
3 Maciej Los 4,961
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web01 | 2.8.141223.1 | Last Updated 28 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100