CODEWITHSUNDEEP
sqlsql-server
urlreader
urlreader

Reputation: 6615

Find the overlap of the datetime interval in different rows?

I have a table which is a log of some tasks. Different task has different TaskConfigId. Each run of the task has a different TaskGuid. So same task but different run will have different TaskGuid. Each run will start with status 10 and will end with status 20. It could have other status between 10 to 20.

Different tasks are running based on schedule. Some run very frequently, some not. So, it could have multiple tasks running at the same time, but each task can only have ONE instance running at a time. Now, for some unknown reasons, sometimes, a task may have more than one instance running. We want to query the database to see whether this happened.

Here is a screen shot of the log, which is normal. When issue happened, it may have a row between 3468118 and 3468117 for TaskConfigId#100 whose TaskGuid is different. We want to find the case like that.

enter image description here

I'm think we need to Group By TaskConfigId and TaskGuid, then find the TaskTrackingId of the status 20 and 10 (say A and B), then check for the same TaskConfigId, whether there are TaskTrackingId between A and B.

SELECT TaskConfigId, TaskGuid
    , MAX(CreatedDate) AS [Max]
    , MIN(CreatedDate) AS [Min]
FROM [AptFeed].[TaskTracking]
GROUP BY TaskConfigId, TaskGuid
HAVING MAX(TaskStatusId) = 20 AND MIN(TaskStatusId) = 10
ORDER BY [Max] DESC

This way, I can find the task run's start and end datetime, but how can we use this to find whether the different task run have overlap?

It will be easy to do this in C#, but can we do this in SQL Server?

enter image description here

Upvotes: 0

Views: 72

Answers (1)

critical_error
critical_error

Reputation: 6726

With the understanding that there may be more factors to be considered in your environment, you could try something like this (runnable in SSMS):

/* TASKTRACKING MOCK-UP */
DECLARE @TaskTracking TABLE (
    TaskTrackingId INT,
    TaskConfigId INT,
    TaskGuid VARCHAR(50),
    TaskStatusId INT,
    CreatedDate DATETIME
);

/* TASKTRACKING DATA */
INSERT INTO @TaskTracking ( TaskTrackingId, TaskConfigId, TaskGuid, TaskStatusId, CreatedDate )
VALUES
    ( 3426760, 270, 'e881670a-e643-4b25-a4f4-dd30fc28bd23', 10, '2020-08-26 19:00:00.053' ), 
    ( 3424077, 270, '9ff 91e67-071b-48f2-bOdb-172d3e9f 234a', 20, '2020-08-26 17:19:30.740' ), 
    ( 3422102, 270, '9ff 91e67-071b-4812-bOdb-172d3e9f 234a', 10, '2020-08-26 16:52:36.443' ), 
    ( 3422101, 270, '6ef629ae-8ee1-44b7-aedd-43b246379e8f', 20, '2020-08-26 16:52:36.430' ), 
    ( 3411426, 270, '6ef629ae-8ee1-44b7-aedd-43b246379e8f', 10, '2020-08-26 14:26:01.613' ), 
    ( 3411425, 270, 'ce4f8688-5191-4735-a7e9-525781f3d72f', 20, '2020-08-26 14:26:01.600' ), 
    ( 3400704, 270, 'ce4f8688-5191-4735-a7e9-525781f3d72f', 10, '2020-08-26 12:00:00.190' ), 
    ( 3397311, 270, '7dbcc241-3c86-426f-9f6b-ef 12dfd5b2a1', 20, '2020-08-26 11:12:37.650' ), 
    ( 3397310, 270, '7dbcc241-3c86-426f-9f6b-ef 12dfd5b2a1', 10, '2020-08-26 11:12:37.620' ), 
    ( 3397309, 270, 'e46782caf 763-4580-89f 9-99da0d6a1762', 20, '2020-08-26 11:12:37.610' ), 
    ( 3397077, 270, 'e46782caf763-4580-89f9-99da0d6a1762', 10, '2020-08-26 11:09:47.410' ), 
    ( 3397076, 270, '7b433f62-6089-44ba-ad6c-eb767bd7e056', 20, '2020-08-26 11:09:47.400' ), 
    ( 3397075, 270, '1890732e-cec3-476-9f44-69d37105ee40', 20, '2020-08-26 11:09:47.400' ), 
    ( 3395100, 270, '1890732e-cec3-476-9f44-69d37105ee40', 10, '2020-08-26 10:42:42.740' ), 
    ( 3395099, 270, 'db05513a-20d5-4e40-96cc-c310ff1a0d4b', 20, '2020-08-26 10:42:42.730' ),
    ( 3394726, 270, '7b433f62-6089-44ba-ad6c-eb767bd7e056', 10, '2020-08-26 10:37:46.750' );

/* Return a list of tasks that have overlapping TaskConfigId values */
SELECT
    *
FROM (
    SELECT
        TaskConfigId,
        TaskGuid, 
        MIN ( CreatedDate ) AS MinDate,
        MAX ( CreatedDate ) AS MaxDate
    FROM @TaskTracking AS task
    GROUP BY
        TaskConfigId, TaskGuid
) AS Tasks
OUTER APPLY (

    SELECT 
        COUNT ( DISTINCT TaskGuid ) AS OverlapTasks,
        STRING_AGG ( TaskGuid, '|' ) AS OverlapGuids
    FROM @TaskTracking AS x
    WHERE
        x.TaskConfigId = Tasks.TaskConfigId
        AND x.CreatedDate > Tasks.MinDate AND x.CreatedDate <= Tasks.MaxDate
        AND x.TaskGuid <> Tasks.TaskGuid

) AS Overlaps
WHERE
    Overlaps.OverlapTasks > 0
ORDER BY
    MinDate;

Returns

+--------------+--------------------------------------+-------------------------+-------------------------+--------------+--------------------------------------------------------------------------------------------------------------+
| TaskConfigId |               TaskGuid               |         MinDate         |         MaxDate         | OverlapTasks |                                                 OverlapGuids                                                 |
+--------------+--------------------------------------+-------------------------+-------------------------+--------------+--------------------------------------------------------------------------------------------------------------+
|          270 | 7b433f62-6089-44ba-ad6c-eb767bd7e056 | 2020-08-26 10:37:46.750 | 2020-08-26 11:09:47.400 |            2 | 1890732e-cec3-476-9f44-69d37105ee40|1890732e-cec3-476-9f44-69d37105ee40|db05513a-20d5-4e40-96cc-c310ff1a0d4b |
|          270 | 1890732e-cec3-476-9f44-69d37105ee40  | 2020-08-26 10:42:42.740 | 2020-08-26 11:09:47.400 |            1 | 7b433f62-6089-44ba-ad6c-eb767bd7e056                                                                         |
+--------------+--------------------------------------+-------------------------+-------------------------+--------------+--------------------------------------------------------------------------------------------------------------+

Upvotes: 1

Related Questions