Find Median using SQL Server 2000

I have Max() working as in following query and want to find Mean & Median.....

select 'Max' as Func, Max(b.Proc_Cnt) Procedures
from (
select a.Stu_Code, sum(a.proc_cnt) Proc_Cnt
from (
select s.stu_code, t.chart_no, t.last_date, count(*) proc_cnt
from Treatment  t, Student s, Patient p
where s.stu_code = t.provider_id_credited
and p.chart_no = t.chart_no
and t.last_date between '6/30/2009' and '7/1/2010'
and s.clinic_location like 'GRP%'
 and t.njds_code <> '00000'
 and (t.njds_code like 'D%' or t.njds_code like '0%')
and t.njds_code NOT BETWEEN '90000' and '90100'
and (t.chart_no Not Like 'M%' and t.chart_no Not Like 'F%' and t.chart_no <> '292021')
and (t.Activity Not in ('CP','CS','NS') OR t.Activity is Null)
and ((p.group_assignment IS NULL) OR
     (p.group_assignment <> 'MTHN' AND p.group_assignment <> 'NRTF'
  AND p.group_assignment <> 'SOM' AND p.group_assignment <> 'GWY'))
group by s.stu_code, t.chart_no, t.last_date) a
group by a.stu_code
) b

How do I convert above to get Median?

Thanks,
Ashok
LVL 13
AshokAsked:
Who is Participating?
 
rajeevnandanmishraCommented:
And Median can be found by using below query:
select 'Median' as Func,
(SELECT top 1 Proc_Cnt FROM
(select top 50 percent Proc_Cnt
from (
select a.Stu_Code, sum(a.proc_cnt) Proc_Cnt
from (
select s.stu_code, t.chart_no, t.last_date, count(*) proc_cnt
from Treatment  t, Student s, Patient p
where s.stu_code = t.provider_id_credited
and p.chart_no = t.chart_no
and t.last_date between '6/30/2009' and '7/1/2010'
and s.clinic_location like 'GRP%'
 and t.njds_code <> '00000'
 and (t.njds_code like 'D%' or t.njds_code like '0%')
and t.njds_code NOT BETWEEN '90000' and '90100'
and (t.chart_no Not Like 'M%' and t.chart_no Not Like 'F%' and t.chart_no <> '292021')
and (t.Activity Not in ('CP','CS','NS') OR t.Activity is Null)
and ((p.group_assignment IS NULL) OR
     (p.group_assignment <> 'MTHN' AND p.group_assignment <> 'NRTF'
  AND p.group_assignment <> 'SOM' AND p.group_assignment <> 'GWY'))
group by s.stu_code, t.chart_no, t.last_date) a
group by a.stu_code
) b ORDER BY Proc_Cnt ) c ORDER BY 1 DESC )
+
(SELECT top 1 Proc_Cnt FROM
(select top 50 percent Proc_Cnt
from (
select a.Stu_Code, sum(a.proc_cnt) Proc_Cnt
from (
select s.stu_code, t.chart_no, t.last_date, count(*) proc_cnt
from Treatment  t, Student s, Patient p
where s.stu_code = t.provider_id_credited
and p.chart_no = t.chart_no
and t.last_date between '6/30/2009' and '7/1/2010'
and s.clinic_location like 'GRP%'
 and t.njds_code <> '00000'
 and (t.njds_code like 'D%' or t.njds_code like '0%')
and t.njds_code NOT BETWEEN '90000' and '90100'
and (t.chart_no Not Like 'M%' and t.chart_no Not Like 'F%' and t.chart_no <> '292021')
and (t.Activity Not in ('CP','CS','NS') OR t.Activity is Null)
and ((p.group_assignment IS NULL) OR
     (p.group_assignment <> 'MTHN' AND p.group_assignment <> 'NRTF'
  AND p.group_assignment <> 'SOM' AND p.group_assignment <> 'GWY'))
group by s.stu_code, t.chart_no, t.last_date) a
group by a.stu_code
) b ORDER BY Proc_Cnt DESC) c ORDER BY 1 )/2


Though it could be better if you put your inner processed result in a temp table and then run the query on that temp table.
0
 
rajeevnandanmishraCommented:
Hi,
MEAN can be calculated by using AVG function like:
select 'Max' as Func, Max(b.Proc_Cnt) Procedures
from (
select a.Stu_Code, sum(a.proc_cnt) Proc_Cnt
from (
select s.stu_code, t.chart_no, t.last_date, count(*) proc_cnt
from Treatment  t, Student s, Patient p
where s.stu_code = t.provider_id_credited
and p.chart_no = t.chart_no
and t.last_date between '6/30/2009' and '7/1/2010'
and s.clinic_location like 'GRP%'
 and t.njds_code <> '00000'
 and (t.njds_code like 'D%' or t.njds_code like '0%')
and t.njds_code NOT BETWEEN '90000' and '90100'
and (t.chart_no Not Like 'M%' and t.chart_no Not Like 'F%' and t.chart_no <> '292021')
and (t.Activity Not in ('CP','CS','NS') OR t.Activity is Null)
and ((p.group_assignment IS NULL) OR
     (p.group_assignment <> 'MTHN' AND p.group_assignment <> 'NRTF'
  AND p.group_assignment <> 'SOM' AND p.group_assignment <> 'GWY'))
group by s.stu_code, t.chart_no, t.last_date) a
group by a.stu_code
) b
0
 
AshokAuthor Commented:
MEAN can be calculated by using AVG function like:

I do not see the use of AVG function in above.

I will try the median when I go to work tomorrow morning.

Thanks,
Ashok
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AshokAuthor Commented:
Do I just change SUM to

select 'Avg' as Func, Avg(b.Proc_Cnt) Procedures

in my Max() query?

Ashok
0
 
rajeevnandanmishraCommented:
My mistake. Yes,just change the MAX to AVG in your query and that will provide you the MEAN.
0
 
AshokAuthor Commented:
Thanks a lot
0
 
AshokAuthor Commented:
by the way, this did not work for my data.
Median was out of range (not between Min & Max).

Ashok
0
 
rajeevnandanmishraCommented:
Hi,
Logically it should work. But could be some mistake by me. Can you put the result of below two queries:
SELECT top 1 Proc_Cnt FROM 
(select top 50 percent Proc_Cnt 
from (
select a.Stu_Code, sum(a.proc_cnt) Proc_Cnt
from (
select s.stu_code, t.chart_no, t.last_date, count(*) proc_cnt
from Treatment  t, Student s, Patient p
where s.stu_code = t.provider_id_credited
and p.chart_no = t.chart_no 
and t.last_date between '6/30/2009' and '7/1/2010'
and s.clinic_location like 'GRP%'
 and t.njds_code <> '00000'
 and (t.njds_code like 'D%' or t.njds_code like '0%')
and t.njds_code NOT BETWEEN '90000' and '90100'
and (t.chart_no Not Like 'M%' and t.chart_no Not Like 'F%' and t.chart_no <> '292021')
and (t.Activity Not in ('CP','CS','NS') OR t.Activity is Null)
and ((p.group_assignment IS NULL) OR 
     (p.group_assignment <> 'MTHN' AND p.group_assignment <> 'NRTF' 
  AND p.group_assignment <> 'SOM' AND p.group_assignment <> 'GWY'))
group by s.stu_code, t.chart_no, t.last_date) a
group by a.stu_code
) b ORDER BY Proc_Cnt ) c ORDER BY 1 DESC 

SELECT top 1 Proc_Cnt FROM 
(select top 50 percent Proc_Cnt 
from (
select a.Stu_Code, sum(a.proc_cnt) Proc_Cnt
from (
select s.stu_code, t.chart_no, t.last_date, count(*) proc_cnt
from Treatment  t, Student s, Patient p
where s.stu_code = t.provider_id_credited
and p.chart_no = t.chart_no 
and t.last_date between '6/30/2009' and '7/1/2010'
and s.clinic_location like 'GRP%'
 and t.njds_code <> '00000'
 and (t.njds_code like 'D%' or t.njds_code like '0%')
and t.njds_code NOT BETWEEN '90000' and '90100'
and (t.chart_no Not Like 'M%' and t.chart_no Not Like 'F%' and t.chart_no <> '292021')
and (t.Activity Not in ('CP','CS','NS') OR t.Activity is Null)
and ((p.group_assignment IS NULL) OR 
     (p.group_assignment <> 'MTHN' AND p.group_assignment <> 'NRTF' 
  AND p.group_assignment <> 'SOM' AND p.group_assignment <> 'GWY'))
group by s.stu_code, t.chart_no, t.last_date) a
group by a.stu_code
) b ORDER BY Proc_Cnt DESC) c ORDER BY 1

Open in new window

0
 
AshokAuthor Commented:
Proc_Cnt
321

(1 row(s) affected)

Proc_Cnt
322

(1 row(s) affected)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.