Remove From My Forums

Query to fetch record from database within 5 minutes

Question
0

Sign in to vote
suppose this is my Table in which records are inserted in every second now i want to fetch records or TagID from this table who is continuously inserted in database
or
whose TagID is inserted in database within 5 seconds
Here TagId Q11 is repeatedly inserted in database .
so i want a query or function which can retrive record or TagId from database who is continuously inserted within 5 secons
please help me..................

TagID Antenna DATETIME Q11 1 07/23/2014 11:10:01 pm Q11 1 07/23/2014 11:10:02 pm Q11 1 07/23/2014 11:10:03 pm Q11 1 07/23/2014 11:10:04 pm Q11 1 07/23/2014 11:10:05 pm Q11 1 07/23/2014 11:10:06 pm Q11 1 07/23/2014 11:10:07 pm Q11 1 07/23/2014 11:10:08 pm Q22 2 07/23/2014 11:10:09 pm Q22 2 07/23/2014 11:10:10 pm Q22 2 07/23/2014 11:10:12 pm Q22 2 07/23/2014 11:10:13 pm
Wednesday, July 23, 2014 9:14 AM

Answers

Try this:

DECLARE @T TABLE 
(TagID CHAR(3),crdate DATETIME)
INSERT INTO @T 
 
SELECT 'Q11','07/23/2014  11:10:01 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:02 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:03 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:04 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:05 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:06 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:07 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:08 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:09 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:10 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:12 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:13 pm' 

SELECT *
  FROM @T
 WHERE crdate >= DATEADD(SECOND,-5,(SELECT MAX(crdate) FROM @t))

Proposed as answer by Elvis Long Tuesday, July 29, 2014 9:31 AM
Marked as answer by Elvis Long Thursday, July 31, 2014 11:25 AM

Friday, July 25, 2014 4:44 PM

This might also be what you're looking for:

DECLARE @T TABLE 
(TagID CHAR(3),crdate DATETIME)
INSERT INTO @T 
 
SELECT 'Q11','07/23/2014  11:10:01 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:02 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:03 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:04 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:05 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:06 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:07 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:08 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:09 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:10 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:12 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:13 pm' 

SELECT *
  FROM (
        SELECT tagid, crdate AS d1, 
         LEAD(crdate,1) OVER (PARTITION BY TagID ORDER BY crdate) AS d2, 
         LEAD(crdate,2) OVER (PARTITION BY TagID ORDER BY crdate) AS d3, 
         LEAD(crdate,3) OVER (PARTITION BY TagID ORDER BY crdate) AS d4, 
         LEAD(crdate,4) OVER (PARTITION BY TagID ORDER BY crdate) AS d5
          FROM @T
	   ) a
 WHERE d5 IS NOT null

Proposed as answer by Elvis Long Tuesday, July 29, 2014 9:31 AM
Marked as answer by Elvis Long Thursday, July 31, 2014 11:25 AM

Friday, July 25, 2014 4:49 PM

All replies

if you want to check for every 5 second interval :

DECLARE @T TABLE 
(TagID CHAR(3),Antenna DATETIME)
INSERT INTO @T 
 
SELECT 'Q11','07/23/2014  11:10:01 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:02 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:03 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:04 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:05 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:06 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:07 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:08 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:09 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:10 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:11 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:12 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:13 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:14 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:15 pm'

;with cte as 
(
SELECT  * , DATEPART(second,Antenna) as sec
FROM @T 
)
SELECT tagid
FROM Cte c
GROUP BY tagid,(sec - 1) / 5
HAVING COUNT(*) = 5

Thanks and regards, Rishabh K

Wednesday, July 23, 2014 9:50 AM

Try below

this will search for continous 5 records with 1 second different

DECLARE @T TABLE 
(TagID CHAR(3),crdate DATETIME)
INSERT INTO @T 
 
SELECT 'Q11','07/23/2014  11:10:01 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:02 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:03 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:04 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:05 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:06 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:07 pm' UNION ALL
SELECT 'Q11','07/23/2014  11:10:08 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:09 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:10 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:12 pm' UNION ALL
SELECT 'Q22','07/23/2014  11:10:13 pm' 

select distinct tagid from @T b
where exists(select top 1 1 from @T a where a.tagid = b.tagid and a.crdate=dateadd(ss,1,b.crdate))
and exists(select top 1 1 from @T a where a.tagid = b.tagid and a.crdate=dateadd(ss,2,b.crdate))
and exists(select top 1 1 from @T a where a.tagid = b.tagid and a.crdate=dateadd(ss,3,b.crdate))
and exists(select top 1 1 from @T a where a.tagid = b.tagid and a.crdate=dateadd(ss,4,b.crdate))

Thanks

Saravana Kumar C

Edited by SaravanaC Wednesday, July 23, 2014 10:27 AM

Wednesday, July 23, 2014 10:27 AM

0

Sign in to vote

your Query is Great but it searches all records which are in continuous time interval
Means its searches those tags also which are in continuous time interval
1 sec ,2 sec, 3 sec

I want that it search only those Tags that are in continuous time interval and repeated for 5 times also

Friday, July 25, 2014 6:11 AM
0

Sign in to vote

Can you clarify a touch more on the input/output expectations?

Let me lay out some assumptions; tell me if I'm on target or now.

Tagid "X1" has records at 3:59:58 and 4:00:03 and nothing else within a five second interval.

TagID "X1" also has records every second from 5:59:58 until 6:01:20 on one second intervals.

TagID "X1" also has records every half second from 7:59:59 until 8:03:00.

What output are you wanting to see? Let me use the terminology of "echo record" to refer to a record that gets posted again within a 5 second interval.

For the first case, do you want to see only one record indicating X1 had starting at 3:59:58, with a count of echo records?

For the second case, do you want to see one output record indicating the start time of the sequence and a count of echo records? Or do you want to see one output record for each 5 second block?

For the third case, any special considerations for how frequently the records appear?

Do the 5 second time intervals need to start on 00, 05, 10... or do any records within 5 contiguous seconds count (e.g. n:n:03 and n:n:06)?

With a little more detail on expected output and rules, I'm sure the group can get you to a satisfactory query.

Friday, July 25, 2014 4:35 PM