Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.

# Calculating Percentage Change

By Ryan,

To provide data analysis you may need to perform some basic trend analysis. For example, if you want to identify the percentage of change in the number of orders received from one month to the next. The challenge to providing that on the fly is the change can be either an increase or a decrease, you can risk divide by zero errors, or your users may need a general percentage regardless of direction.

I wrote this function (SQL 2000) to handle the various scenarios I was seeing, and used it in a couple select statements that ran aggregations to return sum values. To give an example using Northwind, this query will return the total orders per month:

SELECT Count(*), Month(OrderDate)
FROM Northwind..Orders
GROUP BY Month(orderDate)
ORDER BY Month(orderDate)

January has 88 orders and February 83, a decrease in sales by -5.68%. Other way around it would have been an increase by 6.02%. If you didn't care about the direction and only wanted the absolute change it would be an average of 5.85%. This function will let you retrieve any of those values.

SELECT
dbo.PercentageChange (a.prev, b.curr, 0, 0), -- -5.86%
dbo.PercentageChange (b.curr, a.prev, 0, 0), -- 6.02%
dbo.PercentageChange (a.prev, b.curr, 1, 0) -- 5.85%
FROM (SELECT Count(*) as [prev] FROM Northwind..Orders WHERE Month(OrderDate) = 1) as [a],
(SELECT Count(*) as [curr] FROM Northwind..Orders WHERE Month(OrderDate) = 2) as [b]

Any suggestions for improvement are welcome. You can certainly embed the math directly in a sql query and execute faster, the value here is mainly encapsulating the logic and reuse. I hope you find it handy.

Cheers!

Total article views: 2148 | Views in the last 30 days: 8

Related Articles
FORUM

### can't query on 'Order Details' table in Northwind

Northwind - Order Details

FORUM

### OrderDate = Today

How to make code below working? (OrderDate is date type) I want to add some code if OrderDate is ...

FORUM

### Select all months bewtween two dates

Select all months bewtween two dates

FORUM

### Month Names display order incorrect in reports (using Cube data)

Month Names display order incorrect in reports (using Cube data)

FORUM

### Group by month and year problem

different results to select over individual month

Tags
 miscellaneous sqlinsider scripts t-sql

## Join the most active online SQL Server Community

### SQL knowledge, delivered daily, free:

#### You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platformâ€¦ And itâ€™s our huge, buzzing community of SQL Server Professionals that makes it such a success.