2

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.

0

2 Answers 2

1

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.

0
-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.

1
  • Thank you for your answer but it does not work. Yes, input data does not cross dates. Your solution seems to fail when multiple events overlaps. It produces this row: 2021-01-06 15:30:00.000 2021-01-06 16:30:00.000 0 but it should be 2021-01-06 15:30:00.000 2021-01-06 16:30:00.000 1 And column with ID is missing
    – Tom C.
    Jan 16, 2021 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.