finding periods

joost versteegen

given a table job with columns time_utc (datetime) and state (integer), i want to find the periods (start-time and end_time) where the consequetive value of the state column equals 4. i tried to find the start of the periods with the folllowing query, but no success. too many results. can someone please help me? i cannot figure it out.

SELECT act_start_time_utc, state_cd FROM SRPMES901007.WWMESDB.dbo.job WHERE act_start_time_utc IS NOT NULL ORDER BY act_start_time_utc ASC

DECLARE @PERIOD TABLE (start_time DATETIME, end_time DATETIME)

INSERT INTO @PERIOD(start_time)
SELECT t.start_time/*, t.new_state, t.last_state*/ FROM (
SELECT act_start_time_utc AS start_time, state_cd AS new_state, LAG(state_cd, 1) OVER (ORDER BY act_start_time_utc ASC) AS last_state
FROM SRPMES901007.WWMESDB.dbo.job
WHERE act_start_time_utc IS NOT NULL) AS t
WHERE t.new_state = 4 AND t.last_state <> 4

UPDATE period
SET end_time = (SELECT MIN(act_start_time_utc) endtime FROM SRPMES901007.WWMESDB.dbo.job INNER JOIN @PERIOD period ON job.act_start_time_utc > period.start_time AND state_cd <> 4)
FROM @PERIOD period

SELECT * FROM @PERIOD ORDER BY start_time ASC

joost versteegen

i finally figured it out, here's the result:

DECLARE @PERIOD TABLE (start_time DATETIME, end_time DATETIME)

INSERT INTO @PERIOD(start_time)
SELECT t.start_time FROM (
SELECT act_start_time_utc AS start_time, state_cd AS new_state, LAG(state_cd, 1) OVER (ORDER BY act_start_time_utc ASC) AS last_state
FROM SRPMES901007.WWMESDB.dbo.job
WHERE act_start_time_utc IS NOT NULL) AS t
WHERE t.new_state = 4 AND t.last_state <> 4

UPDATE period
SET end_time = (SELECT TOP(1) act_start_time_utc FROM SRPMES901007.WWMESDB.dbo.job WHERE state_cd <> 4 AND act_start_time_utc > period.start_time ORDER BY act_start_time_utc ASC)
FROM @PERIOD period

SELECT * FROM @PERIOD ORDER BY start_time ASC

But the query takes 19 seconds, can it be speeded up?