melegant99
asked on
Help With SQL Query
So what I am trying to do is get the total sales from each quarter of a year for each user
I have a quarters table, that has the following for columns:
q1,q2,q3,q4,quotayear
The rest of the following query should be easy to follow...now it works GREAT, except that the 1Q Total is showing the same for each year. In the below example, the value is correct for 2010, but should be 0 for 2009.
SELECT quotas.quotayear,lname,IsN ull(q1t.to t,0) as '1st Quarter' FROM eusers
LEFT OUTER JOIN
(SELECT sum(tsc) as 'tot',proposal.salesid,quo tas.quotay ear
FROM priceinfo
INNER JOIN proposal ON proposal.sanum = priceinfo.sanum
INNER JOIN quotas on quotas.salesid = proposal.salesid
INNER JOIN quarters ON quarters.quotayear = quotas.quotayear where dajobfin between quarters.q1 AND quarters.q2
group by quotas.quotayear,proposal. salesid)
q1t ON q1t.salesid = eusers.userid
INNER JOIN quotas ON quotas.salesid = eusers.userid
Results something like:
quotayear lname 1Q Total
2009 Shepard 1051.34
2010 Shepard 1051.34
2009 Austin 0.00
2010 Austin 0.00
Thanks.
I have a quarters table, that has the following for columns:
q1,q2,q3,q4,quotayear
The rest of the following query should be easy to follow...now it works GREAT, except that the 1Q Total is showing the same for each year. In the below example, the value is correct for 2010, but should be 0 for 2009.
SELECT quotas.quotayear,lname,IsN
LEFT OUTER JOIN
(SELECT sum(tsc) as 'tot',proposal.salesid,quo
FROM priceinfo
INNER JOIN proposal ON proposal.sanum = priceinfo.sanum
INNER JOIN quotas on quotas.salesid = proposal.salesid
INNER JOIN quarters ON quarters.quotayear = quotas.quotayear where dajobfin between quarters.q1 AND quarters.q2
group by quotas.quotayear,proposal.
q1t ON q1t.salesid = eusers.userid
INNER JOIN quotas ON quotas.salesid = eusers.userid
Results something like:
quotayear lname 1Q Total
2009 Shepard 1051.34
2010 Shepard 1051.34
2009 Austin 0.00
2010 Austin 0.00
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sweet.
add this to the end of your query
and quarters.quotayear = eusers.quotayear