Найти / создать правильный конец для серии записей отслеживания

Это один из них, так что держись со мной.

У меня есть две таблицы, которые отслеживают людей в местах. Я успешно объединил их с помощью LEAD и LAG, чтобы создать бесшовный переход в одну таблицу.
Моя проблема сейчас в том, что для одной из таблиц есть дополнительные элементы активности, которые мне нужно включить, которые находятся в некоторых сегментах.
Поэтому для простоты я имею следующий нормальный случай:

| System | ID | Item | Start | End | Alpha | 987 | 123 | May, 20 2015 07:00:00 | May, 20 2015 08:00:00 | Alpha | 374 | 123 | May, 20 2015 08:00:00 | May, 20 2015 10:00:00 | Beta | 184 | 123 | May, 20 2015 10:00:00 | May, 20 2015 11:00:00 | Beta | 798 | 123 | May, 20 2015 11:00:00 | May, 20 2015 12:00:00 

Теперь эти дополнительные элементы помещаются в некоторые записи, поэтому данные, которые у меня есть, выглядят примерно так:

 | System | ID | Item | Start | End | Alpha | 987 | 123 | May, 20 2015 07:00:00 | May, 20 2015 08:00:00 | Alpha | 374 | 123 | May, 20 2015 08:00:00 | May, 20 2015 10:00:00 | Beta | 184 | 123 | May, 20 2015 10:00:00 | May, 20 2015 11:00:00 | Charlie | 874 | 123 | May, 20 2015 10:20:00 | May, 20 2015 10:25:00 | Charlie | 984 | 123 | May, 20 2015 10:37:00 | May, 20 2015 10:54:00 | Beta | 798 | 123 | May, 20 2015 11:00:00 | May, 20 2015 12:00:00 

Обратите внимание, что два события Charlie происходят в бета-версии 184.

В настоящее время я столкнулся с таблицей в каждую запись (необходимо для следующего шага, не спрашивайте) и пусть она отображает правильные записи в этой форме, но я не могу понять, как тогда верните его вместе и получите правильные начальные и конечные времена для каждого сегмента. Кроме того, как-то новые записи собираются сформировать, чтобы заполнить пробелы.

Мой желаемый конечный результат вышеупомянутой ситуации таков:

 | System | ID | Item | Start | End | Alpha | 987 | 123 | May, 20 2015 07:00:00 | May, 20 2015 08:00:00 | | Alpha | 374 | 123 | May, 20 2015 08:00:00 | May, 20 2015 10:00:00 | | Beta | 184 | 123 | May, 20 2015 10:00:00 | May, 20 2015 10:20:00 | | Charlie | 874 | 123 | May, 20 2015 10:20:00 | May, 20 2015 10:25:00 | | Beta | 184 | 123 | May, 20 2015 10:25:00 | May, 20 2015 10:37:00 | new | Charlie | 984 | 123 | May, 20 2015 10:37:00 | May, 20 2015 10:54:00 | | Beta | 184 | 123 | May, 20 2015 10:54:00 | May, 20 2015 11:00:00 | new | Beta | 798 | 123 | May, 20 2015 11:00:00 | May, 20 2015 12:00:00 | 

Имеет ли это смысл?

Надеюсь, кто-нибудь может мне помочь.

Вы можете использовать что-то вроде этого:

 DECLARE @Source TABLE ( [System] VARCHAR(50), ID INT PRIMARY KEY, Item INT NOT NULL, Start DATETIME NOT NULL, [End] DATETIME NOT NULL, CHECK (Start<[End]) ) INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Alpha', 987, 123, '2015-05-20 07:00', '2015-05-20 08:00') INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Alpha', 374, 123, '2015-05-20 08:00', '2015-05-20 10:00') --INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Delta', 555, 123, '2015-05-20 09:30', '2015-05-20 10:00') INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Beta', 184, 123, '2015-05-20 10:00', '2015-05-20 11:00') --INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Charlie', 111, 123, '2015-05-20 10:05', '2015-05-20 10:07') --INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Charlie', 222, 123, '2015-05-20 10:10', '2015-05-20 10:20') INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Charlie', 874, 123, '2015-05-20 10:20', '2015-05-20 10:25') INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Charlie', 984, 123, '2015-05-20 10:37', '2015-05-20 10:54') INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Beta', 798, 123, '2015-05-20 11:00', '2015-05-20 12:00') ;WITH CTE AS ( SELECT * FROM @Source s1 OUTER APPLY ( SELECT MIN(s2.Start) AS NextStart FROM @Source s2 WHERE s2.Start>s1.Start AND s2.Start<s1.[End] ) q2 OUTER APPLY ( SELECT MAX(s3.[End]) AS PreviousEnd FROM @Source s3 WHERE s3.[End]>s1.Start AND s3.[End]<s1.[End] ) q3 ) SELECT System, ID, Item, Start, [End] FROM CTE WHERE NextStart IS NULL AND PreviousEnd IS NULL UNION ALL SELECT System, ID, Item, Start, NextStart FROM CTE WHERE NextStart IS NOT NULL UNION ALL SELECT System, ID, Item, PreviousEnd, [End] FROM CTE WHERE PreviousEnd IS NOT NULL UNION ALL SELECT s4.System, s4.ID, s4.Item, q5.[End], q6.Start FROM @Source s4 CROSS APPLY ( SELECT * FROM @Source s5 WHERE s5.Start>s4.Start AND s5.Start<s4.[End] ) q5 CROSS APPLY ( SELECT TOP 1 * FROM @Source s6 WHERE s6.Start>q5.Start AND s6.Start<s4.[End] ORDER BY s6.Start ) q6 WHERE q5.[End]<q6.Start ORDER BY [Start] 

Первая часть UNION обрабатывает интервалы, которые не перекрываются никакими другими интервалами.

Вторая часть обрабатывает строки, которые перекрываются в конце интервала.

Третья часть обрабатывает строки, которые перекрываются в начале интервала.

Последняя часть создает промежуток между двумя другими интервалами, которые перекрываются с базовым интервалом (когда два интервала не смежны).

Кажется, @RazvanSocol избил меня, но так как я сделал это, и он выглядит проще, чем его, я тоже опубликую его здесь:

 create table #times ( Item int, EndTime datetime, primary key (Item, EndTime) ) insert into #times select distinct Item, StartTime from timetable union select distinct Item, EndTime from timetable ;with CTE as ( select System, ID, Item, StartTime from timetable T1 union all select T1.System, T1.ID, T1.Item, T2.EndTime from timetable T1 join timetable T2 on T1.Item = T2.Item and T1.StartTime < T2.StartTime and T1.EndTime > T2.EndTime where -- This check added to handle cases with adjacent ranges in the dates -- as pointed out by Razvan Socol not exists (select 1 from timetable T3 where T3.StartTime = T2.EndTime) ) select System, ID, Item, StartTime, E.EndTime from CTE outer apply ( select top 1 EndTime from #times T where T.Item = CTE.Item and T.EndTime > CTE.StartTime order by EndTime asc ) E order by Item, StartTime 

Я использовал темп. таблицу, чтобы собрать все разное время начала / окончания для каждого элемента, а затем использовать второй выбор в CTE для создания недостающих строк, а внешний применить в конце пересчитывает даты окончания для каждой строки, ища самую раннюю дату, найденную для этого элемента.

SQL Fiddle

Изменить: добавлена ​​проверка для соседних диапазонов

Попробуй это

Sql скрипка

 ;with cte as (select distinct t1.system,t1.ID,t1.item,t2.start, ROW_NUMBER() over(order by t2.start) rownum from timetable t1 right join (select system,start from timetable union all select system,[end] from timetable ) as t2 on t1.start = t2.start ) select case when c1.system is null then 'Beta' else c1.system end as system, case when c1.id is null then '184' else c1.id end as id, case when c1.item is null then '123' else c1.item end as item,c1.start,c2.start as [End] from cte c1 join cte c2 on c1.rownum = c2.rownum-1 and c1.start != c2.start order by c1.rownum; 
  • Заказ упорядоченных групп
  • Проблема с порядком по FOR XML в T-sql (предложение ORDER BY недопустимо в представлениях, встроенных функциях, производных таблицах)
  • упорядочение результатов запроса двумя столбцами
  • В SQL Server, по умолчанию детерминирован ТОЛЬКО при использовании в таблице с кластеризованным индексом?
  • Изменить порядок столбцов, отображаемых в результатах, без изменения порядка выбора
  • Tsql упорядочивает данные по определенным строковым значениям
  • SQL XML - 2 набора отдельных данных - ORDER BY & RowNumber
  • Interesting Posts

    Исключение было выбрано целью вызова в SSIS

    System.Data.SqlClient.SqlException: недопустимое имя столбца

    Использует ли скрипт SSDT для только измененных объектов?

    Использование рекурсивного запроса с помощью CTE

    Почему недавно созданные функции не могут быть найдены SQL Server?

    TSQL: как присоединить / объединить строки одного столбца в список CSV

    Эффективные вычисления в MS SQL Server с различным количеством входов

    Определение параметров подключения для другого соединения в SQL Server

    Установка переменной C # в значение значения с автоматическим приращением столбца в SQL?

    Получить дату Минус 1 день, но в точном 4:00

    Как разбить одну строку на несколько строк в SQL

    Структура отношений «многие ко многим» в SQL Server с дополнительным столбцом первичного ключа или без него?

    Размещается ли WHERE в другом положении в фазе логического запроса по сравнению с процессом физических запросов

    Конструктор отчетов SSRS 2012 не запускается с удаленного хоста – 401 Несанкционированный

    Является ли соединение в справочной таблице более оптимальным, чем пользовательская скалярная функция?

    Давайте будем гением компьютера.