locked
Query to fetch record from database within 5 minutes RRS feed

  • Question


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

    Did you try my query, what do you mean by continuous time interval in sec or min (1,2,3,4...)

    it will give you Tags that are in continuous time interval and repeated for 5 times

    thanks

    Sarvana Kumar C

    Friday, July 25, 2014 5:04 PM