Query to find missing data

Question

Post reply

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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