Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Help With SQL Query

Posted on 2010-01-05
Medium Priority
175 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 61

Expert Comment

ID: 26184962
you have to add year to inner query somehow ;)

and quarters.quotayear = eusers.quotayear
0

LVL 26

Accepted Solution

tigin44 earned 2000 total points
ID: 26184977
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

ID: 31673187
Sweet.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
###### Suggested Courses
Course of the Month15 days, 5 hours left to enroll