0

Calculate percent change between 2 rows in SQL Server using CTE

A common table expression (CTE) is used in this example to create a temporary result set with a sequential number that allows us to do the arithmetic required to calculate percent change.

Let’s set up a test table:

CREATE TABLE gas_price_data (
	station_id int NOT NULL,
	data_date date NOT NULL,
	price decimal(4,3) NULL,
	PRIMARY KEY (station_id, data_date)
)

Give it some data:

INSERT INTO gas_price_data (station_id, data_date, price) VALUES 
(1, '2012-06-18', '3.999'), 
(1, '2012-06-19', '3.849'), 
(1, '2012-06-20', '3.789'),
(2, '2012-06-18', '4.019'), 
(2, '2012-06-19', '3.879'), 
(2, '2012-06-20', '3.829')

Let’s calculate the percent change between the two most recent entries for station 1 using a CTE:

WITH t1 AS
(
	SELECT TOP 2 price, ROW_NUMBER() OVER(ORDER BY data_date DESC) AS row_id FROM gas_price_data WHERE station_id = 1
)
 
SELECT ((SELECT price FROM t1 WHERE row_id = 1) - (SELECT price FROM t1 WHERE row_id = 2)) / (SELECT price FROM t1 WHERE row_id = 2) AS 'change'

If you wanted to calculate the percent change for each station, you could create a function to fetch the percent change by station like so:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE FUNCTION get_percent_change (@station_id int)
RETURNS decimal(6,5)
AS
BEGIN
	DECLARE @change decimal(6,5)
 
	;WITH t1 AS
	(
		SELECT TOP 2 price, ROW_NUMBER() OVER(ORDER BY data_date DESC) AS row_id FROM gas_price_data WHERE station_id = @station_id
	)
 
	SELECT @change = ((SELECT price FROM t1 WHERE row_id = 1) - (SELECT price FROM t1 WHERE row_id = 2)) / (SELECT price FROM t1 WHERE row_id = 2)
 
	RETURN @change
END

Add your function into your query like so:

SELECT DISTINCT a.station_id, (SELECT get_percent_change (a.station_id)) AS 'change' FROM gas_price_data

The function does not do any error checking.

Norbert Krupa

Technical Consultant

Leave a Reply

Your email address will not be published.