why won't this run - SQL Script

[cpuphantom]

I'm trying to put together a single stored procedure that I can pass a number to. It will then return me the percentile that number was compared to a group, as well as the max, min, median, 75th percentile, and 25th percentile numbers from the group.

I put together a nifty little script that works nice to give me just the percentile. It works, and it looks like this (it also takes care of the max and min totals from the group:

select 
	CAST(sum(case when sum_metric <= 75000 THEN 1 ELSE 0 END) as float)/count(*) as PthPercentile,
	MIN(sum_Metric) as MinMetric,
	MAX(sum_Metric) as MaxMetric
from 
	(select 
		sum(tbNumbers.billings) as sum_metric 
	from 
		tbNumbers,
		tbMembers	
	where 
		tbNumbers.user_id = tbMembers.id and 
		tbNumbers.datestamp between '1/1/2003' and '12/31/2003' and 
		tbMembers.status in ('A', 'P', 'B', 'H')
	group by 
		tbNumbers.user_id
	having
		sum(tbNumbers.billings) > 0
	) source

So then I went looking to find out how to also do the 25th, 50th, and 75th percentiles.

My guess is that "Select TOP 25 PERCENT" is my best bet... this is what I can up with:

select 
	CAST(sum(case when sum_metric <= 75000 THEN 1 ELSE 0 END) as float)/count(*) as PthPercentile,
	MIN(sum_Metric) as MinMetric,
	MAX(sum_Metric) as MaxMetric,
	MIN(top25_metric) as MedianMetric1,
	MIN(top50_metric) as MedianMetric2,
	MIN(top75_metric) as MedianMetric3
	
from 
	(select 
		sum(tbNumbers.billings) as sum_metric				
	from 
		tbNumbers,
		tbMembers	
	where 
		tbNumbers.user_id = tbMembers.id and 
		tbNumbers.datestamp between '1/1/2003' and '12/31/2003' and 
		tbMembers.status in ('A', 'P', 'B', 'H')
	group by 
		tbNumbers.user_id
	having
		sum(tbNumbers.billings) > 0
	) allmetric,

	(select 
		top 25 percent sum(tbNumbers.billings) as top25_metric				
	from 
		tbNumbers,
		tbMembers	
	where 
		tbNumbers.user_id = tbMembers.id and 
		tbNumbers.datestamp between '1/1/2003' and '12/31/2003' and 
		tbMembers.status in ('A', 'P', 'B', 'H')
	group by 
		tbNumbers.user_id
	having
		sum(tbNumbers.billings) > 0
	order by
		sum(tbNumbers.billings) desc
	) toptwentyfive,

	(select 
		top 50 percent sum(tbNumbers.billings) as top50_metric				
	from 
		tbNumbers,
		tbMembers	
	where 
		tbNumbers.user_id = tbMembers.id and 
		tbNumbers.datestamp between '1/1/2003' and '12/31/2003' and 
		tbMembers.status in ('A', 'P', 'B', 'H')
	group by 
		tbNumbers.user_id
	having
		sum(tbNumbers.billings) > 0
	order by
		sum(tbNumbers.billings) desc
	) topfifty,

	(select 
		top 75 percent sum(tbNumbers.billings) as top75_metric				
	from 
		tbNumbers,
		tbMembers	
	where 
		tbNumbers.user_id = tbMembers.id and 
		tbNumbers.datestamp between '1/1/2003' and '12/31/2003' and 
		tbMembers.status in ('A', 'P', 'B', 'H')
	group by 
		tbNumbers.user_id
	having
		sum(tbNumbers.billings) > 0
	order by
		sum(tbNumbers.billings) desc
	) topseventyfive

In theory it should work... and it does work, just fine, when I only do allmetrics and one of the others... but when I try and do the whole thing, it just executes and executes. Forever.

Any help, critisism... anything would be great!
Thanks.

 

[maswien]

The problem is that the cross join on all the subqueries will take long time to run, so the query is phased wrong and need to be rewritten.