Percentage change

Question

Percentage change

SQLbeginner

SSC Eights!

Points: 918
More actions
January 25, 2011 at 1:24 pm

#144443

I am trying to find the percentage change from a value of the month of Jan 2011, compared to the value of last Jan 2010.
The value is in one column and it contains all of the months values dating back to Jan 2010.
In the Select statement, how would I do this?
My calculation is something like this:
(current value Jan 2011 - value Jan 2010)/(value Jan 2010) = % changed
Thanks.

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

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

Mike01 SSChampion Points: 11381 More actions · Answer 1

Do you have any table layouts, sample data and expected outcome that you can provide? That will make it easier for us to help you

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLbeginner SSC Eights! Points: 918 More actions · Answer 2

Attached is the table, I am trying to get a comparison of each month % changes from 2010 to 2010.

Although, since it is only January, I can only do this month's comparison from last January.

SQLbeginner SSC Eights! Points: 918 More actions · Answer 3

Here is another sample of the table, what I need to know is how to script the query for the percentage change of current month from previous month, i.e. Jan 2011 from Jan 2010

TOTALMINS MONTH YEAR % CHANGE

60 JANUARY 2010 -

77 FEBRUARY 2010 -

66 MARCH 2010 -

60 APRIL 2010 -

70 MAY 2010 -

110 JUNE 2010 -

24 JULY 2010 -

55 AUGUST 2010 -

44 SEPTEMBER 2010 -

48 OCTOBER 2010 -

55 NOVEMBER 2010 -

33 DECEMBER 2010 -

69 JANUARY 2011 15

Mike01 SSChampion Points: 11381 More actions · Answer 4

Here is the syntax to get the values you are looking for. Essentially using a CTE to get last year and this year. Since this is using a temp table, you will need to run this in the same session as the create table script attached

SETUP

Create Table #EmpMinutes (EmpID varchar(10), TotalMins Smallint, WorkMonth varchar(12), WorkYear smallint)

insert into #EmpMinutes

Select 'B51332',0,'January',2010 union all

Select 'B51340',148,'January',2010 union all

Select 'B51348',830,'January',2010 union all

Select 'B51353',0,'January',2010 union all

Select 'B51353',239,'January',2010 union all

Select 'B51356',0,'January',2010 union all

Select 'B51357',0,'January',2010 union all

Select 'B51360',184,'January',2010 union all

Select 'B51360',100,'January',2010 union all

Select 'B56053',0,'September',2010 union all

Select 'B56063',222,'September',2010 union all

Select 'B56067',0,'September',2010 union all

Select 'B56084',0,'September',2010 union all

Select 'B56089',0,'September',2010 union all

Select 'B56099',53,'September',2010 union all

Select 'B56099',22,'September',2010 union all

Select 'B60324',92,'January',2011 union all

Select 'B60324',95,'January',2011 union all

Select 'B60325',0,'January',2011 union all

Select 'B60326',0,'January',2011 union all

Select 'B60326',1,'January',2011 union all

Select 'B60326',124,'January',2011 union all

Select 'B60326',46,'January',2011 union all

Select 'B60326',0,'January',2011

CODE

;with cte as

(select cast(sum(TotalMins) as Decimal(12,5)) TotalMinutes, WorkMonth, WorkYear

from #EmpMinutes

group by WorkMonth, WorkYear )

select CurrVal.TotalMinutes, CurrVal.WorkMonth, CurrVal.WorkYear,

cast((CurrVal.TotalMinutes - NextVal.TotalMinutes)/NextVal.TotalMinutes as decimal(12,5)) PctChange

from cte CurrVal

left outer join

cte NextVal

on CurrVal.WorkMonth = NextVal.WorkMonth

and Currval.WorkYear = NextVal.WorkYear + 1

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/