Query to find missing data

• Gerry Roston

  Right there with Babe

  Points: 786

  More actions

  September 8, 2016 at 2:09 pm

  #321282

  Apologies if this is not the proper forum...

  We have a data table whose columns are:

  ID

  DeviceID

  Value

  DateTime

  In theory, a new record is written by each device every n minutes. What I would like is a query to find cases in which a row was not written for a particular device. For example, if representative data looks like (with the value of n being 10 minutes):

  22, 4, 23.6, 2016-09-07 10:05:00

  27, 4, 22.2, 2016-09-07 10:15:01

  33, 4, 24.1, 2016-09-07 10:34:59

  47, 4, 23.7, 2016-09-07 10:45:02

  The query would return: ID=27 (the entry before the missing entry), ID=33 (the entry after the missing entry), or some other such indicator of a missing record. If multiple records are missing, only one return value would be needed.

  Can you offer some suggestions for a set-based approach for accomplishing this? (Doing this with a procedural approach is easy...) Thank you.

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  September 8, 2016 at 2:18 pm

  #1899922

  Here's some sample data and an example of how to use LAG to get you started.

  DECLARE @sometable table (ID int, DeviceID int, Value decimal(8,1), DT datetime);

  INSERT @sometable

  VALUES

  (22, 4, 23.6, '2016-09-07 10:05:00'),

  (27, 4, 22.2, '2016-09-07 10:15:01'),

  (33, 4, 24.1, '2016-09-07 10:34:59'),

  (47, 4, 23.7, '2016-09-07 10:45:02');

  SELECT *, TimeSinceLast = DATEDIFF(MINUTE, LAG(DT,1,DT) OVER (ORDER BY DT), DT)

  FROM @sometable

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Gerry Roston

  Right there with Babe

  Points: 786

  More actions

  September 8, 2016 at 3:39 pm

  #1899928

  Alan,

  Thank you! We were not familiar with the LAG command and that appears to do exactly as needed.

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  September 9, 2016 at 6:27 am

  #1900012

  It's a good practice to go through every new release and read up on what new functions have been added. Lots of pleasant surprises to be found.

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills