How to filter for consecuting timeline events in pure SQL?
You don't need cursor for that.

If your id are automatically incrementing, you also don't need ordering by the timestamp, the id is enough. If not, then use timestamps. Note, timestamps have some possibility of funny effects (timezones, daylight saving times, simultaneous effects inside a single second and many similar "surprises").

This query filters those camera1 events, which are not followed by a camera2 event - either because the car is in the lot, or because they are tricking. The query handles also "funny" cases, for example, if a car would be detected by the camera on the entrance multiple times.

It does not handle the case somebody leaves the lot without entering it, but understanding the logic, it is easy to improve.

WITH Raw AS (
  SELECT RANK() OVER (PARTITION BY Plate ORDER BY Id) AS EventNo,
  Id,
  Camera,
  Plate,
  Timestamp
  FROM CameraLog
)
SELECT Prev.*
FROM CameraLog AS Prev
LEFT JOIN CameraLog AS Next
  ON Prev.Plate = Next.Plate AND Prev.EventNo + 1 = Next.EventNo
WHERE Prev.Camera = 1 AND Next.Camera &lt;&gt; 1;


The logic is that first we extend the table with the "event history" order of a single car by the RANK() windowing function: the Raw subquery essentially extends the CameraLog table with a new, EventNo column. This column is essentially an auto-incrementing id, but it is ordered for all the plates induvidually.

In the second step, in the main query, we join this table with itself together. Essentially, we try to find for all event the next one.

The result is that we have such a table:

columns of camera events | columns of the consecuting camera event


In this column structure, it is already easy to filter with a WHERE (last line).

Note, this query will be highly suboptimal, if the size of the tables is in the order of the available RAM in your server. If you need to handle large data tables quickly, you will need more advanced techniques.