MySQL Forums
Forum List  »  Stored Procedures

Need help with a stored procedure comparing dates in two tables
Posted by: Jim Newman
Date: February 11, 2013 01:05PM

Hi, and thanks in advance for any assistance.

I'm working with a company that specialist in hiring plant machinery. When hiring plant they check whether it already exists in a booking or whether it has been scheduled for maintenance. The booking and maintenance dates are stored in separate tables.

I need to create a stored procedure that will allow the user to specify a series of dates and check the availability of the machinery in these two tables, ideally returning a percentage of the time available (as they can see which machine might be available for the greatest period of time and make arrangements with their clients).

I am completely stumped and don't know how to compare a range of dates like this, particularly against two sets of fields. The database is currently a bit like the tables below.

I'd appreciate any help you can give me.

CREATE TABLE `main`.`plant` (
`plant_id` INT NOT NULL AUTO_INCREMENT ,
`planttype` VARCHAR(45) NOT NULL ,
`dailycost` DECIMAL(7,2) NOT NULL ,
PRIMARY KEY (`plant_id`) )

CREATE TABLE `main`.`bookings` (
`booking_id` INT NOT NULL AUTO_INCREMENT ,
`bookingforclient` INT NOT NULL ,
`plant_id` INT NOT NULL ,
PRIMARY KEY (`booking_id`) );


CREATE TABLE `main`.`bookingdates` (
`booking_id` INT NOT NULL ,
`bookingdate` DATETIME NOT NULL ,
`datepart` INT NOT NULL );

CREATE TABLE `main`.`maintenance` (
`plant_id` INT NOT NULL ,
`maintenancedate` DATETIME NOT NULL ,
`datepart` INT NOT NULL );

Options: ReplyQuote


Subject
Views
Written By
Posted
Need help with a stored procedure comparing dates in two tables
3431
February 11, 2013 01:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.