Sql-server – Convert overlapping datetime ranges into non-overlapping ranges with priorities

intervalsql serversql-server-2012

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 SQL Server 2012?

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

Just to clarify my problem, I have overlapping events like this:

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

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

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.

Best Answer

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 means we don't consider two intervals with one having To the same as the other From as being overlapping, they are just abutting each other.
  • 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.

Related Question