Экстраполировать перекрывающиеся периоды времени на до, перекрытие и после vin SQL

У меня есть данные, которые будут содержать перекрывающиеся даты в разных строках. Для каждых двух строк, содержащих перекрытие, мне нужно соответствующим образом разбить их следующим образом.

Прежде всего, данные выглядят следующим образом:

EMPID FIRSTNAME SURNAME ACTIVITY  START_DATE          END_DATE            HOURS_PER_DAY STATION
101   ANDREW    SMITH   AVAILABLE 2013-07-08 09:00:00 2013-07-08 17:00:00 8.00             LONDON
101   ANDREW    SMITH   SICKNESS  2013-07-08 07:00:00 2013-07-08 12:00:00 5.00             LONDON

Причина этого в том, что данные изначально поступали из двух отдельных таблиц, и я вставил содержимое обеих таблиц в одну.

Эти данные в конечном итоге будут сгруппированы по каждому человеку, предоставляя общее количество часов в день в столбце. В приведенной выше таблице у этого человека было бы 13 часов, но, как мы видим выше, временной интервал обоих периодов составляет всего 10 часов.

В приведенном выше примере мне нужно, чтобы результаты были разделены на 3 строки — бит до перекрытия, перекрытие и бит после перекрытия:

EMPID  NAME ACTIVITY  START_DATE          END_DATE            HOURS_PER_DAY STATION
101   JOHN  SICKNESS  2013-07-08 07:00:00 2013-07-08 09:00:00 2             LONDON
101   JOHN  SICKNESS  2013-07-08 09:00:00 2013-07-08 12:00:00 3             LONDON
101   JOHN  AVAILABLE 2013-07-08 12:00:00 2013-07-08 17:00:00 5             LONDON

Период времени, который перекрывается, должен классифицироваться как Болезнь. Обратите внимание, что есть и другие параметры, кроме «Доступно» и «Болезнь», но единственное, что будет перекрываться, — это «Болезнь» с любым другим типом, например. столкновение между «Болезнью» и «Доступностью» или столкновение между «Болезнью» и «Тренировкой».

Кроме того, данные уже разделены на 24-часовые периоды, т. е. я уже экстраполировал период болезни, который охватывает 4 дня, на 4 разных строки, поэтому столбец часов в день никогда не будет больше 24, если полный 24-часовой период присутствует, то дата окончания будет ровно через 1 день после даты начала - и даты начала, и даты окончания будут в полночь. Когда я попытался выполнить то, что требуется, я попытался сгруппировать данные по EMPID, NAME, CAST (START_DATE) AS DATE, CAST (END_DATE AS DATE) и STATION. Это соответствующие поля, которые определяют группировку, которую необходимо сравнить.

Обратите внимание, что в настоящее время насчитывается ок. 2500 строк данных, аналогичных предоставленным, и возможны следующие типы перекрытий (если ДОСТУПНАЯ запись начинается в 09:00 и заканчивается в 17:00 - обратите внимание, что это не так, я просто использую это как пример :

  • БОЛЕЗНЬ и ДОСТУПНОСТЬ начинаются и заканчиваются в одно и то же время, т.е. Болезнь начинается в 09:00 и заканчивается в 17:00 (Результаты 8 часов БОЛЕЗНИ)
  • БОЛЕЗНЬ начинается перед ДОСТУПНОЙ записью и заканчивается в ее середине: например. Болезнь начинается в 07:00 и заканчивается в 12:00 (Результаты: 2 часа БОЛЕЗНЬ, 3 часа БОЛЕЗНЬ за перекрытие, 5 часов ДОСТУПНО)
  • БОЛЕЗНЬ начинается в середине ДОСТУПНОЙ записи и заканчивается после ДОСТУПНОЙ записи, например. Болезнь начинается в 12:00 и заканчивается в 22:00 (Результаты: 3 часа ДОСТУПНО, 5 часов БОЛЕЗНЬ за перекрытие, 5 часов ДОСТУПНО)
  • БОЛЕЗНЬ начинается во время ДОСТУПНОЙ записи и заканчивается во время ДОСТУПНОЙ записи, например. Болезнь начинается в 11:00 и заканчивается в 14:00 (Результаты: 2 часа ДОСТУПНО, 3 часа БОЛЕЗНЬ на перекрытие, 3 часа ДОСТУПНО)
  • БОЛЕЗНЬ начинается перед записью ДОСТУПНО и заканчивается после записи ДОСТУПНО, например. Болезнь начинается в 06:00 и заканчивается в 18:00 (Результаты: 12-часовая болезнь)
  • Болезнь начинается на полпути через ДОСТУПНУЮ запись и заканчивается в то же время
  • Болезнь начинается в середине ДОСТУПНОЙ записи и заканчивается во время ДОСТУПНОЙ записи.

Еще раз обратите внимание, что вариант «ДОСТУПЕН» не единственный.

Мой запрос в настоящее время сводится к тому, что данные возвращаются в формате, указанном в верхней части этого поста, который хранится во временной таблице @INDIVIDUALDAYS. Теперь я хочу сделать все, что требуется для @INDIVIDUALDAYS, чтобы экстраполировать его, как описано выше.

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

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

Вот некоторые примеры данных, которые охватывают 7 различных описанных выше проникновений:

101 Andrew  Smith   Available   2014-08-19 09:00:00.000 2014-08-19 17:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-19 09:00:00.000 2014-08-19 17:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-20 12:00:00.000 2014-08-20 19:00:00.000 7.00    London
101 Andrew  Smith   Available   2014-08-20 09:00:00.000 2014-08-20 17:00:00.000 8.00    London
101 Andrew  Smith   Available   2014-08-21 09:00:00.000 2014-08-21 17:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-21 04:00:00.000 2014-08-21 12:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-22 06:00:00.000 2014-08-22 18:00:00.000 12.00   London
101 Andrew  Smith   Available   2014-08-22 09:00:00.000 2014-08-22 17:00:00.000 8.00    London
101 Andrew  Smith   Available   2014-08-23 09:00:00.000 2014-08-23 17:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-23 11:00:00.000 2014-08-23 14:00:00.000 3.00    London
101 Andrew  Smith   Available   2014-08-24 09:00:00.000 2014-08-23 17:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-24 09:00:00.000 2014-08-23 14:00:00.000 3.00    London
101 Andrew  Smith   Available   2014-08-25 09:00:00.000 2014-08-23 17:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-25 11:00:00.000 2014-08-23 17:00:00.000 3.00    London

sql sql-server date sql-server-2012 overlapping
person BHodgson    schedule 08.09.2014    source источник
comment
Разве вариант 3 выше не должен быть: БОЛЕЗНЬ начинается в середине ДОСТУПНОЙ записи и заканчивается после ДОСТУПНОЙ записи, например. Болезнь начинается в 12:00 и заканчивается в 22:00 (Результаты: 3 часа ДОСТУПНО, 5 часов БОЛЕЗНЬ за перекрытие, 5 часов БОЛЕЗНЬ). Если они больны, разве они не больны?   -  person LeeG    schedule 06.11.2014

Ответы (1)


arrow_upward
0
arrow_downward

Для этого сначала нужно соединить оба ряда вместе в 1 ряд. Когда все данные будут представлены в виде одной строки, вы можете выполнить серию запросов на объединение для построения результирующих строк, по одному запросу для каждого условия.

Это создает тестовые данные:

INSERT INTO TData
    ([ID], [FName], [LName], [Status], [StartTime], [EndTime], [Hours], [Location])
VALUES
    (101, 'Andrew', 'Smith', 'Available', '2014-08-19 09:00:00', '2014-08-19 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-19 09:00:00', '2014-08-19 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-20 12:00:00', '2014-08-20 19:00:00', 7.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-20 09:00:00', '2014-08-20 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-21 09:00:00', '2014-08-21 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-21 04:00:00', '2014-08-21 12:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-22 06:00:00', '2014-08-22 18:00:00', 12.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-22 09:00:00', '2014-08-22 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-23 09:00:00', '2014-08-23 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-23 11:00:00', '2014-08-23 14:00:00', 3.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-24 09:00:00', '2014-08-23 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-24 09:00:00', '2014-08-23 14:00:00', 3.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-25 09:00:00', '2014-08-23 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-25 11:00:00', '2014-08-23 17:00:00', 3.00, 'London')
;

И вот начнем с результата. Вам нужно добавить больше предложений UNION для каждой строки/условия, которые в конечном итоге будут отображаться.

;WITH Combined AS (
SELECT T1.*, t2.StartTime AS S_StartTime, t2.EndTime AS S_EndTime, 
        t2.Status AS S_Status, t2.Hours AS S_Hours
FROM TData T1 --Change TData to your table name
LEFT OUTER JOIN Tdata T2 ON T1.id = T2.id 
        AND CAST(T1.StartTime AS date) = CAST(T2.StartTime as date) 
        AND t1.Status <> t2.Status
WHERE T1.Status <> 'SICKNESS' 
)
--this is case 1 Show sickness only?  If not, add another row
SELECT ID, FName, LName, s_Status AS Status, 
        s_StartTime AS StartTime, s_EndTime AS EndTime, 
        S_Hours AS Hours, Location
FROM combined 
WHERE StartTime = S_StartTime and EndTime = s_EndTime 
UNION --Case 2 Row 1
SELECT ID, FName, LName, s_Status AS Status, 
        s_StartTime AS StartTime, StartTime AS EndTime, 
        DATEDIFF(minute,S_StartTime,StartTime)/60 AS Hours, Location
FROM combined 
WHERE S_StartTime < StartTime 
      AND S_EndTime BETWEEN StartTime AND EndTime 
UNION --case 2 row 2
SELECT ID, FName, LName, S_Status AS Status, 
       StartTime AS StartTime, S_EndTime AS EndTime, 
       DATEDIFF(minute,StartTime,S_EndTime)/60 AS Hours, Location
FROM combined 
WHERE S_StartTime < StartTime 
      AND S_EndTime BETWEEN StartTime AND EndTime 
UNION --case 2 row 3
SELECT ID, FName, LName, Status, 
       S_EndTime AS StartTime, EndTime AS EndTime, 
       DATEDIFF(minute,S_EndTime,EndTime)/60 AS Hours, Location
FROM combined 
WHERE S_StartTime < StartTime 
      AND S_EndTime BETWEEN StartTime AND EndTime

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

person LeeG    schedule 06.11.2014