[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