By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE log (time DATETIME, value FLOAT, label_2 VARCHAR(50), id VARCHAR(50)
);
INSERT INTO log (time, value, label_2, id) VALUES
('2023-05-02 12:00', 0.75, 'Rolling', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-02 12:45', 1.75, 'Ct-Primary', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-02 14:30', 9.50, 'Ct-Primary', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-02 15:30', 1.00, 'NU/ND WH/XT', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-02 16:30', 4.00, 'NU/ND', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-02 20:30', 0.50, 'NU/ND', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-02 21:00', 3.00, 'NU/ND', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-03 00:00', 3.00, 'Casing', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-03 03:00', 3.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-03 06:00', 14.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-03 20:00', 4.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-04 00:00', 7.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-04 07:00', 5.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-04 12:00', 8.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-04 20:00', 4.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-05 00:00', 6.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-05 06:00', 7.00, 'Out Track', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-05 10:00', 0.50, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-05 10:30', 13.50, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-06 00:00', 7.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-06 07:00', 0.50, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-06 07:30', 16.50, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-07 00:00', 7.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-07 07:00', 0.50, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-07 07:30', 12.50, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-07 20:00', 4.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-08 00:00', 7.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-08 07:00', 0.50, 'Safety Mtg', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-08 07:30', 13.50, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-08 21:00', 3.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-09 00:00', 5.00, 'Ct-Primary', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-09 06:00', 2.25, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-09 08:15', 0.25, 'Safety Mtg', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-09 08:30', 10.50, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
('2023-05-09 19:00', 5.00, 'EQ Mob', 'C409EA83-A2C6-39F5-A8BC-C9E91BA7A756'),
90 rows affected
select id,time,label_2,
row_number() over (partition by id order by time) as rank
from log
where label_2 in ('ct-primary', 'out track')
| id | time | label_2 | rank |
|---|---|---|---|
| 97B398D7-9BBC-D036-1CC2-C828F0F22243 | 2023-05-02 21:00:00.000 | Ct-Primary | 1 |
| 97B398D7-9BBC-D036-1CC2-C828F0F22243 | 2023-05-09 00:00:00.000 | Out Track | 2 |
| C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-02 12:45:00.000 | Ct-Primary | 1 |
| C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-02 14:30:00.000 | Ct-Primary | 2 |
| C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-05 06:00:00.000 | Out Track | 3 |
| C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-09 00:00:00.000 | Ct-Primary | 4 |
| C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-11 18:00:00.000 | Out Track | 5 |
with labels as (
select id,time,label_2,
row_number() over (partition by id order by time) as rank
from log
where label_2 in ('ct-primary', 'out track')
),
valid_pairs as
(
select l1.id, l1.time as start_time, l2.time as end_time
from labels l1
inner join labels l2 on l1.id = l2.id and l2.rank = l1.rank + 1
where l1.label_2 = 'ct-primary' and l2.label_2 = 'out track'
)
select * from valid_pairs;
| id | start_time | end_time |
|---|---|---|
| 97B398D7-9BBC-D036-1CC2-C828F0F22243 | 2023-05-02 21:00:00.000 | 2023-05-09 00:00:00.000 |
| C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-02 14:30:00.000 | 2023-05-05 06:00:00.000 |
| C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-09 00:00:00.000 | 2023-05-11 18:00:00.000 |
with labels as (
select id,time,label_2,
row_number() over (partition by id order by time) as rank
from log
where label_2 in ('ct-primary', 'out track')
),
valid_pairs as
(
select l1.id, l1.time as start_time, l2.time as end_time
from labels l1
inner join labels l2 on l1.id = l2.id and l2.rank = l1.rank + 1
where l1.label_2 = 'ct-primary' and l2.label_2 = 'out track'
)
select l.id,sum(l.value) as total
from log l
inner join valid_pairs vp
on l.id = vp.id and l.time between vp.start_time and vp.end_time
group by l.id
order by l.id;
| id | total |
|---|---|
| 97B398D7-9BBC-D036-1CC2-C828F0F22243 | 118.5 |
| C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 153 |