Get Difference of Previous Record Value and Current Record Value

Question

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 10-Jan-14 4:57am

Arunprasath Natarajan

Add a Solution

2 solutions

Solution 2

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.

Posted 12-Jan-14 13:42pm

Christian Graus

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Karthik_Mahalingam · Accepted Answer · 2014-01-10T05:25:00

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