select highest and lowest figures in a group for %ge growth

Question

Post reply

select highest and lowest figures in a group for %ge growth

Nuts

SSCrazy

Points: 2547
More actions
January 21, 2010 at 8:59 pm

#209354

Hi Guys
Here is my sample data...
GPName Year Qtr Total_Doses
XYZ 2008 1 20
XYZ 2008 2 300
.
.
XYZ 2009 4 500
BNG 2007 2 440
BNG 2007 3 1000
BNG 2009 4 50
.
.
.
I want to calculate the percentage growth of total_doses for each doctor
For ex the %ge growth for XYZ is 96% (((500 - 20)/500)*100
There are about 300,000 rows in my table.
Could you pls help guys??
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

From your sample data, we can do it by min,max class

create table #T( GPName varchar(10),Year int,Qtr int,Total_Doses int)

insert into #T

select 'XYZ',2008,1,20 union all

select 'XYZ',2008,2,300 union all

select 'XYZ',2009,4,500 union all

select 'BNG',2007,2,440 union all

select 'BNG',2007,3,1000 union all

select 'BNG',2009,4,50

select GPName,((max(Total_Doses)-min(Total_Doses))/cast(max(Total_Doses)as numeric(8,0)))*100

from #T

group by GPName

Nuts SSCrazy Points: 2547 More actions · Answer 2

Hi

Thanks for ur query.

I apologise I did not make it very clear the first time...

I dont want to calculatethe percentage by highest and lowest values...

I want to do it by latest date(year / qtr) and start date(year / qtr)

So it should be like this

for Dr BNG, it would be (((50 - 440)/50)*100

Hope it helps

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

PLEASE, read the article at the first link in my signature. It will help you help others give YOU a correct answer more quickly. Thanks.

Unless your datatypes are different (might still work if they are), this should probably do.

create table #T( GPName varchar(10),Year int,Qtr int,Total_Doses int);
insert into #T
select 'XYZ',2008,1,20 union all
select 'XYZ',2008,2,300 union all
select 'XYZ',2009,4,500 union all
select 'BNG',2007,2,440 union all
select 'BNG',2007,3,1000 union all
select 'BNG',2009,4,50;
WITH
cteLo AS (SELECT GPName, ROW_NUMBER() OVER (PARTITION BY GPName ORDER BY YEAR ASC ,Qtr ASC) AS Num, Total_Doses FROM #t),
cteHi AS (SELECT GPName, ROW_NUMBER() OVER (PARTITION BY GPName ORDER BY YEAR DESC,Qtr DESC) AS Num, Total_Doses FROM #t)
SELECT lo.GPName, (hi.Total_Doses-lo.Total_Doses+0.0)/hi.Total_Doses * 100
FROM cteLo lo
INNER JOIN cteMax hi
ON lo.GPName = hi.GPName
AND lo.Num = 1
AND hi.Num = 1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)