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

Joey 1 Reputation point
2021-08-20T17:25:45.967+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,851 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

2 answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-08-23T06:39:26.877+00:00

    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.

    0 comments No comments

  2. Tom Phillips 17,716 Reputation points
    2021-08-23T21:45:56.417+00:00

    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
    
    0 comments No comments