CODEWITHSUNDEEP
sql
Virat
Virat

Reputation: 107

SQL : Select the nearest datetime row along with specific column value

I have the following table

       Date                    Vehicle_ID          Status     
01/01/2020 5:00 PM                X500              Ready
01/01/2020 6:00 PM                X500              Ready
01/01/2020 7:15 PM                X500              Ready
01/01/2020 8:00 PM                X500              Service
01/01/2020 5:00 PM                X500              Ready
01/01/2020 4:00 PM                X670              Ready
01/01/2020 4.30 PM                X670              Ready
01/01/2020 8:00 PM                X670              Ready
01/01/2020 9:30 PM                X670              Service
01/01/2020 5:00 PM                X670              Ready

I need to subset the rows that had service and the sent record where it was ready immediately after the service.

The output I want is

       Date                    Vehicle_ID          Status  
01/01/2020 7:15 PM                X500              Ready
01/01/2020 8:00 PM                X500              Service
01/01/2020 8:00 PM                X670              Ready
01/01/2020 9:30 PM                X670              Service

I am struggling to write the condition to filter those records. Please help me

Upvotes: 2

Views: 35

Answers (2)

dnoeth
dnoeth

Reputation: 60462

Based on your result you want those rows where the status = 'Service' and the row before:

with cte as 
 ( 
   select t.*
      -- next row's status
      ,lead(status)
       over (partition by vehicle_id
             order by date) as next_status
   from tab as t
 )
select date, vehicle_id, status
from cte
where status = 'Service'      -- rows that had service
   or next_status = 'Service' -- row before service

Btw, when you write ready immediately after the service I would expect to get the following row, not the previous. You might switch to LAG instead of LEAD fort this.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

One method uses lead()/lag():

select date, vehicle_id, status
from (select t.*,
             lag(status) over (partition by vehicle_id order by date) as prev_status,
             lead(status) over (partition by vehicle_id order by date) as next_status
     from t
    ) t
where (status = 'Ready' and next_status = 'Service') or
      (prev_status = 'Ready' and status = 'Service')

Upvotes: 2

Related Questions