Link to home
Start Free TrialLog in
Avatar of melegant99
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,IsNull(q1t.tot,0) as '1st Quarter' FROM eusers
LEFT OUTER JOIN
(SELECT sum(tsc) as 'tot',proposal.salesid,quotas.quotayear
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.
Avatar of HainKurt
HainKurt
Flag of Canada image

you have to add year to inner query somehow ;)

add this to the end of your query

and quarters.quotayear = eusers.quotayear
ASKER CERTIFIED SOLUTION
Avatar of tigin44
tigin44
Flag of Türkiye image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of melegant99
melegant99

ASKER

Sweet.