asked on

% change between rows

I want to get something like the following.

week count % changed
10 100 0
11 200 100%
12 300 50%
13 100 -66.7%
14 500 500%

What's the simplest way to do this?

Scott Pletcher

Perhaps something like this:

SELECT week, [count],
(SELECT ISNULL(CAST((currwk.[count] - prevwk.[count]) AS DECIMAL(10,2)) /
CAST(currwk.[count] AS DECIMAL(10,2)) * 100.00,0) AS '% changed'
FROM tablename prevwk
WHERE prevwk.week = currwk.week - 1)
FROM tablename currwk

spcmnspff

This will find the percentage change in records. in a table provided there is a column named week. Change Column1 to the name of any field in the table other than Week.

SET ARITHABORT OFF
Select [Week], C1 [Count], (C1-C2)/C1 [% Change]
From
(select T1.[Week], Count(T1.Column1) C1,
(Select IsNull(Count(T2.Column1) from MyTable T2 Where T2.[Week] = T1.[Week]-1),0) C2
From MyTable T1
Group By T1.[Week]) VT

If you don't have a week field, then you can use the datepart function to get the date from a create date:

SET ARITHABORT OFF
Select [Week], C1 [Count], (C1-C2)/C2 [% Change]
From
(select DatePart(wk,T1.CreateDt) [Week],
Count(T1.Column1) C1,
(Select IsNull(Count(T2.Column1)
from MyTable T2
Where DatePart(wk,T2.CreateDt) =
DatePart(wk,T1.CreateDt)-1),0) C2
From MyTable T1
Group By DatePart(wk,T1.CreateDt)-1)) VT

spcmnspff

Minor Change: Had to divide by C2 rather than C1:

SET ARITHABORT OFF
Select [Week], C1 [Count], (C1-C2)/C2 [% Change]
From
(select T1.[Week], Count(T1.Column1) C1,
(Select IsNull(Count(T2.Column1) from MyTable T2 Where T2.[Week] = T1.[Week]-1),0) C2
From MyTable T1
Group By T1.[Week]) VT

Or with the datepart function:

SET ARITHABORT OFF
Select [Week], C1 [Count], (C1-C2)/C2 [% Change]
From
(select DatePart(wk,T1.CreateDt) [Week],
Count(T1.Column1) C1,
(Select IsNull(Count(T2.Column1)
from MyTable T2
Where DatePart(wk,T2.CreateDt) =
DatePart(wk,T1.CreateDt)-1),0) C2
From MyTable T1
Group By DatePart(wk,T1.CreateDt)-1)) VT

gotaquestion

ASKER

I've created a table to support your queries and with both the last ones your proposed I'm getting.

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'from'.

I think it's missing a )

I can't seem to see it.

ASKER CERTIFIED SOLUTION

spcmnspff

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gotaquestion

ASKER

Thanks, I had to fix the divide by zero error because the
SET ARITHIGNORE OFF
SET ARITHABORT OFF
were not working for some reason.

Select [Week], C1 [Count], CASE C2 WHEN 0 THEN 0 ELSE (C1-C2)/C2 END [% Change]
From
(select T1.[Week], sum(T1.Column1) C1,
IsNull((Select sum(T2.Column1) from MyTable T2 Where T2.[Week] = T1.[Week]-1),0) C2
From MyTable T1
Group By T1.[Week]) VT