根据SQL中另一列的状态减去行

假设你不能在"监控"按日期顺序连续两次声明，这是许多解决方案中的一个。如果票证在数据中的最终状态仍然是"监控";此解决方案将假设您还想减去到现在为止的所有分钟数。

-- demo schema and data
create table tickets(id int, state varchar(16), changed_at datetime);
insert tickets 
values (1, 'state 1',      dateadd(minute, -7,  getdate())), 
(1, 'monitoring',   dateadd(minute, -6,  getdate())), 
(1, 'state 1',      dateadd(minute, -5,  getdate())),
(1, 'state 2',      dateadd(minute, -4,  getdate())),
(1, 'monitoring',   dateadd(minute, -3,  getdate())),
(1, 'state 3',      dateadd(minute, -1,  getdate())),
(2, 'state 1',      dateadd(minute, -10, getdate())),
(2, 'monitoring',   dateadd(minute, -5,  getdate())); -- ticket 2's final state is still "monitoring"
select      id,
duration_monitoring = sum(datediff(minute, t.changed_at, v.changed_at))
from        tickets  t
cross apply (
select   top 1 changed_at
from     tickets u
where    u.id = t.id
and      u.state != 'monitoring'
and      u.changed_at > t.changed_at
order by changed_at asc
) v
where       state = 'monitoring'
group by    id;
-- if you actually store to the second and want to round up...
select      id,
duration_monitoring = ceiling(sum(datediff(second, t.changed_at, v.changed_at)) / 60.0)
from        tickets  t
cross apply (
select   top 1 changed_at
from     tickets u
where    u.id = t.id
and      u.state != 'monitoring'
and      u.changed_at > t.changed_at
order by changed_at asc
) v
where       state = 'monitoring'
group by    id;