Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2013
    Posts
    3

    Unanswered: Trigger or Views to input column value (2 tables)

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    No need to store the miles completed. Just create a view over the table with this calculation in the definition e.g.
    Code:
    CREATE VIEW ...
      AS
    SELECT trip_completion_form.list
         , trip_completion_form.of
         , trip_completion_form.columns
         , trip_completion_form.OdometerStart
         , trip_completion_form.OdometerEnd
         , (trip_completion_form.OdometerEnd - trip_completion_form.OdometerStart) As MilesCompleted
         , vehicles.HireCharge
         , (trip_completion_form.OdometerEnd - trip_completion_form.OdometerStart) * vehicles.HireCharge As MileageCharge
         , ...etc
    FROM   trip_completion_form
     INNER
      JOIN vehicles
        ON vehicles.vehicleID = trip_completion_form.Vehicle_ID
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2013
    Posts
    3
    Awesome Stuff mate.. figure it all out now. Thanks for that, I need to use Joins and Views more often
    Last edited by JJ1717; 12-19-13 at 13:09.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •