question

Joey-8456 avatar image
0 Votes"
Joey-8456 asked EchoLiu-msft commented

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

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-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The standard recommendation for this kind of question is that you post
1. CREATE TABLE statements for your tables.
2. INSERT statements with test data.
3. The expected result from your test data.
4. A short description that explains why you want that particular result.
5. The output of "SELECT @@version".





0 Votes 0 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @Joey-8456,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.