how to get rows by a given interval ?

Question

3.00/5 (2 votes)

See more:

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 16:11pm

defygravity

Add a Solution

Comments

Sergey Alexandrovich Kryukov 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 27-Dec-12 22:34pm

"get" means query.

Sergey Alexandrovich Kryukov 27-Dec-12 22:37pm

OK, first of my questions is answered... :-)
—SA

defygravity 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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

P.S Vijay · Answer 1 · 2012-12-27T18:20:00

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

CSS

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......

SQL

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

CSS

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

Posted 27-Dec-12 18:20pm

P.S Vijay

Comments

defygravity 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 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 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 28-Dec-12 5:13am

Ohhh ...It is little bit challenging.. Give me some time, I will get back to you.

P.S Vijay · Answer 2 · 2012-12-28T00:26:00

Hi Defygravity

I added a cursor over here.

SQL

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

SQL

SELECT * from T

Output is ....

CSS

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