September 28, 2007 at 8:10 am
4 tables:
Offices
Users
Jobs
Estimates
Right now I'm using the query below, which doesn't use the job table. A single job can have many estimates. The problem is, I need to adjust things so that I'm only pulling the most recent estimate per job.
SELECT
O.OfficeAbbr,
U.Name,
AVG(E.FinalAmount) AS Average,
COUNT(E.EstimateID) AS Number,
SUM(E.FinalAmount) AS Total
FROM
Estimates E, Users U, Offices O
WHERE
E.CreateDate > '08/31/2007' AND E.CreateDate < '10/01/2007'
AND
E.OfficeID IN (
'ID1', ID2', 'ID3'
)
AND
E.EstimatorID = U.UserID
AND
E.OfficeID = O.OfficeID
GROUP BY U.Name, O.OfficeAbbr
I've been banging my head on the desk for an hour, but can't seem to figure out how to get it done.
Thanks in advance for any help!
September 28, 2007 at 8:37 am
Is there a date column on the job table that tells you which is the most recent?
Also how does the job table link back to the other tables?
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 28, 2007 at 8:41 am
I've tried this for now.
I'm not sure 100% about your joins or columns but I'm sure you'll get the jist of it:
SELECT
O.OfficeAbbr,
U.Name,
AVG(E.FinalAmount) AS Average,
COUNT(E.EstimateID) AS Number,
SUM(E.FinalAmount) AS Total
FROM Estimates E
INNER JOIN Users U
ON E.EstimatorID = U.UserID
INNER JOIN Offices O
ON E.OfficeID = O.OfficeID
INNER JOIN (SELECT MAX(DATE) as DATE, EstimatorID FROM jobs GROUP BY EstimatorID) J
ON J.EstimatorID = E.EstimatorID
WHERE
E.CreateDate > '08/31/2007' AND E.CreateDate < '10/01/2007'
AND
E.OfficeID IN ('ID1', 'ID2', 'ID3')
GROUP BY U.Name, O.OfficeAbbr
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 28, 2007 at 9:33 am
Awesome, thanks!
The join from Job to Estimates is on Job.JobID = Estimate.JobID instead of the userID, but I think this slight change makes my day.
SELECT
O.OfficeAbbr,
U.Name,
AVG(E.FinalAmount) AS Average,
COUNT(E.EstimateID) AS Number,
SUM(E.FinalAmount) AS Total
FROM Estimates E
INNER JOIN Users U
ON E.EstimatorID = U.UserID
INNER JOIN Offices O
ON E.OfficeID = O.OfficeID
INNER JOIN (SELECT MAX(CreateDate) as DATE, JobID FROM jobs GROUP BY JobID) J
ON J.JobID = E.JobID
WHERE
E.CreateDate > '08/31/2007' AND E.CreateDate < '10/01/2007'
AND
E.OfficeID IN ('ID1', 'ID2', 'ETC')
GROUP BY U.Name, O.OfficeAbbr
It was that select in the join I couldn't figure out, and I thank you for the help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply