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
3
Medium Priority
?
175 Views
Last Modified: 2012-05-08
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
Comment
Question by:melegant99
3 Comments
 
LVL 61

Expert Comment

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

add this to the end of your query

and quarters.quotayear = eusers.quotayear
0
 
LVL 26

Accepted Solution

by:
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

Open in new window

0
 

Author Closing Comment

by:melegant99
ID: 31673187
Sweet.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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…
Video by: Steve
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

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question