Jump to content

Recommended Posts

Hello.

Can somebody please help me? I have made a site for a helpdesk. It has tables which are, job, labour, customer and material. I have wrote an SQL statement which is looking at the job table and gathering the labour used and the material used. It is adding anadditional cost onto the material, as well as a VAT content of 17.5% on the material and the labour. The calculation works fine if there is only 1 entry for the labour and 1 entry for the material. But, if I have 2 entries for the labour and 1 entry for the material, it looks at the labour and doubles the entries for the materials.

 

Here is my SQL script.

 

select job.Job_No,
job.Order_Date,
job.File_No,
job.Order_Site_Address,
job.Job_Description,
job.CustomerRef,
job.Invoice_Tax_Date,
customer.Customer_Name,
customer.Billing_Address,
labour.Working_Hrs * Paying_Rate,
labour.Working_Hrs * Paying_Rate * 0.175,
(material.Quantity * Unit_Price * Additional_Cost / 100 + Quantity * Unit_Price)*0.175,
material.Quantity * Unit_Price + (Quantity * Unit_Price * Additional_Cost / 100),
(labour.Working_Hrs * Paying_Rate + material.Quantity * Unit_Price + Quantity * Unit_Price * Additional_Cost / 100) * 0.175,
labour.Working_Hrs * Paying_Rate + material.Quantity * Unit_Price + (Quantity * Unit_Price * Additional_Cost / 100),
(labour.Working_Hrs * Paying_Rate + material.Quantity * Unit_Price + Quantity * Unit_Price * Additional_Cost / 100) * 0.175 + labour.Working_Hrs * Paying_Rate + material.Quantity * Unit_Price + (Quantity * Unit_Price * Additional_Cost / 100) 
from job left join labour
on (job.Job_No=labour.Job_No)
left join material
on (job.Job_No=material.Job_No)
left join customer
on (job.Customer_Name=customer.Customer_Name)

 

I have made it using PHPRunner. I have upped it to a demo account at http://demo.asprunner.net/Account/Login.aspx

 

The email is pggrimes@hotmail.com and the password is 1234

 

Thanks for any help

 

Paul.

Link to comment
https://forums.phpfreaks.com/topic/83705-returning-too-many-fields/
Share on other sites

If we send 1 engineer to a job, they might be there for an hour and use an item, but he might need a hand to fit it, so another engineer would assist resulting in engineer 'A' booking 2 hours, engineer 'B' booking 1 hour, with engineer 'A' booking materials. They might have one item or fifty items, they all get put into the database under a Job_No which is set to auto increment on the next new job.

 

I have nearly got my project finished now, just needs the pages to be setup according to our desires, but, I am getting this problem.

That's the nature of joins

[pre]

labour                      material                          joined result

 

Job    |  Cost              Job    |  MatCost                Job    |  Cost  |  MatCost

-------+---------            -------+---------                  -------+---------+---------

A      |  50                A      |  20                      A      |  50    |  20     

                            A      |  30                      A      |  50    |  30

                           

[/pre]

                           

You can reduce the labour and material costs to a single row each using subqueries to pre-calculate the job totals prior to joining with the other tables 

example

SELECT job.Job_no, job.Order_Date, m.totcost, l.totcost
FROM job
LEFT JOIN (SELECT job_no, SUM(cost) as totcost FROM labour GROUP BY job_no) as l ON job.job_no=l.job_no
LEFT JOIN (SELECT job_no, SUM(matcost) as totcost FROM material GROUP BY job_no) as m ON job.job_no=m.job_no

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.