Solved

# Help With SQL Query

Posted on 2010-01-05
167 Views
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.
0
Question by:melegant99

LVL 51

Expert Comment

you have to add year to inner query somehow ;)

and quarters.quotayear = eusers.quotayear
0

LVL 26

Accepted Solution

try this
``````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 and quotas.quotayear = q1t.quotayear
INNER JOIN quotas ON quotas.salesid = eusers.userid
``````
0

Author Closing Comment

Sweet.
0

## Featured Post

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …