What you are primarily looking for is a SUM
command run on your queries where you will GROUP BY jobId
. That will allow you to get the SUM
of your hours worked as well as the cost of the parts.
I can see two options that you can utilize. Now there may be some variables I am missing but this should give you a framework to work with.
Basically what you need to do is get the SUM
separated out which you should and then the math is fairly easy after that. You are able to get SUM
value by using the GROUP BY
clause in a SELECT
query.
Sub Queries As "Tables"
With this approach you create a sort of "table" which has your SUM
pre calculated which is then added in the main SELECT
. (I am assuming you have some sort of a JOB
table which is where the jobId
column comes from).
SELECT J.JobID,
A.SumOfProductUnitPrice + (B.SumOfHoursWorked * 30)
FROM Job J
LEFT OUTER JOIN
(
SELECT j.jobId,
SUM(p.productUnitPrice) AS SumOfProductUnitPrice --This becomes the name of this "column" in the main SELECT statement
FROM JobRequiredProducts J
JOIN Product p
ON p.productId = j.productId
GROUP BY J.JobID
) A
ON A.JobID = J.JobID
LEFT OUTER JOIN
(
SELECT j.jobId,
SUM(j.hoursOfWork) AS SumOfHoursWorked --This becomes the name of this "column" in the main SELECT statement
FROM JobRequiredLabours J
JOIN Labour L
ON l.labourId = j.labourId
GROUP BY j.jobId
) B
ON B.JobID = J.JobID
WHERE J.JobID = 1 --WHERE Command can change to return the same information for a different jobID
CTE's (Common Table Expression)
An alternative that also works is using a CTE
(a Common Table Expression) which is almost like a VIEW
that exists only for this query. It allows you to pre-define a query and utilize it later in a different command. In this particular instance we are using it to pre-generate the SUM
so we can then use it later in a main SELECT
. The benefit of this is the logic of how the CTE's are created is not in the main SELECT
which helps with readability.
--All CTE's need to be precided with a ";" if it is not the first statement in the batch
;
WITH ProductSum (JobID, SumOfProductUnitPrice)
AS
(
SELECT j.jobId,
SUM(p.productUnitPrice) AS SumOfProductUnitPrice
FROM JobRequiredProducts J
JOIN Product p
ON p.productId = j.productId
GROUP BY J.JobID
), --Comma seaparates the two CTE's that could be used in the next statement
HoursWorkSum (JobID, SumOfHoursWorked)
(
SELECT j.jobId,
SUM(j.hoursOfWork) AS SumOfHoursWorked
FROM JobRequiredLabours J
JOIN Labour L
ON l.labourId = j.labourId
GROUP BY j.jobId
) --No comma follows the last CTE in the chain
SELECT J.JobID,
A.SumOfProductUnitPrice + (B.SumOfHoursWorked * 30)
FROM Job J
LEFT OUTER JOIN ProductSum A
ON A.JobID = J.JobID
LEFT OUTER JOIN HoursWorkSum B
ON B.JobID = J.JobID
WHERE J.JobID = 1 --WHERE command can change to return the same data for a different jobId
These queries will get slightly more complicated, if a job requires more than one instance of a single part AND if that is held through some kind of QUANTITY
COLUMN
or if the Hourly Rate for a given person is not 30 every time. But hopefully this will give you something to work with as a start.