I am writing a query to get first changed timestamp by record ID and the time it remained in the same status. So if the table is

Output:

Basically, I need to track when a record gets put on hold and the first instance that it comes off hold.

I have been able to get the first two columns easily, but have not been able to get the third column onwards.

CodePudding user response：

assuming you are using sql server you can try this query

select p.id,b.[time],p.[status] as [change value]
from (
select*
from your_table_name
where [status] not in ('Hold')) as p
join (
select id,[status],min([time]) as [time]
from your_table_name
where [status] = 'Hold'
group by id,[status]) as b
on p.id = b.id

but this query can not get your value with id F cause by your example data, id f dont have status = hold

hope this can help you

CodePudding user response：

Schema (MySQL v5.7)

CREATE TABLE t (
  `id` VARCHAR(1),
  `Status` VARCHAR(9),
  `Time` DATETIME
);

INSERT INTO t
  (`id`, `Status`, `Time`)
VALUES
  ('A', 'Hold', '2022-04-12 00:00:00'),
  ('B', 'Available', '2022-04-13 00:00:00'),
  ('C', 'Hold', '2022-04-14 00:00:00'),
  ('D', 'Hold', '2022-04-13 00:00:00'),
  ('A', 'Hold', '2022-04-12 06:00:00'),
  ('C', 'QA', '2022-04-14 08:00:00'),
  ('D', 'Available', '2022-04-13 02:00:00'),
  ('E', 'Available', '2022-04-15 00:00:00'),
  ('A', 'Available', '2022-04-16 00:00:00'),
  ('F', 'Available', '2022-04-17 00:00:00');

Query

SELECT id
    , MAX(Hold_Time) AS Hold_Time
    , MIN(CASE WHEN Time > Hold_Time THEN Time END) AS Change_Time
    , Status
FROM t
JOIN (
  SELECT id
       , MIN(CASE WHEN Status = 'Hold' THEN Time END) AS Hold_Time
  FROM t
  GROUP BY id
) t2 USING(id)
WHERE Status <> 'Hold' and Hold_Time IS NOT NULL
GROUP BY id, Status;

View on DB Fiddle