Encontre intervalos de tempo ausentes em uma tabela
Eu tenho a tabela a seguir, que contém valores lidos a cada 15 minutos a partir de vários dispositivos diferentes:
ID DeviceID Date Value
----------------------------------------------
1 3 24.08.2011 00:00:00 0.51
2 3 24.08.2011 00:15:00 2.9
3 3 24.08.2011 00:30:00 0
4 3 24.08.2011 00:45:00 7.1
5 3 24.08.2011 01:00:00 1.05
6 3 24.08.2011 03:15:00 3.8
Eu gostaria de encontrar todas as lacunas na tabela para cada dispositivo, onde não há entradas, para um determinado mês. Para a tabela acima, o resultado deve ser algo assim:
DeviceID StartDate EndDate
-------------------------------------------------------
3 24.08.2011 01:00:00 24.08.2011 03:15:00
A tabela tem aproximadamente 35.000 dispositivos e 100 milhões de entradas.
Isso é o que eu tentei; é um pouco lento, mas retorna o que eu preciso. No entanto, além de sua velocidade, há outro problema: ele só encontra intervalos ausentes até a última entrada de um dispositivo no mês determinado; qualquer coisa depois disso será ignorada, por isso é possível perder um intervalo extra de valores ausentes.
SELECT
t2.Date AS StartDate
, t1.Date AS EndDate
FROM
TestTable t1
INNER JOIN TestTable t2 ON t1.DeviceID = t2.DeviceID
WHERE
(t2.Date = (SELECT MAX(Date) FROM TestTable t3 WHERE t3.Date < t1.Date AND t3.DeviceID = t1.DeviceID)
AND DATEDIFF(MINUTE, t2.Date, t1.Date) > 15)
AND t1.DeviceID = @id
AND DATEPART(YEAR, t1.Date) = @year AND DATEPART(MONTH, t1.Date) = @month