Rental Car Utilization time difference between end trip and begin trip for same VIN

Question

So I work for a smaller rental car company (free floating) and I am trying to create heat maps. So I have a fleet of 150 cars and they are able to be parked anywhere in our defined "home zone" which is essentially the main city. Members can park on any street legal space and it is my duty to get vehicles into areas of our home zone that will increase the likelihood of it being rented. I would like to understand utilization rates by calculating how quickly a rental ends and how quickly that vehicle is re-rented. The lower the amount of time the higher the utilization rate which will help me create heat maps. I need to not only know the difference between times but they must be for the same VIN to make sense. There is only 1 table that I am working with on this particular question. See the table info below. Please let me know if you need any additional information. I really appreciate any help you all may provide.

Trips Table:
VIN,
Trip start date/time,
Trip End date/time,
Start Lat,
Start Long,
End Lat,
End Long

Answer 1

Try this:
DECLARE @trips TABLE (VIN VARCHAR(100),TripStartDateTime DATETIME2(7), TripEndDateTime DATETIME2(7))

INSERT INTO @trips
VALUES
('1','2021-01-01 13:00:00','2021-01-01 18:00'),
('1','2021-01-01 20:00:00','2021-01-03 11:00'),
('1','2021-01-03 20:00:00','2021-01-12 18:00')


SELECT *
    , DATEDIFF(MINUTE,TripEndDateTime, Next_TripStartDateTime) as IdleTime_Minutes
FROM (
    SELECT VIN
        ,TripStartDateTime
        ,TripEndDateTime
        ,LEAD(TripStartDateTime,1,NULL)  OVER (PARTITION BY VIN ORDER BY TripStartDateTime) as Next_TripStartDateTime
    FROM @trips
)a
ORDER BY VIN, TripStartDateTime

Answer 2

Hi @Joey ,

Welcome to the microsoft TSQL Q&A forum!

As Erland said, we need you to provide the tables and test data involved in the problem, as well as the output you expect.

My simple guess:

    SELECT VIN,DATEDIFF(mi,[Trip start date/time],[Trip End date/time]) diff  
    FROM Trips  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.