Find Jobs
Hire Freelancers

MySQL queries

$30-250 USD

Completed
Posted about 6 years ago

$30-250 USD

Paid on delivery
In this project you will create four MySQL queries. The schema is below, I have attached an EER, and a database dump including sample data will be provided on project acceptance. We have a database that stores records of earnings and expenses for various machines. For earnings, half of the machines show their earnings as a single amount, e.g. $1,250.25. The other half show their earnings as two values, CoinsLeft and CoinsRight. If CoinsLeft is 1205 and CoinsRight is 962 the dollar amounts would be $301.25 and $240.50, (1205 / 4) and (962 / 4) for a total of $541.75. Half of the time, amounts recorded are the cumulative total amount earned over the lifetime of the machine. The other half of the time, the amount is just the amount shown, and in these cases the BOOL field ResetEarnings is TRUE. Queries needed for this project: 1) For a specified date range, show the sum of TotalEarnings OR the total of (CoinsLeft and CoinsRight) for each idMachine. Group results by Location. If a given idMachine's CountsCoins is TRUE, calculate the total of (CoinsLeft + CoinsRight) / 4 ), otherwise calculate TotalEarnings. In all cases, you must subtract the last entry's amount from the current entry, EXCEPT the first entry which is not counted in the sum. However, if a given TotalEarnings row has a TRUE ResetEarnings field, do not subtract the prior entry's earnings and instead add the full amount of TotalEarnings or (CoinsLeft + CoinsRight / 4) for that entry. Example 1: idMachine, date, TotalEarnings, ResetEarnings 1, 1/1/2018, 100, 0 1, 1/7/2018, 150, 0 1, 1/14/2018, 250, 0 1, 1/21/2018, 20, 1 1, 1/28/2018, 120, 0 For date range 1/1/2018 - 1/7/2018 the total for idMachine 1 would be: (150 - 100) = $50 For date range 1/1/2018 - 1/14/2018 the total for idMachine 1 would be: (150 - 100) + (250 - 150) = $150 For date range 1/1/2018 - 1/21/2018 the total for idMachine 1 would be: (150 - 100) + (250 - 150) + (20 - 0) = $170 For date range 1/1/2018 - 1/28/2018 the total for idMachine 1 would be: (150 - 100) + (250 - 150) + (20 - 0) + (120 - 20) = $270 Example 2: idMachine, date, ResetEarnings, CountsCoins, CoinsLeft, CoinsRight 2, 1/1/2018, 0, 1, 4, 8 2, 1/7/2018, 0, 1, 16, 21 2, 1/14/2018, 1, 1, 8, 16 2, 1/21/2018, 0, 1, 10, 20 For date range 1/1/2018 - 1/7/2018 the total for idMachine 2 would be: (16 - 4) + (21 - 8) / 4 = $6.25 For date range 1/1/2018 - 1/14/2018 the total for idMachine 2 would be: (6.25) + ((8 + 16) / 4 )) = $12.25 For date range 1/1/2018 - 1/21/2018 the total for idMachine 2 would be: ($12.25) + (10 - 8) + ((20 - 16) / 4 ) = $13.75 2) For a specific date range, show the sum of Expenses per idMachine, grouped by Location. 3) For a specific date range, show the /net/ (e.g. the result of query 1 minus the result of query 2) per idMachine, grouped by location, WITH a net sum of the machines in each location. 4) For a specific date range, show the number of plays per idMachine, grouped by location. Similar to TotalEarnings, you must subtract the number of plays from the previous entry, and total plays can also be reset when the ResetPlays field is TRUE. Schema: MACHINE Machine ID Machine name, text Date purchased Cost of purchase, integer CountsCoins, bool Serial number, text LOCATION idLocation Name EXPENSE idExpense Expense, text Date TotalExpense, float [login to view URL] [login to view URL] TOTALPLAYS idTotalplays Totalplays, integer Date [login to view URL] [login to view URL] TOTALEARNINGS idTotalearnings ResetEarnings, bool Coins left, integer Coins right, integer TotalEarnings, float Date [login to view URL] [login to view URL]
Project ID: 16263004

About the project

37 proposals
Remote project
Active 6 yrs ago

Looking to make some money?

Benefits of bidding on Freelancer

Set your budget and timeframe
Get paid for your work
Outline your proposal
It's free to sign up and bid on jobs
Awarded to:
User Avatar
I am an experience database adminstration developer. I read your situation and I can help you in this. Please message me for further discussion. I will charge the minimum as possible,
$30 USD in 3 days
5.0 (6 reviews)
4.3
4.3
37 freelancers are bidding on average $143 USD for this job
User Avatar
Hi, I have read your job description. I can definitely do it. I can give you fully responsive website design. I have done many projects using Codeigniter, php, wordpress etc. I can give you the exact what you want.
$100 USD in 5 days
5.0 (58 reviews)
6.2
6.2
User Avatar
Hi , I can start immediately to write mySQL queries as per the requirement. I am a highly motivated professional expert in programming and design. I will go with you until the queries are 100% accurate and also you can count on me after that for upgrades or specific requirements. Ill be more than happy to assist you in this journey. I do have few questions to start with the project so please drop me a note and lets start working. Sneha
$200 USD in 2 days
5.0 (55 reviews)
6.3
6.3
User Avatar
Hi there! Allow me to complete this task. Please ping me so we can discuss more and I can start off my work right away :)
$150 USD in 3 days
5.0 (15 reviews)
5.1
5.1
User Avatar
Hi there, I have read the details and checked attached screenshot of UML. I have strong expertise in plsql, pivot tables, dynamic queries, normalization, umls, triggers, procedures and more. Have a look at my profile too for similar projects and reviews. This is an easy job for me. Max 2 days are enough for me. Message me,
$260 USD in 2 days
5.0 (29 reviews)
5.4
5.4
User Avatar
Hello Sir, hope you are fine, I am interested in your work "MySQL queries". I am ready to write quires from given ERD. i need MYSQL recording for testing the queries in live database ?
$150 USD in 3 days
4.9 (20 reviews)
5.1
5.1
User Avatar
I am mysql dba having more then 10 yrs of exp............i am expert in performance tunning,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
$111 USD in 3 days
4.6 (64 reviews)
5.4
5.4
User Avatar
I have read through your project and I am incredibly interested in taking on your project. I am an experienced developer with a diploma in the field of web development.. I have been working in the field of web development for roughly 7 years. I take great pride in my work and deliver only the best quality. My skill-set includes... -PHP -HTML5 -CSS -AJAX -Responsive Design -Joomla -Wordpress -Magento -Drupal -Photoshop -Illustrator -Video Editing -Data entry -Iphone -Android I look forward to hearing from you and hopefully we will have the opportunity to work together. Thank you, Coasif
$155 USD in 3 days
3.6 (24 reviews)
5.3
5.3
User Avatar
Hi, A SQL Server, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS) and SQL Server Analysis Services (SSAS) expert here. Please feel free to discuss the project with me so that I can wrap it up in a timely manner as per your requirements. I would like to have a detailed document of your requirements so that we can discuss the each and every point in details. Thanks, Murtaza
$250 USD in 5 days
5.0 (2 reviews)
3.9
3.9
User Avatar
Hello, I am interested in your job, it will be done in few hours. Further, once I will complete the queries I will get a feedback from you and incase of any issue with outcome I will resolve the matter. Let's chat to discuss in detail.
$111 USD in 1 day
5.0 (3 reviews)
3.5
3.5
User Avatar
Good afternoon, my name is Franco, i am web developer whit strong knowledge of sql language, so, we would be happy to collaborate with you so that your project be done in the best way. We are available for any questions, Have a great day, Regards, Franco
$100 USD in 1 day
5.0 (1 review)
2.6
2.6
User Avatar
Greetings!!! I can start immediately I'm a professional Web developer with more than 6 Years experience in php, laravel, magento, wordpress and all types of designing work. I have very good Knowledge and clear understanding of online store websites.
$100 USD in 4 days
4.1 (10 reviews)
3.4
3.4
User Avatar
We are highly experienced Post Graduate Engineers, Doctors, IT, ITES, Software, Infrastructure Professionals, Web Designers and IT Architects. We can redo, improve, and publish any engineering, scientific MATLAB, SIMULINK, PSPICE, NS2, NS3 based IEEE papers. We can give remote support in Wintel, ADS, Unix, Storage, Backup, Network, Security tracks. Have experience in Active Directory, DNS, DHCP, FTP, IIS, SharePoint, CRM, SQL, WDS, ADRMS,ADFS,ADLDS, SCCM, Citrix Xenserver, Citrix XenApp, CRM,CRM Outlook connector IFD, Linux, Apache, My SQL, Postfix, VM Ware, Vcenter, VCO, VOIP (3CX)WHM, cPanel, H-Sphere, Hosting Controller, Plesk, DirectAdmin based web hosting support. We work in PHP, JAVA, ASP, DOT NET, Prestashop, Magento, woo commerce, OSCommerce, WordPress, Joomla, Web Hosting Panels Programming, LAMP, Android java development, Backend Server APIs, Android, Java, C/C++, Linux, PHP, MySQL, AJAX, JavaScript, C#, Visual Basic, PHP, MS SQL, My SQL, PHOTOSHOP, CSS, Bootstrap, HTML, JQUERY, JAVA, SCRIPT. We specialize in business, technical, academic, commercial, fiction, and Blog Writing, Dissertations, Academic papers, eBooks, Fiction, Sci-Fi thriller, Technical content, Articles, Web content, and blogs
$30 USD in 3 days
4.4 (4 reviews)
2.6
2.6
User Avatar
Hi my name is Eber Reta, a software developer specializing in desktop applications, I use the familiar languages: C # in the VIsual Studio environment, Java in Intellij Idea and Netbeans. As a database manager I have 2 years of experience in MySql and SqlServer. I read your project, I am very interested in doing it for you. I think I have what you need. Give me the opportunity to make it possible.
$77 USD in 2 days
5.0 (1 review)
0.2
0.2
User Avatar
hi I am a computer science graduate and done many projects of mySQL i can do this for you in your desired budget and time looking forward to your reply
$111 USD in 3 days
0.0 (0 reviews)
0.0
0.0
User Avatar
Knocking on your Project because i read through the job details extremely carefully and I am absolutely sure that I can do the project very well but need to clear few points. So can you please send me a message when you have time to discuss about the project. Here is the complete portfolio showing past projects please have a look https://www.freelancer.com/u/Aliasim85
$166 USD in 3 days
0.0 (0 reviews)
0.0
0.0
User Avatar
Graduated as an information systems engineer (UTN) Developer: Backend: NodeJS, ExpressJS, C #, Java Database: MongoDB, T-SQL, PL-SQL, MySQL, MariaDB, MSSQL Frontend: JQuery, AngularJS, React Functional analyst: Documentation, UML, Task scheduling, system design, requirements analysis
$150 USD in 2 days
0.0 (0 reviews)
0.0
0.0
User Avatar
I am an engineer I have 7 years experience in web and mobile development and management of the database
$155 USD in 3 days
0.0 (0 reviews)
0.0
0.0
User Avatar
A proposal has not yet been provided
$100 USD in 3 days
0.0 (0 reviews)
0.0
0.0
User Avatar
I have over 7 years experience as a VBA programmer and analyst using the Microsoft Office environment. Relevant Skills and Experience VBA, MySQL, Database admin, MS Office
$144 USD in 3 days
0.0 (0 reviews)
0.0
0.0
User Avatar
Hi, I do not have any review yet, I just started as a freelance. I am a data analyst and I was working for Airbus. SQL request are not a problem for me, I was doing a lot of them to extract data and create analysis. if you have any question, let me know !
$100 USD in 2 days
0.0 (0 reviews)
0.0
0.0

About the client

Flag of UNITED STATES
Portland, United States
5.0
13
Payment method verified
Member since Dec 23, 2010

Client Verification

Thanks! We’ve emailed you a link to claim your free credit.
Something went wrong while sending your email. Please try again.
Registered Users Total Jobs Posted
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Loading preview
Permission granted for Geolocation.
Your login session has expired and you have been logged out. Please log in again.