# 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```

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