2

What is the best way to perform an SQL query to return all records where there is not another record with a matching id and a timestamp within X time?

So, for example, given the database table sample below and a timeframe of 5 hours, I would want the query to return the sample result.

Sample Data:

id | timestamp
01 | 2015-08-03 03:00:00
01 | 2015-08-03 14:00:00
01 | 2015-08-03 15:00:00
01 | 2015-08-03 16:00:00
02 | 2015-08-03 4:00:00
02 | 2015-08-03 5:00:00
03 | 2015-08-03 2:00:00
03 | 2015-08-03 5:00:00
03 | 2015-08-03 10:01:00

Sample Result:
01 | 2015-08-03 03:00:00
01 | 2015-08-03 16:00:00
02 | 2015-08-03 5:00:00
03 | 2015-08-03 5:00:00
03 | 2015-08-03 10:01:00

...since these are the only records for their respective ids where no other records with matching ids occur in the following 5 hours.

My current solution is to essentially query the entire table and then loop through each record querying on a local datatable. It works but is highly inefficient. Is there an SQL way of doing this? I have looked around for awhile, and I am coming up short. But, I might just not be searching correctly. There is probably some simple solution that I am missing, but I am still pretty new to SQL.

Thanks!

Update:
I should have included the current query I am using for reference. Note that "id" in the sample above really refers to "deviceid" in my actual query. It is:

SELECT id                                   AS 'ID', 
       eventsink.elevatortripevent.deviceid AS 'DeviceID', 
       completed                            AS 'Created', 
       digitalposition                      AS 'DigitalPosition', 
       buildingstate                        AS 'State' 
FROM   eventsink.elevatortripevent 
       JOIN md.elevator 
         ON md.elevator.deviceid = eventsink.elevatortripevent.deviceid 
       JOIN ea.elevatoreventevent 
         ON ea.elevatoreventevent.deviceid = 
            eventsink.elevatortripevent.deviceid 
WHERE  completed >= '8/2/2016 8:13:34 PM' 
       AND completed <= '8/3/2016 8:13:34 PM' 
ORDER  BY eventsink.elevatortripevent.deviceid 

Attempt at Implementing Answer by @irimias

SELECT id              AS 'ID', 
       trip1.deviceid  AS 'Device ID', 
       trip1.completed AS 'Created', 
       digitalposition AS 'Digital Position', 
       buildingstate   AS 'State' 
FROM   eventsink.elevatortripevent trip1 
       LEFT JOIN eventsink.elevatortripevent trip2 
              ON trip1.deviceid = trip2.deviceid 
                 AND trip1.completed < trip2.completed 
                 AND trip1.completed > Dateadd(hh, -5, trip2.completed) 
       JOIN md.elevator 
         ON md.elevator.deviceid = trip1.deviceid 
       JOIN ea.elevatoreventevent 
         ON ea.elevatoreventevent.deviceid = trip1.deviceid 
WHERE  trip2.deviceid IS NULL 
       AND trip1.completed >= '8/2/2016 8:13:34 PM' 
       AND trip1.completed <= '8/3/2016 8:13:34 PM' 
ORDER  BY trip1.deviceid 

Attempt at Implementing Answer by @dnoeth

WITH cte 
     AS (SELECT eventsink.elevatortripevent.deviceid, 
                completed, 
                Lead(completed) 
                  OVER ( 
                    partition BY eventsink.elevatortripevent.deviceid 
                    ORDER BY completed) AS nextTS 
         FROM   eventsink.elevatortripevent) 
SELECT id                                   AS 'ID', 
       cte.deviceid AS 'DeviceID', 
       completed                            AS 'Created', 
       digitalposition                      AS 'DigitalPosition', 
       buildingstate                        AS 'State' 
FROM   cte 
       JOIN md.elevator 
         ON md.elevator.deviceid = cte.deviceid 
       JOIN ea.elevatoreventevent 
         ON ea.elevatoreventevent.deviceid = 
            cte.deviceid 
WHERE  ( nextts IS NULL 
          OR completed < Dateadd(hh, -5, nextts) ) 
       AND completed >= '8/2/2016 8:13:34 PM' 
       AND completed <= '8/3/2016 8:13:34 PM' 
ORDER  BY cte.deviceid 
3

A direct translation to SQL will result in a NOT EXISTS:

select *
from tab as t1
where not exists  -- no other row
 ( select *
   from tab as t2
   where t2.id = t1.id   -- with the same id
     and t2.timestamp > t1.timestamp -- within the next five hours
     and t2.timestamp <= dateadd(hh, 5, ds2.timestamp)
 ) 

In fact this is quite similar to @irimias solution.

But SQL Server 2012 supports LEAD, which results in a more elegant solution, look for a gap greater than 5 hours:

with cte as
 ( 
   select id, timestamp,
      -- next row's timestamp
      lead(timestamp) over (partition by id order by timestamp) as nextTS
   from tab
 )
select *
from cte
where nextTS is null -- no next row, i.e. it's the last row
   or timestamp < dateadd(hh, -5, nextTS) -- gap to next row > 5 hours
  • ok, great, thanks! gotta step away for an hour or so, but i'll give it a shot in a bit. – Collin Barrett Aug 4 '16 at 16:44
  • @CollinM.Barrett: What's your SQL Server release? – dnoeth Aug 4 '16 at 16:46
  • ok, I'm back. I'm using 12.0.2000.8 – Collin Barrett Aug 4 '16 at 17:29
  • I just attempted your second solution (see edited question). Something is not quite right, though. I have lots of groups of records in the results that have the same (DeviceID,Created) pair. There shouldn't be any duplicate (DeviceID,Created) pairs. I probably made a mistake merging your solution with my query... – Collin Barrett Aug 4 '16 at 17:39
3

maybe you can try something like :

select ds1.*
from SampleData ds1
left join SampleData ds2 on ds1.id = ds2.id 
    and ds1.timestamp < ds2.timestamp
    and ds1.timestamp > dateadd(hh, -5, ds2.timestamp)
where ds2.id is null
  • Ok, thanks so much! I think this might be exactly what I need. I'm still testing to verify. Note, I just added my actual query to the question as well as an attempt to modify it to include your answer. Does it look like I did so correctly? Thanks! – Collin Barrett Aug 4 '16 at 16:35
  • My attempt at integrating your solution into my query resulted in a query that ran for an hour (until I cancelled it) and 0 results. I must have done something incorrectly... – Collin Barrett Aug 4 '16 at 17:43
  • @Colin : how big is your elevatortripevent table ? does it have indexes ? – irimias Aug 5 '16 at 7:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.