Click here to Skip to main content
15,905,148 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear Friends,

I am having data of

Month Growth
SEP 5%
OCT 6%
NOV 2%
DEC 9%

I want to compare the previous month with next month and need to show it is grown or not.
Eg. OCT is Growth (OCT-SEP) and NOV is De-Growth (NOV-OCT)
How to get it, please guide
Posted

Try this

create    table test 
(
 month varchar(25) , growth int 
)

insert into test ( month, growth ) values ('sep' , 5)
insert into test ( month, growth ) values ('oxt' , 6)
insert into test ( month, growth ) values ('nov' , 2)
insert into test ( month, growth ) values ('dec' , 9)


Declare @prevmonthgrowth as int
Declare @month as  varchar(33)
Declare @currentgrowth as int
 
 declare @tbl table ( month varchar( 25) , growth int , result varchar(44) ) 
Declare MY_data CURSOR FOR
 
Select month ,growth from  test (Nolock)
 set @prevmonthgrowth = 0
OPEN MY_data
    FETCH NEXT FROM MY_data INTO @month ,@currentgrowth
        WHILE @@FETCH_STATUS = 0
        BEGIN
          
		  if(  @currentgrowth > @prevmonthgrowth )
		  insert into @tbl ( month , growth ,result ) values ( @month , @currentgrowth , 'growth');
		  else
		  insert into @tbl ( month , growth ,result ) values ( @month , @currentgrowth , 'no growth');
          set @prevmonthgrowth =   @currentgrowth          
        FETCH NEXT FROM MY_data INTO @month ,@currentgrowth
        END
    CLOSE MY_data
DEALLOCATE MY_data
select * from @tbl
 
Share this answer
 
Comments
Arunprasath Natarajan 10-Jan-14 13:18pm    
Tat was quick. But few corrections was there, But I have made the same. Tan q.
Karthik_Mahalingam 10-Jan-14 20:15pm    
Thanks Arun :)
The other answer is really terrible in terms of efficiency. Try this:

SQL
create    table test
(
id int identity,
 month varchar(25) , growth int
)

insert into test ( month, growth ) values ('sep' , 5)
insert into test ( month, growth ) values ('oct' , 6)
insert into test ( month, growth ) values ('nov' , 2)
insert into test ( month, growth ) values ('dec' , 9)

with growth
as
(
select month, growth, lag(growth, 1, null) over (order by id) as prev from test
)

select month, growth, case when prev is null then 'first value' when growth - prev > 1 then 'increased' when growth = prev then 'same' else 'decreased' end as result
from growth


This works for SQL Server 2012. Note that I added an id column, without that, you need to find another way to define the sort order so that you sort by month.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900