I have an application that logs data after around 1 second if the packet is healthy which means the logging is not guaranteed. Now the client wants to see the log data only with the gap of at least 1 minute. For example, this the sample schema and data
declare @mytable as table(ID int, tm DATETIME, Val int)
insert into @mytable values
(1, convert(DATETIME, N'2018-12-24 17:45:25.000', 21), 1),
(2, convert(DATETIME, N'2018-12-24 17:45:35.000', 21), 2),
(3, convert(DATETIME, N'2018-12-24 17:45:55.000', 21), 1),
(4, convert(DATETIME, N'2018-12-24 17:46:05.000', 21), 5),
(5, convert(DATETIME, N'2018-12-24 17:47:25.000', 21), 2),
(6, convert(DATETIME, N'2018-12-24 17:47:55.000', 21), 7),
(7, convert(DATETIME, N'2018-12-24 17:48:25.000', 21), 9),
(8, convert(DATETIME, N'2018-12-24 17:48:50.000', 21), 1),
(9, convert(DATETIME, N'2018-12-24 17:49:25.000', 21), 1),
(10, convert(DATETIME, N'2018-12-24 17:50:05.000', 21), 5),
(11, convert(DATETIME, N'2018-12-24 17:50:50.000', 21), 4),
(12, convert(DATETIME, N'2018-12-24 17:51:25.000', 21), 8),
(13, convert(DATETIME, N'2018-12-24 17:55:25.000', 21), 4),
(14, convert(DATETIME, N'2018-12-24 17:56:58.000', 21), 4),
(15, convert(DATETIME, N'2018-12-24 17:58:15.000', 21), 5),
(16, convert(DATETIME, N'2018-12-24 18:10:25.000', 21), 8);
The output should be
1, convert(DATETIME, N'2018-12-24 17:45:25.000', 21), 1
5, convert(DATETIME, N'2018-12-24 17:47:25.000', 21), 2
7, convert(DATETIME, N'2018-12-24 17:48:25.000', 21), 9
9, convert(DATETIME, N'2018-12-24 17:49:25.000', 21), 1
11, convert(DATETIME, N'2018-12-24 17:50:50.000', 21), 4
13, convert(DATETIME, N'2018-12-24 17:55:25.000', 21), 4
14, convert(DATETIME, N'2018-12-24 17:56:58.000', 21), 4
15, convert(DATETIME, N'2018-12-24 17:58:15.000', 21), 5
16, convert(DATETIME, N'2018-12-24 18:10:25.000', 21), 8
Here, after a qualified entry, next entry to qualify is to have at least 1 minute gap. However, I have to log all the healthy entries as in some other procedure I'll have to return all records. So skipping during logging is not an option.
Also, I know I can achieve it using a cursor but given the amount of data, this is not an acceptable solution.
Using recursive common table expression:
;WITH CTE (Id, tm, val)
AS
(
SELECT TOP 1 *
FROM @mytable m
WHERE m.ID = 1
UNION ALL
SELECT
p.Id,
p.tm,
p.val
FROM
(
SELECT
m.*,
ROW_NUMBER() OVER(ORDER BY c.Id) AS row_num
FROM CTE c
INNER JOIN @mytable m ON c.Id < m.ID AND DATEADD(MINUTE, 1, c.tm) <= m.tm
) AS p
WHERE p.row_num = 1
)
SELECT *
FROM CTE c
Id tm val
----------- ----------------------- -----------
1 2018-12-24 17:45:25.000 1
5 2018-12-24 17:47:25.000 2
7 2018-12-24 17:48:25.000 9
9 2018-12-24 17:49:25.000 1
11 2018-12-24 17:50:50.000 4
13 2018-12-24 17:55:25.000 4
14 2018-12-24 17:56:58.000 4
15 2018-12-24 17:58:15.000 5
16 2018-12-24 18:10:25.000 8
But, I'm not sure, this solution will work on big tables with many records. Maybe, you need some kind of post-processing after inserting in log table.