this is my query:
with Q1 as
(
select *,
LAG(ignition) over (ORDER BY [timestamp]) as PrevStatus,
LEAD(ignition) over (order by [timestamp]) as NextStatus
FROM vehiclehistorytable
WHERE plateno='Crematory' and timestamp between '2021-09-02 00:00:10.00' and '2021-09-02 23:59:59.00'
),
Q2 as
(
select *,
ROW_NUMBER() over (order by timestamp) as rn,
LAG([volume1]) OVER (ORDER BY [timestamp]) AS VolumeIgOff
from Q1
where PrevStatus is null or NextStatus is null or not (ignition = PrevStatus and ignition = NextStatus)
),
Q3 as
(
select a.timestamp, a.ignition, a.volume1
from Q2 as a
inner join Q2 as b on b.rn = a.rn + 1
where a.rn % 2 0
)
select
timestamp AS IgnitionOn,
ignition,
[volume1] AS FuelLevelON
from Q3
order by timestamp
this is my current results:
IgnitionOn ignition FuelLevelON
2021-09-02 00:00:10.00 1 251.35
2021-09-02 00:39:09.00 0 251.35
2021-09-02 00:48:34.00 1 251.35
2021-09-02 03:24:31.00 0 208.1178
2021-09-02 03:37:22.00 1 208.1178
2021-09-02 04:59:37.00 0 181.4747
2021-09-02 05:17:14.00 1 181.4747
2021-09-02 06:20:27.00 0 159.8586
2021-09-02 06:22:29.00 1 159.8586
2021-09-02 07:30:41.00 0 140.2533
2021-09-02 11:29:38.00 1 141.2587
2021-09-02 12:41:52.00 0 117.6318
2021-09-02 12:43:22.00 1 117.6318
2021-09-02 14:07:43.00 0 254.3662
2021-09-02 14:23:49.00 1 254.3662
2021-09-02 15:31:14.00 0 238.2798
2021-09-02 15:41:02.00 1 238.2798
2021-09-02 16:46:55.00 0 218.6745
2021-09-02 16:48:25.00 1 218.6745
2021-09-02 17:55:06.00 0 196.5557
2021-09-02 18:09:03.00 1 196.5557
2021-09-02 19:09:37.00 0 174.9396
2021-09-02 19:30:26.00 1 174.9396
2021-09-02 20:56:53.00 0 152.8208
2021-09-02 21:01:40.00 1 152.8208
2021-09-02 22:08:59.00 0 135.729
this is my expected results:
IgnitionOn IgnitionOff 2021-09-02 00:00:10.00 2021-09-02 00:39:09.00 2021-09-02 00:48:34.00 2021-09-02 03:24:31.00 2021-09-02 03:37:22.00 2021-09-02 04:59:37.00 2021-09-02 05:17:14.00 2021-09-02 06:20:27.00 2021-09-02 06:22:29.00 2021-09-02 07:30:41.00 2021-09-02 11:29:38.00 2021-09-02 12:41:52.00 2021-09-02 12:43:22.00 2021-09-02 14:07:43.00 2021-09-02 14:23:49.00 2021-09-02 15:31:14.00 2021-09-02 15:41:02.00 2021-09-02 16:46:55.00 2021-09-02 16:48:25.00 2021-09-02 17:55:06.00 2021-09-02 18:09:03.00 2021-09-02 19:09:37.00 2021-09-02 19:30:26.00 2021-09-02 20:56:53.00 2021-09-02 21:01:40.00 2021-09-02 22:08:59.00
this is my source data vehiclehistorytable:
timestamp ignition volume1 2021-09-02 22:10:11.00 0 135.729 2021-09-02 22:09:11.00 0 135.729 2021-09-02 22:08:59.00 0 135.729 2021-09-02 21:01:40.00 1 152.8208 2021-09-02 21:01:23.00 0 152.8208 2021-09-02 21:01:12.00 0 152.8208 2021-09-02 21:00:13.00 0 152.8208 2021-09-02 20:59:11.00 0 153.3235 2021-09-02 20:58:15.00 0 153.3235 2021-09-02 20:57:11.00 0 152.8208 2021-09-02 20:56:53.00 0 152.8208 2021-09-02 19:36:13.00 1 174.9396 2021-09-02 19:35:15.00 1 174.9396 2021-09-02 19:34:11.00 1 174.9396 2021-09-02 19:33:10.00 1 174.9396 2021-09-02 19:32:17.00 1 174.9396 2021-09-02 19:31:24.00 1 174.9396 2021-09-02 19:30:42.00 1 174.9396 2021-09-02 19:30:26.00 1 174.9396 2021-09-02 19:16:15.00 0 174.9396 2021-09-02 19:14:12.00 0 174.9396 2021-09-02 19:10:11.00 0 174.9396 2021-09-02 19:09:37.00 0 174.9396 2021-09-02 19:09:13.00 1 174.9396 2021-09-02 18:09:03.00 1 196.5557 2021-09-02 18:01:15.00 0 196.5557 2021-09-02 17:59:11.00 0 196.5557 2021-09-02 17:58:11.00 0 196.5557 2021-09-02 17:57:17.00 0 196.5557 2021-09-02 17:56:12.00 0 196.5557 2021-09-02 17:55:18.00 0 196.5557 2021-09-02 17:55:06.00 0 196.5557 2021-09-02 16:48:25.00 1 218.6745 2021-09-02 16:47:11.00 0 218.6745 2021-09-02 16:46:55.00 0 218.6745 2021-09-02 16:46:11.00 1 218.6745 2021-09-02 15:57:11.00 1 232.2474 2021-09-02 15:41:19.00 1 238.2798 2021-09-02 15:41:02.00 1 238.2798 2021-09-02 15:35:11.00 0 238.2798 2021-09-02 15:31:14.00 0 238.2798 2021-09-02 14:24:05.00 1 254.3662 2021-09-02 14:23:49.00 1 254.3662 2021-09-02 14:07:43.00 0 254.3662 2021-09-02 12:44:10.00 1 117.6318 2021-09-02 12:43:33.00 1 117.6318 2021-09-02 12:43:22.00 1 117.6318 2021-09-02 12:42:10.00 0 117.6318 2021-09-02 12:41:52.00 0 117.6318 2021-09-02 11:29:38.00 1 141.2587 2021-09-02 07:30:41.00 0 140.2533 2021-09-02 06:22:29.00 1 159.8586 2021-09-02 06:22:10.00 0 159.8586 2021-09-02 06:21:17.00 0 159.8586 2021-09-02 06:20:27.00 0 159.8586 2021-09-02 05:17:14.00 1 181.4747 2021-09-02 04:59:37.00 0 181.4747 2021-09-02 03:37:22.00 1 208.1178 2021-09-02 03:24:31.00 0 208.1178 2021-09-02 00:48:34.00 1 251.35 2021-09-02 00:39:09.00 0 251.35 2021-09-02 00:03:09.00 1 251.35 2021-09-02 00:02:10.00 1 251.35 2021-09-02 00:01:11.00 1 251.35 2021-09-02 00:00:10.00 1 251.35
That's a so-called gaps-and-islands problem.
As you only have two states, you can just look for transitions from 0 to 1. Summing that over time will give you a 'partition_id'; each partition beginning with consecutive 1's and ending with consecutive 0's.
Once you have those groups, conditional aggregation will find the first 1 in the group (switch on) and the first 0 in the group (switch off).
WITH
transitions AS
(
SELECT
vehiclehistorytable.*,
CASE WHEN LAG(ignition) OVER (ORDER BY timestamp) = ignition OR ignition = 0 THEN 0 ELSE 1 END AS switch_on
FROM
vehiclehistorytable
),
ignition_phases AS
(
SELECT
transitions.*,
SUM(switch_on) OVER (ORDER BY timestamp) AS partition_id
FROM
transitions
)
SELECT
MIN(CASE WHEN ignition = 1 THEN timestamp END) AS ignition_on,
MIN(CASE WHEN ignition = 0 THEN timestamp END) AS ignition_off
FROM
ignition_phases
GROUP BY
partition_id
ORDER BY
partition_id
If you want to know the volumes at those times, add a check for when switch_off occurs, in the first cte...
WITH
transitions AS
(
SELECT
vehiclehistorytable.*,
CASE WHEN LAG(ignition) OVER (ORDER BY timestamp) = ignition OR ignition = 0 THEN 0 ELSE 1 END AS switch_on,
CASE WHEN LAG(ignition) OVER (ORDER BY timestamp) = ignition OR ignition = 1 THEN 0 ELSE 1 END AS switch_off
FROM
vehiclehistorytable
),
ignition_phases AS
(
SELECT
transitions.*,
SUM(switch_on) OVER (ORDER BY timestamp) AS partition_id
FROM
transitions
)
SELECT
MIN(CASE WHEN switch_on = 1 THEN timestamp END) AS ignition_on,
MIN(CASE WHEN switch_on = 1 THEN volume END) AS ignition_on_volume,
MIN(CASE WHEN switch_off = 1 THEN timestamp END) AS ignition_off,
MIN(CASE WHEN switch_off = 1 THEN volume END) AS ignition_off_volume
FROM
ignition_phases
GROUP BY
partition_id
ORDER BY
partition_id
EDIT: Inverted logic to deal with NULL coming from LAG()
Demo : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=04a5ee6c62671cde5884a392831e0f16