0

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.

2
  • Can you please try adding more details and explaining how you went from your starting dataset to your end result? It's not clear to me why the same Id is shown multiple times in the final results. Are the final results ordered by FromDate, ToDate, and then Priority? I believe you have a rather simple problem to solve, but I need more clarity to write out the solution to get you from your start to your end. – J.D. Jan 16 at 0:11
  • @J.D. Input results are (generally) unordred but here for better reading are ordered by From and then Priority. Final results are ordered by From. I will update my question to make it more clear. – Tom C. Jan 16 at 9:43
0

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 at 10:10
0

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.

2
  • Can you tell me more about your third assumption? – Tom C. Jan 17 at 22:11
  • It 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. See my comment if that is not what you want – Charlieface Jan 17 at 22:16
-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
2
  • 1
    I believe I do not want to send emails, maybe different question – Tom C. Jan 16 at 9:56
  • This appears to be unrelated to the question above. – Max Vernon Jan 16 at 19:49

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.