[thelist] [SQL] SELECT Question

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

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?

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.

	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