Need a query that returns first recod, every time a car cross the speed of 100

Transact-SQL
1

create table dbo.GPSlog
(carId int,
GPSTime datetime,
Speed int
)

insert into dbo.GPSlog values (1,'2015-10-05 13:55:55.940',95)
insert into dbo.GPSlog values (1,'2015-10-05 13:55:57.940',101)--1st 100+ record
insert into dbo.GPSlog values (1,'2015-10-05 13:55:59.940',106)
insert into dbo.GPSlog values (1,'2015-10-05 13:58:55.940',102)
insert into dbo.GPSlog values (1,'2015-10-05 13:59:22.940',95)
insert into dbo.GPSlog values (1,'2015-10-05 13:59:30.940',103)--1st 100+ record
insert into dbo.GPSlog values (1,'2015-10-05 13:59:40.940',99)
insert into dbo.GPSlog values (1,'2015-10-05 13:59:55.940',101)--1st 100+ record
insert into dbo.GPSlog values (2,'2015-10-05 13:54:55.940',94)
insert into dbo.GPSlog values (2,'2015-10-05 13:55:55.940',99)
insert into dbo.GPSlog values (2,'2015-10-05 13:56:10.940',102)--1st 100+ record
insert into dbo.GPSlog values (2,'2015-10-05 13:56:40.940',105)
insert into dbo.GPSlog values (2,'2015-10-05 13:57:55.940',110)

Need a query that returns first recod, every time a car cross the speed of 100 , i.e. for my inserts cases the recors will be the one that are with comments (--1st 100+ record)

Thanks

2 
;

WITH cte
AS (
	SELECT *,
		rn = row_number() OVER (
			PARTITION BY carId ORDER BY GPSTime
			)
	FROM dbo.GPSlog
	)
SELECT c2.*
FROM cte c1
INNER JOIN cte c2
	ON c1.carId = c2.carId
		AND c1.rn = c2.rn - 1
WHERE c1.Speed < 100
	AND c2.Speed >= 100
ORDER BY c2.carId,
	c1.GPSTime
1 Like
3

or use the LAG function:

WITH Prev
AS
(
	SELECT carId, GPSTime, Speed
		,LAG(Speed, 1, 0) OVER (PARTITION BY carId ORDER BY GPSTime) AS PrevSpeed
	FROM dbo.GPSLog
)
SELECT carId, GPSTime, Speed
FROM Prev
WHERE Speed >= 100
	AND PrevSpeed < 100;
1 Like
4

Thanks 'Khtan' and 'IFor' , both of yours solutions worked for me, now i need a little change i.e. get only records when car keep speed over 100 for more then 10 seconds and return Start GPSTime and End GPSTime when over speed start and end as well.

5 
; WITH Prev
AS
(
    SELECT   carId, 
             GPSTime, Speed
            ,LAG(GPSTime, 1, 0) OVER (PARTITION BY carId ORDER BY GPSTime) AS PrevGPSTime
            ,LAG(Speed, 1, 0)   OVER (PARTITION BY carId ORDER BY GPSTime) AS PrevSpeed
    FROM     dbo.GPSlog
)
SELECT   carId, PrevGPSTime, PrevSpeed, GPSTime, Speed
FROM     Prev
WHERE    Speed         >= 100
AND      PrevSpeed     >= 100
AND      DATEDIFF(MINUTE, PrevGPSTime, GPSTime) <= 1
AND      DATEDIFF(SECOND, PrevGPSTime, GPSTime) >= 10;
1 Like