[thelist] [SQL] SELECT Question

Jacques Capesius Jacques.Capesius at compellent.com
Fri May 27 10:16:28 CDT 2005


<snip>
Jacques Capesius asked:


>>I am building a rather cumbersome SQL Query that has two sub-queries
in it, 


Hi Jacques,


Can you share the revelant portions of the table structures and maybe a 
few lines of example data and the result you would like to see?
</snip>

Hi Luther,

Thanks for your input. It would take me the better part of the day
trying to explain where all the data I'm pulling together is coming
from, as several tables, a view, and two databases are involved in the
query. But to summarize a lot, the SQL Query looks like this.

@start_date = start of date range for which I wish to view information 
@end_date = end of date range for which I whish to view information
??? = part I don't yet know how to do the way I want.

SELECT 
	acct.name as 'Sales Rep',
	(
		SELECT sum(totalvalue) 
		FROM view_sales 
		WHERE salesAcct_id = acct.accountid
		AND actualCloseDate between @start_date and @end_date
	) as 'Sales',
	(
		SELECT sum(sales_quota)
		FROM table_sales_quota
		WHERE acctid = acct.accountid
		AND start_date < @end_date
		AND end_date > @start_date
	) as 'quota',
	??? as 'Percent to Quota'
FROM table_account acct
INNER JOIN (a whole buncha crap, don't ask)

The desired results would look like this.

Sales Rep   |  Sales   | Quota    | Percent To Quota
---------------------------------------------------
John Smith  | 500.00   | 1000.00  | .5
Bob Jones   | 1000.00  | 5000.00  | .2
Ed Williams | 90.00    | 100.00   | .9

I first approached this query using GROUP Bys, but as you mentioned, all
the stuff I was needing to pull together was making the SQL very ugly,
and was producing unreliable results, so I feel kinda stuck doing things
this way. Now, an obvious way to do this and get the results I need to
the percent to quota field would be to cut/paste the two subqueries, so
the ??? would become [subquery 1] / [subquery 2] but that doubles the
amount of subqueries in the SQL statement, which would slow this query
down a great feel, I fear. Hence, my dilemma.



More information about the thelist mailing list