How can I display the time interval in this dataset?

Question

SQL

Table with data set:

(Tag NVARCHAR(60), [DateTime] datetime)
diag_TCC.tag	2020-06-01 09:16:18.963
diag_TCC.tag	2020-06-03 16:01:03.810
diag_TCC.tag	2020-06-03 16:02:00.843
diag_TCC.tag	2020-06-03 16:03:00.813
diag_TCC.tag	2020-06-03 16:04:00.813
diag_TCC.tag	2020-06-03 16:05:00.817
diag_TCC.tag	2020-06-03 16:06:00.880
diag_TCC.tag	2020-06-03 16:07:00.823
diag_TCC.tag	2020-06-05 15:02:49.247

How to display the interval so that it turns out:

(Tag NVARCHAR(60), [StartTime] datetime,            [EndTime] datetime)
diag_TCC.tag             2020-06-03 16:01:03.810     2020-06-03 16:07:00.823

Answer 1

Taking into account the clarification of data interpretation, and also assuming that labels are only sequentially added, I suggest using an add trigger. Create a period table.

(Tag NVARCHAR(60), [StartTime] datetime, [EndTime] datetime)

In the trigger: if the label is more than 2 minutes from the previous one (it is easily determined: the last one is 1), then we add a line and put it into StartTime, if not, then into EndTime.
Or procedure:

CREATE PROCEDURE DowntimeCalculatePeriod
AS
  DECLARE @P TABLE ( ID INT IDENTITY, 
                     [Tag] NVARCHAR(60), [StartTime] DATETIME, [EndTime] DATETIME)
  DECLARE @DG1 NVARCHAR(60), @DT1 DATETIME
  DECLARE @DG0 NVARCHAR(60) = CHAR(0x19), @DT0 DATETIME = '2020'
  DECLARE @ID INT = -1
  DECLARE DowntimeCalc CURSOR FOR
    SELECT Tag, [DateTime] FROM Downtime ORDER BY 1, 2
  OPEN DowntimeCalc
  FETCH NEXT FROM DowntimeCalc INTO @DG1, @DT1
  WHILE @@FETCH_STATUS = 0 BEGIN
    IF ( @DG1 <> @DG0 ) OR (( @DG1 = @DG0 ) AND ( DATEDIFF( ss, @DT0, @DT1) > 90 )) BEGIN
      INSERT INTO @P ([Tag], [StartTime]) VALUES ( @DG1, @DT1 )
      SET @ID = SCOPE_IDENTITY()
    END ELSE BEGIN
      UPDATE @P SET [EndTime] = @DT1 WHERE ID = @ID
    END
    SET @DG0 = @DG1; SET @DT0 = @DT1
    FETCH NEXT FROM DowntimeCalc INTO @DG1, @DT1
  END
  CLOSE DowntimeCalc
  DEALLOCATE DowntimeCalc
  SELECT [Tag], [StartTime], [EndTime] FROM @P 
    WHERE NOT [EndTime] IS NULL 
    ORDER BY 1, 2
GO
--
EXECUTE DowntimeCalculatePeriod