0

I have a table:

table 1

carId (INT)
Check Out date (DATE)
Checek In date (DATE)

EXPLANATION: This table has a data for a rental car which shows a particular car is checked out on a particular date and checked in on a particular date

I want to find how many cars are on rent on a particular date

OUTPUT

date (DATE)
NO_Of_Cars

Is there a way to solve this problem in python/SQL?

EDIT : I may have posted this question wrongly. In output, I want date and no of cars for all the dates of a year. Apologies

Vatsal Gandhi
  • 21
  • 3

2 Answers2

1
select count(distinct carId) NO_Of_Cars
from table1 
where checkOut <= particular_date 
    and (particular_date < checkIn or checkIn is null)

Note that check out and check in are points of custody change. Hence checkOut <= means from and including the moment of check out and < checkIn means up to but not including the moment of check in. If the check in moment was inclusive <= checkIn then the car could, in principle, have 2 custodians for that exact checkIn moment (maybe the customer changed cars half way through the rental period).

Preacher
  • 2,127
  • 1
  • 11
  • 25
0

Use this code in SQL

     SELECT date, NO_Of_Cars FROM table1
     WHERE
         particular_date <=check_out and 
         (particular_date >= check_in OR check_in IS NULL);

The date comparison logic is needed over here.

You may refer Date comparison in SQL

Optider
  • 550
  • 4
  • 11
  • A car is checked out, then driven around, then checked in. It is not possible for any one particular date to be both before the car was checked out `<=check_out` and after it was checked in `>= check_in`. – Preacher Mar 12 '20 at 15:02