Help with MySQL View
- or -
Post a project like this2563
£25(approx. $31)
- Posted:
- Proposals: 10
- Remote
- #1521767
- PRE-FUNDED
- Awarded
Software Development , Database Development, Web Development and Business Intelligence
Doha
** Top 5% ** - .NET | NodeJS | PHP | Shopify | NopCommerce | QA (Manual & Automation) | SEO & Marketing
Buncrana
Freelancer (WordPress specialists (PHP, HTML, CSS, JS/jQuery)/Mobile Apps/Graphic Designer/)
Larkana
637341047216819742229621033789105640611896971268725150157754832
Description
Experience Level: Intermediate
General information for the business: We are a vehicle delivery company, with a lot of dependence on our MySQL Database
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.
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% (9)Projects Completed
12
Freelancers worked with
13
Projects awarded
65%
Last project
10 Aug 2020
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification 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.
Regards
Mark -
Hi Nick,
By views do you mean a view in the MVC structure, if yes what framework/language you are using?
Vikas
379036378957
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies