Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Get GEOMETRIC MEANS SQL

Posted on 2011-02-17
11
881 Views
Last Modified: 2012-05-11
Similar to GEOMEAN in excel, I am try ing to get the Mean using SQL2000 and SSRS.  Could somebody help me with this?
0
Comment
Question by:epicazo
  • 6
  • 5
11 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 34920686
this will calculate the geometric mean in sql server 2000
declare @scores table (score int)
insert into @scores values (5);  
insert into @scores values (7);  
insert into @scores values (55);  
insert into @scores values (6);  
insert into @scores values (3);  

declare @total	float;
declare @val	float;
declare @cnt	int;
declare @res	float;
set @total = 0.0;

declare cscores cursor for
select score from @scores

open cscores
fetch next from cscores into @val;
while @@FETCH_STATUS = 0
begin
	select @total = @total + LOG(@val);
	fetch next from cscores into @val;
end

select @cnt = COUNT(*) from @scores;
select @res = @total / (@cnt *1.0)
select @cnt, @res, @total


select EXP(@res)

Open in new window

0
 

Author Comment

by:epicazo
ID: 34921117
Sorry, Im so new to this... .So would I create a function?  using LOSD field below, how could I Calculate Means?

This is the Length of Stay in Days, that I need to calculate Mean?
LOSD
16
21
3
8
783
1
4
4
3
13
=avg(Fields!LOSD.Value) gives me 85.60, but I need Mean?

The output will be in SSRS
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34921235
A function like this should help you
create function dbo.fnGetMedian ()
returns float
as 
begin
	declare @total	float;
	declare @val	float;
	declare @cnt	int;
	declare @res	float;
	set @total = 0.0;

	declare cscores cursor for
	select losd from yourTable

	open cscores
	fetch next from cscores into @val;
	while @@FETCH_STATUS = 0
	begin
		select @total = @total + LOG(@val);
		fetch next from cscores into @val;
	end

	select @cnt = COUNT(*) from @scores;
	select @res = @total / (@cnt *1.0)

	select @val = EXP(@res);
	
	return @val;
end

Open in new window

0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 26

Expert Comment

by:tigin44
ID: 34921266
I missed to replace a point change line 22 as

      select @cnt = COUNT(*) from yourTable;
0
 

Author Comment

by:epicazo
ID: 34921504
Iam sorry, I am still lost.   Here is some actual data from my HPPATMFL table...  
My report is grouped by unit...

INTENSIVE CARE UNIT  
RoomBd      Pt Name      Station      cc Admit DT      P5EFDT      LOSD
0412 A      XXX      ICU      2/1/2011      20110216      16
0412 B      XX      ICU      1/27/2011      20110216      21
0412 C      X      ICU      2/14/2011      20110216      3
0412 D      XXX      ICU      2/9/2011      20110216      8
0412 E      XX      ICU      12/26/2008      20110216      783
0412 F      X      ICU      2/16/2011      20110216      1
0412 G      XXXXXX      ICU      2/13/2011      20110216      4
0412 H      XXXXX      ICU      2/13/2011      20110216      4
0412 I      DOE, JANE      ICU      2/14/2011      20110216      3
0412 J      DOE, JOHN      ICU      2/4/2011      20110216      13
                              85.60 AVERAGE
                                                                                                           9.11 GEOMEAN

LABOR & DELIVERY                                                                                                
RoomBd      Pt Name      Station      cc Admit DT      P5EFDT      LOSD
            
0312 A      DOE, JANE      L&D      2/16/2011      20110216      1
                              1.00 AVERAGE
                                                                                                         1.00 GEOMEAN
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34921583
so modify the function as follows and use it like this

select AVG(losd) as avarage,  dbo.fnGetMedian() as geomean
from [INTENSIVE CARE UNIT]
create function dbo.fnGetMedian ()
returns float
as 
begin
	declare @total	float;
	declare @val	float;
	declare @cnt	int;
	declare @res	float;
	set @total = 0.0;

	declare cscores cursor for
	select losd from [INTENSIVE CARE UNIT];

	open cscores
	fetch next from cscores into @val;
	while @@FETCH_STATUS = 0
	begin
		select @total = @total + LOG(@val);
		fetch next from cscores into @val;
	end

	select @cnt = COUNT(*) from [INTENSIVE CARE UNIT];
	select @res = @total / (@cnt *1.0)

	select @val = EXP(@res);
	
	return @val;
end

Open in new window

0
 

Author Comment

by:epicazo
ID: 34921971
It's looking for object [INTENSIVE CARE UNIT] and I error out....
0
 

Author Comment

by:epicazo
ID: 34922063
is there a way to create a more universal function?   without having to , but more universal so when I get the mean it would be something like:   dbo.fnGetMedian(LOSD)     just a thought.
0
 
LVL 26

Accepted Solution

by:
tigin44 earned 250 total points
ID: 34924163
sure you can make the funtion to be more general by providing table name and column name as input and using dynamic sql sytax or you can change funtion to an sp for using dynamic sql.
0
 

Author Comment

by:epicazo
ID: 34956085
Hello,  Could you show me an example?    I haven't worked on dynamic sql much.   Thanks!
0
 

Author Closing Comment

by:epicazo
ID: 35101382
I give you the points for trying.

My solution:
SELECT Exp(Sum(Log(sample))/Count(*)) AS GM
FROM sample_table;
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question