MSSQL - Convert overlaping datetime ranges into non-overlapping datetime ranges with respect to priorities

Question

I have data stored in a table like this:

CREATE TABLE Records
(
  [Id] int IDENTITY(1,1) not null
  ,[From] datetime not null
  ,[To] datetime not null
  ,[Priority] int not null
)

Every row contains time record with from-to range and its priority.

I have following data:

| Id |                 From |                   To | Priority |
|----|----------------------|----------------------|----------|
|  1 | 2021-01-04T00:00:00Z | 2021-01-04T23:59:59Z |        0 |
|  2 | 2021-01-04T08:00:00Z | 2021-01-04T16:30:00Z |        1 |
|  3 | 2021-01-05T00:00:00Z | 2021-01-05T23:59:59Z |        0 |
|  4 | 2021-01-05T08:00:00Z | 2021-01-05T16:30:00Z |        1 |
|  5 | 2021-01-05T16:30:00Z | 2021-01-05T17:30:00Z |      100 |
|  6 | 2021-01-05T17:30:00Z | 2021-01-05T23:00:00Z |      100 |
|  7 | 2021-01-05T23:00:00Z | 2021-01-05T23:59:59Z |      100 |
|  8 | 2021-01-06T00:00:00Z | 2021-01-06T23:59:59Z |        0 |
|  9 | 2021-01-06T08:00:00Z | 2021-01-06T10:00:00Z |      100 |
| 10 | 2021-01-06T08:00:00Z | 2021-01-06T16:30:00Z |        1 |
| 11 | 2021-01-06T10:00:00Z | 2021-01-06T12:30:00Z |      100 |
| 12 | 2021-01-06T12:30:00Z | 2021-01-06T14:30:00Z |      100 |
| 13 | 2021-01-06T14:30:00Z | 2021-01-06T15:30:00Z |      100 |
| 14 | 2021-01-06T17:00:00Z | 2021-01-06T17:45:00Z |      100 |
| 15 | 2021-01-06T17:45:00Z | 2021-01-06T19:45:00Z |      100 |
| 16 | 2021-01-06T19:45:00Z | 2021-01-06T20:30:00Z |      100 |
| 17 | 2021-01-07T00:00:00Z | 2021-01-07T23:59:59Z |        0 |
| 18 | 2021-01-07T08:00:00Z | 2021-01-07T16:30:00Z |        1 |
| 19 | 2021-01-07T08:30:00Z | 2021-01-07T11:30:00Z |      100 |
| 20 | 2021-01-07T11:30:00Z | 2021-01-07T12:30:00Z |      100 |
| 21 | 2021-01-07T12:30:00Z | 2021-01-07T15:30:00Z |      100 |

It can be seen that there are records where from-to overlaps with other records. Records with bigger priority should overwrite records with smaller one.

Expected results should be:

| Id |                 From |                   To | Priority |
|----|----------------------|----------------------|----------|
|  1 | 2021-01-04T00:00:00Z | 2021-01-04T08:00:00Z |        0 |
|  2 | 2021-01-04T08:00:00Z | 2021-01-04T16:30:00Z |        1 |
|  1 | 2021-01-04T16:30:00Z | 2021-01-04T23:59:59Z |        0 |
|  3 | 2021-01-05T00:00:00Z | 2021-01-05T08:00:00Z |        0 |
|  4 | 2021-01-05T08:00:00Z | 2021-01-05T16:30:00Z |        1 |
|  5 | 2021-01-05T16:30:00Z | 2021-01-05T17:30:00Z |      100 |
|  6 | 2021-01-05T17:30:00Z | 2021-01-05T23:00:00Z |      100 |
|  7 | 2021-01-05T23:00:00Z | 2021-01-05T23:59:59Z |      100 |
|  8 | 2021-01-06T00:00:00Z | 2021-01-06T08:00:00Z |        0 |
|  9 | 2021-01-06T08:00:00Z | 2021-01-06T10:00:00Z |      100 |
| 11 | 2021-01-06T10:00:00Z | 2021-01-06T12:30:00Z |      100 |
| 12 | 2021-01-06T12:30:00Z | 2021-01-06T14:30:00Z |      100 |
| 13 | 2021-01-06T14:30:00Z | 2021-01-06T15:30:00Z |      100 |
| 10 | 2021-01-06T15:30:00Z | 2021-01-06T16:30:00Z |        1 |
|  8 | 2021-01-06T16:30:00Z | 2021-01-06T17:00:00Z |        0 |
| 14 | 2021-01-06T17:00:00Z | 2021-01-06T17:45:00Z |      100 |
| 15 | 2021-01-06T17:45:00Z | 2021-01-06T19:45:00Z |      100 |
| 16 | 2021-01-06T19:45:00Z | 2021-01-06T20:30:00Z |      100 |
|  8 | 2021-01-06T20:30:00Z | 2021-01-06T23:59:59Z |        0 |
| 17 | 2021-01-07T00:00:00Z | 2021-01-07T08:00:00Z |        0 |
| 18 | 2021-01-07T08:00:00Z | 2021-01-07T08:30:00Z |        1 |
| 19 | 2021-01-07T08:30:00Z | 2021-01-07T11:30:00Z |      100 |
| 20 | 2021-01-07T11:30:00Z | 2021-01-07T12:30:00Z |      100 |
| 21 | 2021-01-07T12:30:00Z | 2021-01-07T15:30:00Z |      100 |
| 18 | 2021-01-07T15:30:00Z | 2021-01-07T16:30:00Z |        1 |
| 17 | 2021-01-07T16:30:00Z | 2021-01-07T23:59:59Z |        0 |

Possible way how to solve it I have found on reddit, but it does not work on my dataset as you can see on SQL Fiddle Demo (with small change - on reddit lower priority beats higher priority, I need: higher priority beats lower priority).

Basically:

0:      ++++++++++++++++++++++++
1:              ----------       
100:                  |||||||
result: ++++++++------|||||||+++

Any suggestion/solution on how to solve it on MSSQL 2012?

I thought that it is common problem, however I was not able to find a solution on the internet yet.

Thanks in advance.

UPDATE1: Just to clarify my problem.

I have overlapping events like this:

+-----------------------------------------------------------------------+
| #ID:1#                       00:00-23:59                              |
+-----------------------------------------------------------------------+

                     +-----------------------------+
                     | #ID:2#  08:00-16:30         |
                     +-----------------------------+

And I need to get this (because ID2 has higher priority than ID1):

+--------------------+-----------------------------+--------------------+
| #ID:1# 00:00-08:00 | #ID:2# 08:00-16:30          | #ID:1# 16:30-23:59 |
+--------------------+-----------------------------+--------------------+

If ID2 had lower priority than ID1, it would be like this:

+-----------------------------------------------------------------------+
| #ID:1#                       00:00-23:59                              |
+-----------------------------------------------------------------------+

There can be many events in the same time with different priorities.

Answer 1

Based on your description and example, i think what you are looking for is this:

;with prep as (
select distinct [From] as timeValue from Records
union
select distinct  [To] from Records)
,intervals as (
select prep.timeValue as newFrom, lead(prep.timeValue) over (order by timeValue asc) as newTill
  from prep )
  select * 
  from intervals as intervals
  cross apply (select coalesce(max(priority),0) as  val
               from Records
               where Records.[From] = intervals.newFrom) as priority
where datediff(day,newFrom,newTill)=0

This splits overlapping time intervals, they dont cross dates (thats the way i understand the requirements) and gives priority based on their source.

Answer 2

We can formulate your query in the following way:

• We want all time intervals, for which there are no other time intervals that overlap AND also have a higher priority.
• We can define overlapping as: the start time of one interval is between the start and end time of another.
• Assuming here that two intervals starting and finishing coterminus are NOT overlapping.
• This is somewhat complicated by the fact that you appear to want the ID of the original record, so we cannot just filter out unnecessary records with WHERE

SELECT
    ID,
    [From] = ISNULL(priority.[From], r.[From]),
    [To] = ISNULL(priority.[To], r.[To])
FROM Records r
OUTER APPLY (
    SELECT TOP 1 *
    FROM Records priority
    WHERE priority.[From] >= r.[From]
        AND priority.[From] < r.[To]    -- Change to <= for coterminus
        AND priority.ID <> r.ID
    ORDER BY priority.Priority DESC
) priority

I would advise you to have an index with From as the leading column, and including To, Priority and ID.

Answer 3

I would prefer :

CREATE PROC P_LONG_RUNNING_QUERY @DELAY_SECOND SMALLINT
AS
SET NOCOUNT ON;
-->testing
IF NOT EXISTS(SELECT *
              FROM   sys.dm_exec_requests
              WHERE  DATEDIFF(second, start_time, getdate()) > @DELAY_SECOND)
   RETURN;
--> collecting
DECLARE @MSG NVARCHAR(max) = 
CAST((
SELECT Query.session_id as [SPID],
       Query.start_time as [Start Time],
       [SQL].text AS Statement
FROM   sys.dm_exec_requests AS Query
       CROSS APPLY sys.dm_exec_sql_text(Query.sql_handle) AS [SQL]
where  DATEDIFF(second, Query.start_time, getdate()) > @DELAY_SECOND
FOR XML AUTO, ELEMENTS, ROOT('LongRunningQuery')) AS nvarchar(max));
--> sending
exec msdb.dbo.sp_send_dbmail
   @profile_name = 'Test Alerts',
   @recipients = 'test@test.com',
   @subject = 'Warning',
   @body = @MSG;
GO