CODEWITHSUNDEEP
mysqldatabasedatesubquerysql-function
user11070990
user11070990

Reputation:

Create a User-Defined Function to check if a car is available between two dates?

I have a table called Bookings were a number of car reservations are stored with columns CarNumber, StartDate, EndDate. I need to create a User-Defined Function to check whether a car is available or not, if the car is available it should return a 1 and if not return a 0.

The function so far:

CREATE FUNCTION Available(StarDate DATE, EndDate DATE, CarNumber INT) RETURNS INTEGER NOT DETERMINISTIC RETURN

I only just started to learn about user defined functions so could use some help.

CREATE TABLE Bookings (CarNumber INT, StartDate DATE, EndDate DATE);

INSERT INTO Bookings (CarNumber, StartDate, EndDate) VALUES (6, '2018-01-02', '2018-01-15'), (1, '2018-01-03', '2018-01-05'), (3, '2018-01-03', '2018-01-04'), (8, '2018-01-04', '2018-01-30'), (10, '2018-01-10', '2018-01-13');

Upvotes: 0

Views: 226

Answers (1)

GMB
GMB

Reputation: 222622

You could use an exists subquery to check if the given date range overlaps any booking date range for the given car.

Consider the following syntax:

delimiter //

create function availability(
    pStartDate datetime, 
    pEndDate datetime, 
    pCarNumber int
)
returns bool deterministic
begin
    declare res bool;
    select not exists(
        select 1 
        from bookings 
        where startDate < pEndDate and endDate > pStartDate and carNumber = pCarNumber
    ) into res;
    return res;
end;
//

Upvotes: 1

Related Questions