How to eliminate date islands in a logging table?

Question

I have a logging functionality that is executed on each start and end of a table transfer process. It creates a separate entry for both start and stop:

Between each distinct table transfer there is a DB restore process that is not logged:

There is no feasible pattern in the execution (log) times that I can use and base my DATEDIFF on.

I have two questions:

How do I unite the logs for the TransferingTables process so that each logid line represent the proces start and end, opposite to two lines as is now?
How do I create an entry for the DB restore process that is not being logged?

Sample DDL and DML:

    CREATE TABLE #temptableTimeStamps
(
    [logid] INT,
    [procesid] INT,
    [Object] VARCHAR(17),
    [Started] DATETIME2(7),
    [Finished] DATETIME2(7)
);
INSERT INTO #temptableTimeStamps
(
    logid,
    procesid,
    Object,
    Started,
    Finished
)
VALUES
(1, 00000000, 'TransferingTables', N'2020-11-01T04:19:20.2666667', N'2020-11-01T04:19:20.2666667'),
(2, 00000000, 'TransferingTables', N'2020-11-01T20:23:36.39', N'2020-11-01T20:23:36.39'),
(3, 00000000, 'TransferingTables', N'2020-11-01T22:09:13.5766667', N'2020-11-01T22:09:13.5766667'),
(4, 00000000, 'TransferingTables', N'2020-11-02T03:48:34.6733333', N'2020-11-02T03:48:34.6733333'),
(5, 00000000, 'TransferingTables', N'2020-12-01T12:08:15.8466667', N'2020-12-01T12:08:15.8466667'),
(6, 00000000, 'TransferingTables', N'2020-12-01T15:31:42.4633333', N'2020-12-01T15:31:42.4633333'),
(7, 00000000, 'TransferingTables', N'2020-12-01T17:13:04.26', N'2020-12-01T17:13:04.26'),
(8, 00000000, 'TransferingTables', N'2020-12-01T20:16:52.25', N'2020-12-01T20:16:52.2633333'),
(9, 00000000, 'TransferingTables', N'2020-12-14T16:32:22.96', N'2020-12-14T16:32:22.96'),
(10, 00000000, 'TransferingTables', N'2020-12-14T23:08:09.5566667', N'2020-12-14T23:08:09.5566667'),
(11, 00000000, 'TransferingTables', N'2020-12-15T01:15:24.3766667', N'2020-12-15T01:15:24.3766667'),
(12, 00000000, 'TransferingTables', N'2020-12-15T11:39:43.1733333', N'2020-12-15T11:39:43.1733333');

SQL fiddle

user_4641928 · Answer

I believe I understand the logic you are trying to do, but here are examples outlined below to confirm

The start/stop of the first transfer process:

Begins at [Started] from row LogID = 1
Ends at [Finished] from row LogID = 2

The start/stop for the first restore process:

Begins at [Finished] from row LogID = 2
Ends at [Started] from row LogID = 3

Track Start/Stop of Each Process in 1 Row

;WITH cte_RowNumber AS (
    SELECT RowID = ROW_NUMBER() OVER (ORDER BY [Started])
        ,*
    FROM #temptableTimeStamps
),
Cte_Restore AS (
    SELECT A.*
        ,TransferStarted = CASE WHEN RowID % 2 = 1 THEN [Started] END
        ,TransferCompleted = Lead(Finished) OVER (PARTITION BY [Object],ProcesID ORDER BY LogID)
        ,DbRestoreStarted = CASE WHEN RowID % 2 = 0 THEN Finished END
        ,DbRestoreCompleted = Lead([Started]) OVER (PARTITION BY [Object],ProcesID ORDER BY LogID)
    FROM cte_RowNumber AS A
)
SELECT NewRowID = ROW_NUMBER() OVER (ORDER BY LogID) 
    ,A.[Object]
    ,B.*
FROM Cte_Restore AS A
CROSS APPLY (VALUES ('Transfer',TransferStarted,TransferCompleted)
                ,('Restore',DbRestoreStarted,DbRestoreCompleted)
            ) AS B(Activity,StartDatetime,EndDatetime)
Where StartDatetime IS NOT NULL
    --AND EndDatetime IS NOT NULL /*Use this to filter out last restore row with no end date yet*/
ORDER BY NewRowID

Results:

NewRowID	Object	Activity	StartDatetime	EndDatetime
1	TransferingTables	Transfer	2020-11-01 04:19:20.2666667	2020-11-01 20:23:36.3900000
2	TransferingTables	Restore	2020-11-01 20:23:36.3900000	2020-11-01 22:09:13.5766667
3	TransferingTables	Transfer	2020-11-01 22:09:13.5766667	2020-11-02 03:48:34.6733333
4	TransferingTables	Restore	2020-11-02 03:48:34.6733333	2020-12-01 12:08:15.8466667
5	TransferingTables	Transfer	2020-12-01 12:08:15.8466667	2020-12-01 15:31:42.4633333
6	TransferingTables	Restore	2020-12-01 15:31:42.4633333	2020-12-01 17:13:04.2600000
7	TransferingTables	Transfer	2020-12-01 17:13:04.2600000	2020-12-01 20:16:52.2633333
8	TransferingTables	Restore	2020-12-01 20:16:52.2633333	2020-12-14 16:32:22.9600000
9	TransferingTables	Transfer	2020-12-14 16:32:22.9600000	2020-12-14 23:08:09.5566667
10	TransferingTables	Restore	2020-12-14 23:08:09.5566667	2020-12-15 01:15:24.3766667
11	TransferingTables	Transfer	2020-12-15 01:15:24.3766667	2020-12-15 11:39:43.1733333
12	TransferingTables	Restore	2020-12-15 11:39:43.1733333	NULL

How to eliminate date islands in a logging table?

1 Answers

Track Start/Stop of Each Process in 1 Row

Results: