Блог

Я занят анализом наших машин и их обслуживания. У меня есть таблица, в которой каждая машина имеет свой статус работы с отметкой времени.

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

В принципе, я хочу закодировать синий столбец:

Вот некоторые примеры данных:

 DROP TABLE IF EXISTS #machineStatus
CREATE TABLE #machineStatus
(
    machineID VARCHAR(255),
    machineLine VARCHAR(255),
    machineStatus VARCHAR(255),
    periodStart DATETIME,
    periodEnd DATETIME
)

INSERT INTO #machineStatus
(
    machineId,
    machineLine,
    machineStatus,
    periodStart,
    periodEnd
)
VALUES
('01255783', '0251', 'Active', '2016-01-01 00:00:00', '2016-01-31 23:59:59'),
('01255783', '0251', 'Active', '2016-02-05 00:00:00', '2016-02-07 23:59:59'),
('01255999', '415', 'Active', '2016-01-01 00:00:00', '2016-01-31 23:59:59'),
('01255999', '415', 'Active', '2016-02-01 00:00:00', '2016-02-29 23:59:59'),
('01255999', '415', 'Maintenance', '2016-03-01 00:00:00', '2016-03-31 23:59:59'),
('01255783', '0251', 'Service', '2016-02-08 00:00:00', '2016-02-12 23:59:59'),
('01255783', '0251', 'Active', '2016-02-13 00:00:00', '2016-03-31 23:59:59'),
('01255783', '0251', 'Active', '2016-04-01 00:00:00', '2016-04-15 23:59:59'),
('01255783', '0251', 'Terminated', '2016-04-20 00:00:00', NULL),
('01255999', '415', 'Active', '2016-04-01 00:00:00', '2016-04-15 23:59:59'),
('01255999', '415', 'Active', '2019-01-01 00:00:00', '2020-02-26 23:59:59'),
('01255999', '415', 'Service', '2020-02-27 00:00:00', '2020-03-01 00:00:00'),
('01255999', '415', 'Active', '2020-03-01 00:00:00', '2020-03-01 23:59:59'),
('01255999', '415', 'Active', '2020-03-02 00:00:00', '2020-07-29 23:59:59'),
('01255999', '415', 'Service', '2020-07-30 00:00:00', '2020-07-31 23:59:59'),
('01255999', '415', 'Service', '2020-07-31 00:00:00', '2020-08-10 23:59:59'),
('01255999', '415', 'Active', '2020-08-11 00:00:00', '2020-08-30 23:59:59'),
('01255999', '415', 'Service', '2020-09-01 00:00:00', '2020-09-07 23:59:59'),
('01255999', '415', 'Service', '2020-09-08 00:00:00', '2020-10-10 23:59:59'),
('01255999', '416', 'Active', '2020-10-11 00:00:00', '2020-10-28 23:59:59'),
('01255999', '01000', 'Terminated', '2020-10-29 23:59:59', NULL)
 

Я немного поэкспериментировал с ROW_NUMBER(), и кажется, что, возможно, я смогу сделать это, немного повозившись, но коллега упомянул, что эти функции снижают производительность, и я должен держаться от них подальше.

Объем данных находится в диапазоне от 1 до 2 строк в таблице.

Ваша помощь очень ценится. Спасибо!

Вложенный запрос — это один из вариантов получения требуемого результата

 SELECT s.*
, (
    SELECT MAX(ss.PeriodStart) 
    FROM machineStatus ss 
    WHERE ss.machineId = s.machineId 
        AND ss.machineStatus = 'Service' AND ss.PeriodStart < s.PeriodStart
)
FROM machineStatus s