Database management system (DBMS): MySQL
Description of requirements/functionality: For each vehicle we move, this is called a 'job'.
A job can have additional costs associated with it (Fuel, car wash, oil etc.).
We are changing a process within our database for these additional charges.
Currently all charges for a 'job' are held in one row in a database table (sample attached). Our software handles the multiple costs, adds together by category and updates the single table row.
The change will mean that we will have a new database table with one row per cost. So if a driver adds fuel to a car twice, and washes it once, there will be three costs, and three rows in the new table.
There may also be NO costs associated with a move.
We need to create a view that will allow us to use both of these tables for charges, but want to combine them in one efficient view.
Why ? To allow for all the legacy transactions and incorporate the new transactions moving forward.
There will ALWAYS be a single entry in the old table (jobcosts).
There may be none, or multiple entries in the new table (jobcosts_extras)
We don't want the view to include a sub-query for speed-purposes.
What do I want the view to do ? Combine the fields for 'extras' from the old table (jobcosts), and sum the value of the new costs from the new table (jobcosts_extras) into one number - 'Extras'.
We can get the desired result using SQL (Attached), but struggle to convert this to a view.
Jobnumber is the common field between tables.
I've attached a sample SQL query that returns the results I want, but I'm struggling to convert / integrate this into a view.
Specific technologies required: MySQL
Extra notes: The view should be set around just the two tables (jobcosts, and jobcosts_extras). The third table in the example is just how we would reference the view to get the data.
Nick C.100% (8)
Create an account now and send a proposal now to get this job.Sign up
Clarification Board Ask a Question
I would be tempted to bite the bullet now and change the legacy data into the new format also. Then the problem is deal with forever. Also the amount of columns you have to specify specifically on the old data (most of which appears unused) is not good.
By views do you mean a view in the MVC structure, if yes what framework/language you are using?