Hello there,

I am working on a project for the booking of vehicles. I have a an issue with figuring out the 'amount' of a particular booking because I need to calculate the sum via a 'hire charge' that is related to a vehicle ID.

To make that sound more simple I will supply the two tables that are stated above.

Code:
CREATE TABLE `trip_completion_form` (
  `DepartmentName` varchar(45) NOT NULL,
  `Form_ID` int(11) NOT NULL AUTO_INCREMENT,
  `BookingNO` int(11) NOT NULL,
  `Vehicle_ID` int(11) NOT NULL,
  `FuelPurchased` decimal(5,3) DEFAULT NULL,
  `CreditCardNo` int(11) DEFAULT NULL,
  `OdometerStart` decimal(8,1) NOT NULL,
  `OdometerEnd` decimal(8,1) NOT NULL,
  `MilesCompleted` decimal(5,1) NOT NULL,
  `MaintenanceComplaint` varchar(45) DEFAULT NULL,
  `Amount` decimal(8,2) NOT NULL,
  PRIMARY KEY (`Form_ID`,`BookingNO`,`Vehicle_ID`),
  KEY `BookingNO_idx` (`BookingNO`),
  KEY `DepartmentName_idx` (`DepartmentName`),
  CONSTRAINT `BookingNOFK` FOREIGN KEY (`BookingNO`) REFERENCES `booking` (`BookingNO`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `DepartmentNameFK3` FOREIGN KEY (`DepartmentName`) REFERENCES `booking` (`DepartmentName`) ON DELETE CASCADE ON UPDATE CASCADE
)
This table has the 'amount' column that needs to be figured out like this.

Odometer end - odometer start = Value

Miles completed = Value

That value then needs to be multiplied by the hirecharge of the vehicles ID it relates to. For example if the vehicleID of this trip completion form is 3, then 'hirecharge' of the vehicleID 3 of the vehicles table needs to be used.

The vehicle table;
Code:
CREATE TABLE `vehicles` (
  `vehicleID` int(11) NOT NULL AUTO_INCREMENT,
  `Type` varchar(45) NOT NULL,
  `Make` varchar(45) NOT NULL,
  `RegNO` varchar(45) NOT NULL,
  `Damaged` tinyint(4) NOT NULL,
  `HireCharge` decimal(4,2) NOT NULL,
  PRIMARY KEY (`vehicleID`)
)
I am assuming a basic trigger will solve the odometer to the miles completed part. I am just lost on how to the final part.

I can appreciate my schema is probably not the best designed for this kind of thing but it's too late for my project to go back and change everything. If anyone can explain to me how I can fix this issue that would be great. I have just started working with trigger's and views so knowing the restrictions of the syntax is what I am currently studying.