sql server - How to get the date of last service status?

Question

I am taksed with doing an analysis of our machines and their service. I have a table where each machine has its operation status with a time stamp.

One machine can move to different lines, but that is not relevant, since I am interested in the machine's durability regardless of where it is placed.

Basically, I want to code the blue column:

Here is some sample data:

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)

I have experimented a bit with ROW_NUMBER() and it seems that maybe I can do it with a bit of fiddling, but a colleague mentioned that those functions are detremental to performance and I should stay away from them.

The amount of data is in the range of 1m to 2m rows in the table.

Your help is much appreciated. Thanks!

question from:https://stackoverflow.com/questions/65640895/how-to-get-the-date-of-last-service-status

Answer 1

Sub query is one option to get required result

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