SQL Server: поиск пробелов в занятости - проблема острова и пробела

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

Объяснение структур данных

У меня есть следующие таблицы:

Таблица должностей (zz_position), которая используется для хранения сведений о должности (идентификатор задания), включает диапазон дат, для которого она действительна.

PosNo   Description                Date_From    Date_To 
---------------------------------------------------------
10001   System Administrator       20170101     20231231

Таблица ресурсов (zz_resource), которая используется для хранения сведений о ресурсе (сотруднике), включая дату, когда он присоединился к компании и покинул ее.

resID   description  date_from   date_to
------------------------------------------
100     Sam          20160101    20991231
101     Joe          20150101    20991231

Таблица занятости (zz_employment), которая используется для привязки должности к ресурсам в пределах даты от и до диапазона

PosNo    resID       Date_From   Date_To     seqNo
---------------------------------------------------
10001    100         20180101    20180401    1
10001    101         20180601    20191231    2
10001    100         20200101    20991231    3

Проблема

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

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

PosNo   resID      Date_From   Date_To    seqNo
-------------------------------------------------
10001   NULL       20170101    20171231   0
10001   100        20180101    20180401   1
10001   NULL       20180402    20180530   0
10001   101        20180601    20191231   2
10001   100        20200101    20231231   3


insert into zz_employment 
values ('10001', '100', '2018-01-01 00:00:00.000', '2018-04-01 00:00:00.000', 1),
       ('10001', '101', '2018-06-01 00:00:00.000', '2019-12-31 00:00:00.000', 2),
       ('10001', '100', '2020-01-01 00:00:00.000', '2099-12-31 00:00:00.000', 3)

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

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

Правила

  • все даты усекаются до datetime, так что date_to ссылается на дату окончания, а не на дату и время окончания
  • если у поста/работы/ресурса нет даты окончания, то он будет обозначаться как 20991231
  • если сама занятость открыта, то дата в таблице занятости обозначается как 20991231, даже если сама должность может закончиться в 20231231. В идеале я хотел бы, чтобы результат соответствовал дате окончания должности.

SQL-код:

CREATE TABLE zz_position  
(
     posNo varchar(25) NOT NULL,  
     description varchar(25) NOT NULL,  
     date_from datetime NULL,  
     date_to datetime NULL
) 

insert into zz_position 
values ('10001', 'System Administrator', '2017-01-01 00:00:00.000', '2020-12-31 00:00:00.000')
go

CREATE TABLE zz_resource
(
     resID varchar(25) NOT NULL,  
     description varchar(25) NOT NULL,  
     date_from datetime NULL,  
     date_to datetime NULL
)  

insert into zz_resource 
values ('100', 'Sam', '2016-01-01 00:00:00.000', '2099-12-31 00:00:00.000'),
       ('101', 'Joe', '2015-01-01 00:00:00.000', '2099-12-31 00:00:00.000')
go

CREATE TABLE zz_employment
(
      posNo varchar(25) NOT NULL,  
      resID varchar(25) NOT NULL,  
      date_from datetime NULL,  
      date_to datetime NULL,
      seqNo int NULL
)  

insert into zz_employment 
values ('10001', '100', '2018-01-01 00:00:00.000', '2018-04-01 00:00:00.000', 1),
       ('10001', '101', '2018-06-01 00:00:00.000', '2019-12-31 00:00:00.000', 2),
       ('10001', '100', '2020-01-01 00:00:00.000', '2099-12-31 00:00:00.000', 3)

sql-server tsql gaps-and-islands
person Reky    schedule 19.02.2019    source источник
comment
Спасибо за DDL и DML. Однако в качестве полезного совета избегайте формата yyyy-MM-dd( hh:mm:ss) с типом данных datetime; SQL Server интерпретирует его как yyyy-dd-MM( hh:mm:ss), что означает, что некоторые из этих значений не удастся вставить. yyyyMMdd и yyyy-MM-ddThh:mm:ss будут работать независимо от языка и типа данных.   -  person Larnu    schedule 19.02.2019
comment
Спасибо за это larnu, запомню универсальный совет даты и времени в следующий раз   -  person Reky    schedule 21.02.2019

Ответы (1)


arrow_upward
2
arrow_downward

Есть 2 предостережения для этой проблемы:

  • Календарный стол.
  • Способ правильно сгруппировать периоды безработицы, когда между ними есть период занятости.

В следующем решении используется таблица календаря (включая SQL) и трюк DATEDIFF() с привязкой к дате для правильной группировки для 2-й точки.

Заполните скрипт DB здесь.

Решение (пояснение ниже):

;WITH AllPositionDates AS
(
    SELECT
        T.posNo,
        C.GeneratedDate
    FROM
        zz_position AS T
        INNER JOIN Calendar AS C ON C.GeneratedDate BETWEEN T.date_from AND T.date_to
),
AllEmployedDates AS
(
    SELECT
        T.posNo,
        T.resID,
        T.seqNo,
        C.GeneratedDate
    FROM
        zz_employment AS T
        INNER JOIN Calendar AS C ON C.GeneratedDate BETWEEN T.date_from AND T.date_to
),
PositionsByEmployed AS
(
    SELECT
        P.posNo,
        P.GeneratedDate,
        E.resID,
        E.seqNo,
        NullRowNumber = ROW_NUMBER() OVER (
            PARTITION BY
                P.posNo,
                CASE WHEN E.posNo IS NULL THEN 1 ELSE 2 END
            ORDER BY
                P.GeneratedDate ASC)
    FROM
        AllPositionDates AS P
        LEFT JOIN AllEmployedDates AS E ON
            P.posNo = E.posNo AND
            P.GeneratedDate = E.GeneratedDate
)
SELECT
    P.posNo,
    P.resID,
    Date_From = MIN(P.GeneratedDate),
    Date_To = MAX(P.GeneratedDate),
    seqNo = ISNULL(P.seqNo, 0)
FROM
    PositionsByEmployed AS P
GROUP BY
    P.posNo,
    P.resID,
    P.seqNo,
    CASE WHEN P.resId IS NULL THEN P.NullRowNumber - DATEDIFF(DAY, '2000-01-01', P.GeneratedDate) END -- GroupingValueGroupingValue
ORDER BY
    P.posNo,
    Date_From,
    Date_To

Результат:

posNo   resID   Date_From   Date_To     seqNo
10001   NULL    2017-01-01  2017-12-31  0
10001   100     2018-01-01  2018-04-01  1
10001   NULL    2018-04-02  2018-05-31  0
10001   101     2018-06-01  2019-12-31  2
10001   100     2020-01-01  2020-12-31  3

Объяснение

Сначала создание таблицы календаря. Это содержит 1 строку для каждого дня, и в этом примере она ограничена первым и последним возможным днем ​​​​рабочих должностей:

DECLARE @DateStart DATE = (SELECT MIN(P.date_from) FROM zz_position AS P)
DECLARE @DateEnd DATE = (SELECT(MAX(P.date_to)) FROM zz_position AS P)

;WITH GeneratedDates AS
(
    SELECT
        GeneratedDate = @DateStart

    UNION ALL

    SELECT
        GeneratedDate = DATEADD(DAY, 1, G.GeneratedDate)
    FROM
        GeneratedDates AS G
    WHERE
        DATEADD(DAY, 1, G.GeneratedDate) <= @DateEnd
)
SELECT
    DateID = IDENTITY(INT, 1, 1),
    G.GeneratedDate
INTO
    Calendar
FROM
    GeneratedDates AS G
OPTION
    (MAXRECURSION 0)

Это генерирует следующее (до 2020-12-31, что является максимальной датой из выборочных данных):

DateID  GeneratedDate
1       2017-01-01
2       2017-01-02
3       2017-01-03
4       2017-01-04
5       2017-01-05
6       2017-01-06
7       2017-01-07

Теперь мы используем объединение с между, чтобы «распределить» периоды как должностей, так и периодов сотрудников (на разных CTE), поэтому мы получаем 1 строку на каждый день для каждой должности/сотрудника.

-- AllPositionDates
SELECT
    T.posNo,
    C.GeneratedDate
FROM
    zz_position AS T
    INNER JOIN Calendar AS C ON C.GeneratedDate BETWEEN T.date_from AND T.date_to

-- AllEmployedDates
SELECT
    T.posNo,
    T.resID,
    T.seqNo,
    C.GeneratedDate
FROM
    zz_employment AS T
    INNER JOIN Calendar AS C ON C.GeneratedDate BETWEEN T.date_from AND T.date_to

С их помощью мы объединяем их вместе по должности и дате, используя LEFT JOIN, поэтому мы получаем все дни каждой должности и соответствующего сотрудника (если он существует). Мы также вычисляем номер строки для всех значений NULL для каждой позиции, которую будем использовать позже. Обратите внимание, что этот номер строки увеличивается на 1 на 1 с каждой следующей датой соответственно.

;WITH AllPositionDates AS
(
    SELECT
        T.posNo,
        C.GeneratedDate
    FROM
        zz_position AS T
        INNER JOIN Calendar AS C ON C.GeneratedDate BETWEEN T.date_from AND T.date_to
),
AllEmployedDates AS
(
    SELECT
        T.posNo,
        T.resID,
        T.seqNo,
        C.GeneratedDate
    FROM
        zz_employment AS T
        INNER JOIN Calendar AS C ON C.GeneratedDate BETWEEN T.date_from AND T.date_to
)
-- PositionsByEmployee
SELECT
    P.posNo,
    P.GeneratedDate,
    E.resID,
    E.seqNo,
    NullRowNumber = ROW_NUMBER() OVER (
        PARTITION BY
            P.posNo,
            CASE WHEN E.posNo IS NULL THEN 1 ELSE 2 END
        ORDER BY
            P.GeneratedDate ASC)
    FROM
        AllPositionDates AS P
        LEFT JOIN AllEmployedDates AS E ON
            P.posNo = E.posNo AND
            P.GeneratedDate = E.GeneratedDate

Теперь о сложной части. Если мы подсчитаем количество дней разницы между жестко заданной датой и каждым днем, мы получим аналогичный «номер строки», который последовательно увеличивается для каждой даты.

SELECT
    P.posNo,
    P.GeneratedDate,
    DateDiff = DATEDIFF(DAY, '2000-01-01', P.GeneratedDate),
    P.NullRowNumber
FROM
    PositionsByEmployed AS P -- This is declare with the WITH (full solution below)
ORDER BY
    P.posNo,
    P.GeneratedDate

Получаем следующее:

posNo   GeneratedDate   DateDiff    NullRowNumber
10001   2017-01-01      6210        1
10001   2017-01-02      6211        2
10001   2017-01-03      6212        3
10001   2017-01-04      6213        4
10001   2017-01-05      6214        5
10001   2017-01-06      6215        6
10001   2017-01-07      6216        7
10001   2017-01-08      6217        8
10001   2017-01-09      6218        9

Если мы добавим еще один столбец с остальными двумя, вы увидите, что значение остается прежним:

SELECT
    P.posNo,
    P.GeneratedDate,
    DateDiff = DATEDIFF(DAY, '2000-01-01', P.GeneratedDate),
    P.NullRowNumber,
    GroupingValue = P.NullRowNumber - DATEDIFF(DAY, '2000-01-01', P.GeneratedDate)
FROM
    PositionsByEmployed AS P
ORDER BY
    P.posNo,
    P.GeneratedDate

Мы получили:

posNo   GeneratedDate   DateDiff    NullRowNumber   GroupingValue
10001   2017-01-01      6210        1               -6209
10001   2017-01-02      6211        2               -6209
10001   2017-01-03      6212        3               -6209
10001   2017-01-04      6213        4               -6209
10001   2017-01-05      6214        5               -6209
10001   2017-01-06      6215        6               -6209
10001   2017-01-07      6216        7               -6209
10001   2017-01-08      6217        8               -6209
10001   2017-01-09      6218        9               -6209
10001   2017-01-10      6219        10              -6209

Но если мы прокрутим вниз, пока не увидим значения NULL для работника (из выражения ROW_NUMBER() PARTITION BY E.PosNo), мы увидим, что остальное отличается, поскольку ROW_NUMBER() продолжало увеличиваться 1 на 1, а DATEDIFF прыгало, потому что между ними есть работающие люди:

posNo   GeneratedDate   DateDiff    NullRowNumber   GroupingValue
10001   2017-12-28      6571        362             -6209
10001   2017-12-29      6572        363             -6209
10001   2017-12-30      6573        364             -6209
10001   2017-12-31      6574        365             -6209
...
10001   2018-04-02      6666        366             -6300
10001   2018-04-03      6667        367             -6300
10001   2018-04-04      6668        368             -6300
10001   2018-04-05      6669        369             -6300
10001   2018-04-06      6670        370             -6300
10001   2018-04-07      6671        371             -6300

Используйте это "GroupingValue" в качестве дополнительного GROUP BY для правильного разделения интервалов позиций, выходящих за пределы используемых интервалов.

person EzLo    schedule 20.02.2019
comment
Очень нравится ваше решение, одна вещь, которую я забыл указать, это тот факт, что более одного человека могут быть в одном и том же посте одновременно. Я попытался изменить операторы вставки для вставки в значения zz_employment («10001», «100», «2018-01-01 00:00:00.000», «2018-04-01 00:00:00.000», 1), ( '10001', '101', '2018-02-01 00:00:00.000', '2019-12-31 00:00:00.000', 2), ('10001', '100', '2020-01 -01 00:00:00.000', '2099-12-31 00:00:00.000', 3) чтобы вызвать перекрытие и нарушить решение, вы можете внести поправки в код, чтобы согласовать сценарий. - person Reky; 21.02.2019
comment
@Reky, в этом случае единственное, что вам нужно изменить, это выражение GROUP BY, которое будет применяться только тогда, когда сотрудник не является нулевым CASE WHEN P.resId IS NULL THEN P.NullRowNumber - DATEDIFF(DAY, '2000-01-01', P.GeneratedDate) END -- GroupingValue. Я обновил свой ответ. - person EzLo; 21.02.2019
comment
Извините за задержку, вчера у меня была возможность, наконец, протестировать его, и он работает отлично, поэтому спасибо за вашу помощь в этом! - person Reky; 07.03.2019