I have two date parameter.
@startTime = '2020-08-19 14:00'
@endTime = '2020-08-19 21:00'
My table is
table_test| id | time | value | device_id
1| 11 |2020-08-19 14:00| 45 | 1
2| 12 |2020-08-19 16:00| 85 | 1
3| 13 |2020-08-19 17:00| 14 | 1
4| 14 |2020-08-19 18:00| 28 | 1
5| 15 |2020-08-19 20:00| 72 | 1
6| 16 |2020-08-19 21:00| 02 | 1
I want to find data that is not in two given time intervals but also to select any column. For example, my output shoul be
*| device_id | time |
1| 1 |2020-08-19 15:00|
2| 1 |2020-08-19 19:00|
How I do ?
First of all, I tried internal table, I found which device_id
like that
SELECT device_id
FROM table_test
WHERE time between @startTime and @endTime
GROUP BY device_id
HAVING COUNT(*) < 8
And, in cursor insert internal table
OPEN contact_cursor;
FETCH NEXT FROM contact_cursor INTO
@deviceId;
WHILE @@FETCH_STATUS = 0
BEGIN -- BEGIN - contact_cursor
WHILE(@currentTime <= @endTime)
BEGIN -- WHILE
INSERT INTO @current_table VALUES (@deviceId,@currentTime)
SET @currentTime = dateadd(HOUR,1 ,@currentTime);
END -- WHILE
SET @currentTime = @startTime;
FETCH NEXT FROM contact_cursor INTO
@deviceId;
END -- END - contact_cursor
CLOSE contact_cursor;
DEALLOCATE contact_cursor;
After that found not exist value
SELECT cur.device_id cur.measurement_time FROM @current_table AS cur
LEFT JOIN table_test AS t ON t.device_id = cur.device_id and t.time = cur.time
WHERE t.time is null
But these are very costly for long periods of time. What can I do ?
Rather than a recursive solution, use a tally. Tally Tables are significantly faster than both a CURSOR
and a recursive Common Table Expression (see my post Speed Test: rCTE vs Tally for more on the subject). The reason being is that a Tally uses a set-based solution, something that RDBMS excel at. Conversely, RDBMS are not well designed for iterative tasks, which both a CURSOR
and rCTE are.
I build one inline here, however, you can create and store them locally for easy reuse. I also assume that you want this for multiple devices, so I have included a CTe which combines the times and device IDs.
DECLARE @StartTime smalldatetime,
@EndTime smalldatetime;
SET @startTime = '2020-08-19T14:00:00';
SET @endTime = '2020-08-19T21:00:00';
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT TOP(SELECT DATEDIFF(HOUR, @StartTime, @EndTime) + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
FROM N N1, N N2, N N3, N N4, N N5), --100,000 rows, 11.5 years~
Times AS(
SELECT DATEADD(HOUR, T.I, @StartTime) AS T
FROM Tally T),
Devices AS(
SELECT T.T
D.DeviceID
FROM Times T
CROSS JOIN (SELECT DISTINCT DeviceID FROM dbo.YourTable) D)
SELECT D.DeviceID,
D.T AS [Time]
FROM Devices D
LEFT JOIN dbo.YourTable YT ON D.T = YT.[Time]
AND D.DeviceID = YT.DeviceID
WHERE YT.id IS NULL;