SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [Cycles](
    [Cycle] [int] NOT NULL,
    [CycleStart] [datetime] NOT NULL,
    [CycleEnd] [datetime] NOT NULL,
 CONSTRAINT [PK_Cycles] PRIMARY KEY CLUSTERED 
(
    [Cycle] DESC
))
INSERT INTO [Cycles] VALUES
(10,'2023-12-04 9:00:00','2023-12-04 10:00:00'),
(11,'2023-12-04 21:00:00','2023-12-04 22:00:00'),
(12,'2023-12-04 23:00:00','2023-12-05 00:00:00')
3 rows affected

CREATE TABLE [Data](
    [datatimestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY NONCLUSTERED 
(
    [datatimestamp] ASC
))
INSERT INTO [Data] VALUES
('2023-12-05 00:05:20'),
('2023-12-05 00:05:21'),
('2023-12-05 00:05:22'),
('2023-12-05 00:10:01'),
('2023-12-05 00:10:02'),
('2023-12-05 00:10:03')
6 rows affected

DECLARE @lastCycle int = (SELECT TOP 1 Cycle FROM Cycles ORDER BY Cycle DESC);
DECLARE @lastCycleEnd datetime = (SELECT TOP 1 CycleEnd FROM Cycles ORDER BY Cycle DESC);
WITH marks AS (
    SELECT datatimestamp, 
   CASE 
        WHEN DATEDIFF(Second, LAG(datatimestamp, 1, DATEADD(Second, -101, datatimestamp)) OVER (ORDER BY datatimestamp), datatimestamp) > 100 
        THEN 1 ELSE 0 
    END AS NextC
    FROM [Data] 
    WHERE datatimestamp > @lastCycleEnd 
), marks2 AS (
  SELECT m.DataTimeStamp, SUM(m.NextC) OVER (ORDER BY m.DataTimeStamp) AS Cycle
  FROM marks AS m)
  
SELECT @lastCycle + ROW_NUMBER() OVER (ORDER BY m.Cycle) AS Cycle, 
  MIN(m.datatimestamp) AS CycleBegin ,
  MAX(m.datatimestamp) AS CycleEnd
FROM marks2 m 
GROUP BY Cycle


Cycle
CycleBegin
CycleEnd


13
2023-12-05 00:05:20.000
2023-12-05 00:05:22.000

14
2023-12-05 00:10:01.000
2023-12-05 00:10:03.000

Cycle	CycleBegin	CycleEnd
13	2023-12-05 00:05:20.000	2023-12-05 00:05:22.000
14	2023-12-05 00:10:01.000	2023-12-05 00:10:03.000